Re: psql \set variables in crosstab queries?

Ron <ronljohnsonjr@xxxxxxxxx> · Sat, 4 Mar 2023 23:05:52 -0600



    On 3/4/23 19:32, David G. Johnston wrote:

    
          On Sat, Mar
              4, 2023 at 5:20 PM Ron <ronljohnsonjr@xxxxxxxxx>
              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.