Thread: 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 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:
Ugh. It's a long and hairy query that would be a nightmare in a format statement.
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.
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.