On 3/4/23 19:22, Tom Lane wrote:
Ron <ronljohnso...@gmail.com> writes:
According to
https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-VARIABLES and
experience, variables don't interpolate inside of string literals:
"
Variable interpolation will not be performed within quoted SQL literals and
identifiers. Therefore, a construction such as ':foo' doesn't work to
produce a quoted literal from a variable's value.
"
But crosstab takes text strings as parameters.  How then do you use \set
variables in crosstab queries?
If you read a little further, you'll find out the syntax for converting
the value of a psql variable to a SQL string literal:

regression=# \set foo BAR
regression=# select ':foo';
  ?column?
----------
  :foo
(1 row)

regression=# select :'foo';
  ?column?
----------
  BAR
(1 row)

What the server got in the last case was "select 'BAR';".

postgres=# \set foo BAR
postgres=# select :'foo';
 ?column?
----------
 BAR
(1 row)

postgres=# select $$ :foo $$;
 ?column?
----------
  :foo
(1 row)

postgres=# select $$ :'foo' $$;
 ?column?
----------
  :'foo'
(1 row)





--
Born in Arizona, moved to Babylonia.


Reply via email to