主页 / dba / 问题 / 178047
Accepted
Randomize
Asked: 2017-07-06 04:06:43 +0800 CST

在 Postgres 函数中引用函数参数

  • 772

我在 Postgres 9.5 中有这样的功能：

CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
        BEGIN
                RETURN QUERY
                with t as (
                    select p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
                      FROM crosstab (
                        $$
                          select postcode::text, indicator::int, value::float
                          from my_data
                          where (indicator = p2 or indicator = p3) and date = p1
                          order by 1,2
                        $$)
                      AS ct (
                        "postcode" text,
                        "price" float,
                        "rent" float)
                )
                select * from t where result is not null;
        END
$func$ LANGUAGE plpgsql;

当我加载它时，PSQL我得到了错误：

ERROR:  column "p2" does not exist
LINE 4:                           where (indicator = p2 ...
                                                     ^
QUERY:  
                          select postcode::text, indicator::int, value::float
                          from my_data
                          where (indicator = p2 or indicator = p3) and date = p1
                          order by 1,2

我试图在p2etc 前加上前缀，my_func.但它不起作用。我如何在该上下文中引用函数参数？

postgresql pivot

1 个回答

  1. Best Answer

    Crosstab 正好看到一个字符串，所以发送一个好的字符串。

    CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
        BEGIN
                RETURN QUERY
                WITH t AS (
                    SELECT p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
                      FROM crosstab (
                        format(
                          $$
                            SELECT postcode::text, indicator::int, value::float
                            FROM my_data
                            WHERE (indicator = %s or indicator = %s)
                              AND date = '%s'
                            ORDER BY 1,2
                          $$,
                          p2,
                          p3,
                          p1
                        )
                      )
                      AS ct (
                        "postcode" text,
                        "price" float,
                        "rent" float)
                )
                select * from t where result is not null;
        END
$func$
LANGUAGE plpgsql;

    顺便说一句，如果你打算使用命名参数，p1、p2、p3 等是个坏主意。他们已经$1, $2, $2。我会建议至少一些更容易理解的东西，比如indicator1, indicator2

    • 1

相关问题