psql \set variables in crosstab queries?

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

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 3/4/23 19:32, David G. Johnston wrote:

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.

Ugh.  It's a long and hairy query that would be a nightmare in a format
statement.

--
Born in Arizona, moved to Babylonia.

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

Ugh. It's a long and hairy query that would be a nightmare in a format
statement.

Assuming you can pass this thing into the crosstab function in the first
place you must already have put it into a string. Changing "crosstab" to
"format", plopping in the placeholders, and adding a couple of more
passed-in arguments is not materially more effort or hairier.

You could create a SRF for the pre-crosstab data then do: SELECT
crosstab('select * from table_func(%,%,%)', :'dd', ...). The table_func
itself would then just use pl/pgsql variables (or SQL ones...) in a normal
query instead of a dynamic query (presuming you are only substituting
values anyway).

David J.