pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql \set variables in crosstab queries?
6+ messages / 3 participants
[nested] [flat]
* psql \set variables in crosstab queries?
@ 2023-03-05 00:20 Ron <ronljohnsonjr@gmail.com>
2023-03-05 01:22 ` Re: psql \set variables in crosstab queries? Tom Lane <tgl@sss.pgh.pa.us>
2023-03-05 01:32 ` Re: psql \set variables in crosstab queries? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Ron @ 2023-03-05 00:20 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql \set variables in crosstab queries?
2023-03-05 00:20 psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
@ 2023-03-05 01:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-05 05:03 ` Re: psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-03-05 01:22 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql \set variables in crosstab queries?
2023-03-05 00:20 psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
2023-03-05 01:22 ` Re: psql \set variables in crosstab queries? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-05 05:03 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2023-03-05 05:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql \set variables in crosstab queries?
2023-03-05 00:20 psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
@ 2023-03-05 01:32 ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-05 05:05 ` Re: psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2023-03-05 01:32 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql \set variables in crosstab queries?
2023-03-05 00:20 psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
2023-03-05 01:32 ` Re: psql \set variables in crosstab queries? David G. Johnston <david.g.johnston@gmail.com>
@ 2023-03-05 05:05 ` Ron <ronljohnsonjr@gmail.com>
2023-03-05 05:15 ` Re: psql \set variables in crosstab queries? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ron @ 2023-03-05 05:05 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: psql \set variables in crosstab queries?
2023-03-05 00:20 psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
2023-03-05 01:32 ` Re: psql \set variables in crosstab queries? David G. Johnston <david.g.johnston@gmail.com>
2023-03-05 05:05 ` Re: psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
@ 2023-03-05 05:15 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2023-03-05 05:15 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-03-05 05:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-05 00:20 psql \set variables in crosstab queries? Ron <ronljohnsonjr@gmail.com>
2023-03-05 01:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-05 05:03 ` Ron <ronljohnsonjr@gmail.com>
2023-03-05 01:32 ` David G. Johnston <david.g.johnston@gmail.com>
2023-03-05 05:05 ` Ron <ronljohnsonjr@gmail.com>
2023-03-05 05:15 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox