Thread: docs: psql and variable interpolation

Hi all,

I recently used the nice variable capabilities in psql, after having read the "SQL Interpolation" chapter in the psql page (https://www.postgresql.org/docs/12/app-psql.html).

But I spent a lot of time trying to understand why using a variable in a \copy command failed ... until a colleague of mine showed me this was actually written elsewhere in the documentation (formerly in the \copy chapter).

The first sentence of this SQL interpolation chapter says "A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands." But nothing in this chapter indicates that there are exceptions.

May be we could have a wording adjustment with something like : "A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands (unless specifically noted)."

Regards. Philippe.

---

	DALIBO L'expertise PostgreSQL 43, rue du Faubourg Montmartre 75009 Paris	Philippe Beaudoin Consultant Avant-Vente +33 (0)1 84 72 76 11 +33 (0)7 69 14 67 21 philippe.beaudoin@dalibo.com
Valorisez vos compétences PostgreSQL, certifiez-vous chez Dalibo !

The first sentence of this SQL interpolation chapter says "A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands." But nothing in this chapter indicates that there are exceptions.

May be we could have a wording adjustment with something like : "A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands (unless specifically noted)."

On Monday, July 13, 2020, Philippe Beaudoin <philippe.beaudoin@dalibo.com> wrote:

The first sentence of this SQL interpolation chapter says "A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands." But nothing in this chapter indicates that there are exceptions.

May be we could have a wording adjustment with something like : "A key feature of psql variables is that you can substitute (“interpolate”) them into regular SQL statements, as well as the arguments of meta-commands (unless specifically noted)."

Having “unless otherwise noted” be implied doesn’t seem like a big failing.  As for this case it is documented as being an exception:

“Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments.”

If it is only \copy that is an exception I’d rather just say (except \copy} and not have the user meticulously read each item for the single exception.  This extends to if there are a few.

As far as I know, the \! metacommand is another exception. But I don't know if there are some others.

David J.

---

	DALIBO L'expertise PostgreSQL 43, rue du Faubourg Montmartre 75009 Paris	Philippe Beaudoin Consultant Avant-Vente +33 (0)1 84 72 76 11 +33 (0)7 69 14 67 21 philippe.beaudoin@dalibo.com
Valorisez vos compétences PostgreSQL, certifiez-vous chez Dalibo !

Philippe Beaudoin <philippe.beaudoin@dalibo.com> writes:
> Le 13/07/2020 à 17:45, David G. Johnston a écrit :
>> If it is only \copy that is an exception I’d rather just say (except 
>> \copy} and not have the user meticulously read each item for the 
>> single exception.  This extends to if there are a few.

> As far as I know, the \! metacommand is another exception. But I don't 
> know if there are some others.

Looking at the psql source code, the commands that use OT_WHOLE_LINE
argument parsing are

\copy
\ef, \ev
\sf, \sv
\help
\!

There's also OT_FILEPIPE argument parsing, which acts like WHOLE_LINE
if the argument starts with "|" (and otherwise is normal AFAICS).
That's used by

\g, \gx
\o
\w

            regards, tom lane

On Wed, Jul 15, 2020 at 11:49:44AM -0400, Tom Lane wrote:
> Philippe Beaudoin <philippe.beaudoin@dalibo.com> writes:
> > Le 13/07/2020 à 17:45, David G. Johnston a écrit :
> >> If it is only \copy that is an exception I’d rather just say (except 
> >> \copy} and not have the user meticulously read each item for the 
> >> single exception.  This extends to if there are a few.
> 
> > As far as I know, the \! metacommand is another exception. But I don't 
> > know if there are some others.
> 
> Looking at the psql source code, the commands that use OT_WHOLE_LINE
> argument parsing are
> 
> \copy
> \ef, \ev
> \sf, \sv
> \help
> \!

FYI, the way I got around \! not interpolating psql variables is to use
\setenv and then reference the variable in \!, e.g.:

    test=> \setenv x y
    test=> \! echo $x
    y

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee