如何将动态查询的PostgreSQL DO语句转为可变返回结果的函数？

问题描述 #

需要将生成动态交叉表的DO语句转为函数，但查询结果的列数、列名随practice参数动态变化，无法固定函数返回表结构，寻求可行实现方式。原DO语句如下：

do $$
declare 
practice text;
date_columns text;
value_columns text;
query_text text;
query_result text;
begin
practice = 'practice1';
select string_agg(distinct '"' || service || '" numeric', ',')
into date_columns
from (select service_date from public.payment_application pa where practicename = practice) as dates(service);

select string_agg(distinct '(''''' || service || ''''')', ',')
into value_columns
from (select service_date from public.payment_application pa where practicename = practice) as dates(service);

query_text := 'SELECT * FROM crosstab(
   ''SELECT post_date, service_date as date, applied_amount as amount
     FROM public.payment_application where practicename=''''practice1'''
     ORDER BY 1, 2'', ''values ' || value_columns || '''
) AS ct (post_date text, ' || date_columns || ');';

execute query_text;
end $$;

三种可行解决方案 #

1. 使用REF CURSOR返回动态结果 #

通过游标封装动态查询结果，需在事务中调用：

CREATE OR REPLACE FUNCTION get_payment_crosstab(p_practice text)
RETURNS refcursor AS $$
DECLARE
    date_columns text;
    value_columns text;
    query_text text;
    result_cursor refcursor := 'payment_crosstab_cursor';
BEGIN
    -- 生成列定义字符串
    SELECT string_agg(DISTINCT quote_ident(service) || ' numeric', ',')
    INTO date_columns
    FROM (SELECT service_date AS service FROM public.payment_application WHERE practicename = p_practice) AS dates;

    -- 生成crosstab的values子句内容
    SELECT string_agg(DISTINCT format('(''%s'')', service), ',')
    INTO value_columns
    FROM (SELECT service_date AS service FROM public.payment_application WHERE practicename = p_practice) AS dates;

    -- 构造安全的查询语句（避免SQL注入）
    query_text := format('SELECT * FROM crosstab(
        ''SELECT post_date, service_date as date, applied_amount as amount
          FROM public.payment_application where practicename = %L
          ORDER BY 1, 2'',
        ''values %s''
    ) AS ct (post_date text, %s);', p_practice, value_columns, date_columns);

    -- 打开游标执行查询
    OPEN result_cursor FOR EXECUTE query_text;
    RETURN result_cursor;
END;
$$ LANGUAGE plpgsql;

调用方式：

BEGIN;
SELECT get_payment_crosstab('practice1');
FETCH ALL IN payment_crosstab_cursor;
COMMIT;

2. 返回SETOF record（需调用时指定列结构） #

这种方式要求调用时明确声明返回的列结构，适合提前知晓列信息的场景：

CREATE OR REPLACE FUNCTION get_payment_crosstab(p_practice text)
RETURNS SETOF record AS $$
DECLARE
    date_columns text;
    value_columns text;
    query_text text;
BEGIN
    SELECT string_agg(DISTINCT quote_ident(service) || ' numeric', ',')
    INTO date_columns
    FROM (SELECT service_date AS service FROM public.payment_application WHERE practicename = p_practice) AS dates;

    SELECT string_agg(DISTINCT format('(''%s'')', service), ',')
    INTO value_columns
    FROM (SELECT service_date AS service FROM public.payment_application WHERE practicename = p_practice) AS dates;

    query_text := format('SELECT * FROM crosstab(
        ''SELECT post_date, service_date as date, applied_amount as amount
          FROM public.payment_application where practicename = %L
          ORDER BY 1, 2'',
        ''values %s''
    ) AS ct (post_date text, %s);', p_practice, value_columns, date_columns);

    RETURN QUERY EXECUTE query_text;
END;
$$ LANGUAGE plpgsql;

调用示例（假设返回列包含post_date和两个日期列）：

SELECT * FROM get_payment_crosstab('practice1') 
AS (post_date text, "2024-01-01" numeric, "2024-01-02" numeric);

3. 返回JSONB格式（灵活适配动态列） #

将结果转为JSONB返回，无需固定列结构，适合客户端自行解析动态字段：

CREATE OR REPLACE FUNCTION get_payment_crosstab(p_practice text)
RETURNS JSONB AS $$
DECLARE
    date_columns text;
    value_columns text;
    query_text text;
    result_json JSONB;
BEGIN
    SELECT string_agg(DISTINCT quote_ident(service) || ' numeric', ',')
    INTO date_columns
    FROM (SELECT service_date AS service FROM public.payment_application WHERE practicename = p_practice) AS dates;

    SELECT string_agg(DISTINCT format('(''%s'')', service), ',')
    INTO value_columns
    FROM (SELECT service_date AS service FROM public.payment_application WHERE practicename = p_practice) AS dates;

    query_text := format('SELECT to_jsonb(t) FROM (
        SELECT * FROM crosstab(
            ''SELECT post_date, service_date as date, applied_amount as amount
              FROM public.payment_application where practicename = %L
              ORDER BY 1, 2'',
            ''values %s''
        ) AS ct (post_date text, %s)
    ) t;', p_practice, value_columns, date_columns);

    EXECUTE query_text INTO result_json;
    RETURN result_json;
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT get_payment_crosstab('practice1');

关键注意点 #

• 原DO语句的字符串拼接存在SQL注入风险，改用format()和quote_ident()确保查询安全。
• REF CURSOR必须在事务上下文使用，调用后需提交或回滚事务释放游标。
• 使用SETOF record时，若未指定列结构会直接报错，需提前获取列信息。

内容的提问来源于stack exchange，提问作者Alex Childs