Re: psql \set variables in crosstab queries?

On Sat, Mar 4, 2023 at 10:05 PM Ron <ronljohnsonjr(at)gmail(dot)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.