postgresql - Postgresql向函数内的交叉表查询添加参数

Question

我正在尝试在交叉表查询中添加一些参数，如下所示：

    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(
       ...

    $$

如何从函数输入中获取提供的参数 param1 和 param2 到值 $1 和 $2？

标签： postgresql

Answer 1

您可以format()在构建查询字符串时使用

...
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'

       )