Thread: "--variable foo=bar" vs. "\set foo quux" in ~/.psqlrc

Hi all,

I'm trying to add the database host name to my psql prompts.  The obvious
solution is to add %M or %m to the PROMPT{1,2} variables in ~/.psqlrc.

However I have to work with a few databases that can be reached only
through SSH tunnels, for which I use aliases like this:

  alias dbfoo='ssh -q -f -o "ExitOnForwardFailure yes" -L 15432:server-beyond-tunnel:5432
               gateway-host sleep 5; psql -h localhost -p 15432 -U dbuser foodb'

So the problem with the above prompt solution is that this will show a
hostname of "localhost" because that's what psql connects to.

Now I've tried using the following in .psqlrc:

  \set host :HOST
  \set PROMPT1 '%:host::%/%x%R%# '
  \set PROMPT2 '%:host::%/%x%R%# '

and then overriding the "host" variable from the command line using
"--variable host=server-beyond-tunnel".  Unfortunately the --variable
option doesn't seem to have any effect, presumably because it's evaluated
*before* .psqlrc is read and executed.

Can anyone confirm that --variable command-line options are evaluated
before .psqlrc is read and executed?  If so, does anyone know the
rationale for that?  It seems counterintuitive to me, as it makes
overriding variables from the command-line impossible.

If there is consensus that evaluating --variable options *after* .psqlrc
was read and executed is an acceptable change, or that a --variable-late
option should be introduced, I might come up with a patch.

TIA,

-Julian

Julian Mehnle <julian@mehnle.net> writes:
> Can anyone confirm that --variable command-line options are evaluated
> before .psqlrc is read and executed?  If so, does anyone know the
> rationale for that?  It seems counterintuitive to me, as it makes
> overriding variables from the command-line impossible.

Seems entirely intuitive to me, as we can certainly not process .psqlrc
before processing the command line options (one of them might be -X for
instance).  Furthermore, changing it would likely break existing
applications.

            regards, tom lane

Tom Lane wrote:

> Julian Mehnle <julian@mehnle.net> writes:
> > Can anyone confirm that --variable command-line options are evaluated
> > before .psqlrc is read and executed?  If so, does anyone know the
> > rationale for that?  It seems counterintuitive to me, as it makes
> > overriding variables from the command-line impossible.
>
> Seems entirely intuitive to me, as we can certainly not process .psqlrc
> before processing the command line options (one of them might be -X for
> instance).

Well, OK, that may be true for -X and certain others, but not necessarily
for --variable.

> Furthermore, changing it would likely break existing applications.

Fair enough.  Any comments on my other points and questions, Tom?

-Julian