Re: psql \set variables in crosstab queries?

On 3/4/23 19:32, David G. Johnston wrote:
> On Sat, Mar 4, 2023 at 5:20 PM Ron <ronljohnsonjr(at)gmail(dot)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.