From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: psql \set variables in crosstab queries? |
Date: | 2023-03-05 05:03:37 |
Message-ID: | 7fa041bf-bca9-4a2b-f1cc-ebdcb7b76dd2@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/4/23 19:22, Tom Lane wrote:
> Ron <ronljohnsonjr(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-03-05 05:05:52 | Re: psql \set variables in crosstab queries? |
Previous Message | David G. Johnston | 2023-03-05 01:32:21 | Re: psql \set variables in crosstab queries? |