Обсуждение: psql \set variables in crosstab queries?

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

$ psql12 -v BOM=2023-02-01 -af foo.sql
select :'BOM'::timestamp + interval'6 month';
       ?column?
---------------------
  2023-08-01 00:00:00
(1 row)

select $$ :BOM $$;
  ?column?
----------
   :BOM
(1 row)

But crosstab takes text strings as parameters.  How then do you use \set 
variables in crosstab queries?

-- 
Born in Arizona, moved to Babylonia.

Ron <ronljohnsonjr@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';".

            regards, tom lane

On 3/4/23 19:22, Tom Lane wrote:
> Ron <ronljohnsonjr@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.

On Sat, Mar 4, 2023 at 5:20 PM Ron <ronljohnsonjr@gmail.com> wrote:

But crosstab takes text strings as parameters.  How then do you use \set
variables in crosstab queries?

You need to dynamically write the textual query you want to send to the crosstab function.  In particular that means writing it using "format()" and then substituting the values into the query via placeholders.

Roughly like:

SELECT * FROM crosstab(format('SELECT * FROM %I WHERE %I = %L, :'tbl_name', :'col_name', :'compare_value'));

David J.