pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
psql \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