using psql, can I override variables set in .psqlrc from command line?

user_86072 0
Viewed 400

I would like to have a .psqlrc file with default values, and be able to override these values from psql's command line.

For example :

  • have some values set in .psqlrc :
-- .psqlrc :
-- "user@database # " in bold green
\set PROMPT1 '%[%033[1;32;40m%]%n@%/%[%033[0m%]% > '
-- store command history in home directory, with a per database file :
\set HISTFILE ~/.psql-history- :DBNAME
  • in some wrapper script master-psql.sh, which connects as user postgres, be able to override these values :
# master-psql.sh :
# when using this script, change color to red, change history file location :
psql -U postgres \
  -v PROMPT1='%[%033[1;31;40m%]%n@%/%[%033[0m%]% # ' \
  -v HISTFILE='/some/other/place/.psql_history_postgres'

The above does not work, because the the -v ... argument is executed before the .psqlrc file is loaded, and the instruction in .psqlrc overwrites the existing value.

Question

Is there a way to instruct psql to run a set of commands after loading its .psqlrc file(s),
or to have .psqlrc execute some \set or \pset command only if value is not set ?

psql postgresql
2 Answers

You could write the instructions not to overwrite those variables if already set into the .psqlrc file itself:

\if :{?HISTFILE}
\else
\set HISTFILE ~/.psql-history- :DBNAME
\endif

If you can't get your system psqlrc to cooperate with you, then might need to copy and modify it and then bypass the original. You need at least v11 for the :{? construct to work.

The problem is that PROMPT1 has a compiled-in default even in the absence of RC file processing, so you might need to test that against the compiled-in string, rather than test for being defined. So I think that would end up with something like this:

select :'PROMPT1'='%/%R%x%# ' as default_prompt \gset
\if :default_prompt
\set PROMPT1 '%[%033[1;32;40m%]%n@%/%[%033[0m%]% > '
\endif

Note that the compiled in default changed in v13, so if you want to work with older versions as well, you would need to do something more complicated.

user_17212390

From here:

https://www.postgresql.org/docs/current/app-psql.html

Environment
[...]

PSQLRC
Alternative location of the user's .psqlrc file. Tilde (~) expansion is performed.

So create an alternate .psqlrc file and set thePSQLRC environment variable for the script to override your default.

user_70706130
Related