CODEWITHSUNDEEP
postgresql
Daryn
Daryn

Reputation: 1609

Postgresql adding parameters to crosstab query inside a function

I am trying to add some parameters inside a crosstab query something like follows: 

    create or replace function my_function(param1 character varying, param2 date)        
    returns table (

        ...
        ...

   as           
    $$     
        with t as 
        (
        SELECT * FROM crosstab('
       SELECT something , period, value
        FROM   my_table
        WHERE some_value = $1  and some_date = $2 
        ORDER  BY 1,2 ',
        'SELECT p FROM generate_series(1,10) p'

       )

    AS ct(
       ...

    $$

How can I get the supplied parameters param1 and param2 from the function input to the values $1 and $2?

Upvotes: 1

Views: 618

Answers (1)

JGH
JGH

Reputation: 17906

You can use format() when building the query string

...
SELECT * FROM crosstab(format('
       SELECT something , period, value
        FROM   my_table
        WHERE some_value = %s  and some_date = %s 
        ORDER  BY 1,2 ',param1 ,param2),
        'SELECT p FROM generate_series(1,10) p'

       )

Upvotes: 3

Related Questions