docs: psql and variable interpolation

po 13. 7. 2020 v 15:09 odesílatel Philippe Beaudoin <
philippe.beaudoin@dalibo.com> napsal:

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)."

+1

or maybe better - if it is possible reduce a exceptions

Pavel

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.

David J.

Le 13/07/2020 à 17:45, David G. Johnston a écrit :

On Monday, July 13, 2020, Philippe Beaudoin
<philippe.beaudoin@dalibo.com <mailto: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.

------------------------------------------------------------------------
<https://www.dalibo.com/>
	*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 
<https://certification.dalibo.com/> !

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