PostgreSQL：如何无需指定列定义调用返回record的动态列函数

问题分析 #

你当前的函数返回setof record（匿名记录集），PostgreSQL无法提前推断返回的列名和数据类型，因此调用时必须添加as (列名 类型, ...)子句来明确结构。要实现无额外子句的调用，需要调整函数的返回方式或逻辑。

优化原函数（先解决SQL注入风险） #

原函数直接拼接输入列名存在SQL注入风险，先优化为安全版本：

CREATE OR REPLACE FUNCTION public.test_dynamic_data(p_ass_col text, p_test_col text)
RETURNS SETOF record
LANGUAGE plpgsql
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    sql_query TEXT;
BEGIN
    -- 拆分并转义列名，避免SQL注入
    sql_query := format(
        'SELECT DISTINCT %s FROM public.test_dynamic_data_tbl WHERE col2 = %L',
        string_agg(format('%I', unnest(string_to_array(p_ass_col, ','))), ', '),
        p_test_col
    );
    RETURN QUERY EXECUTE sql_query;
END;
$BODY$;

注：这里补上了你未使用的p_test_col参数作为过滤条件，若不需要可移除WHERE col2 = %L部分。

实现无需指定列定义的调用方案 #

由于匿名记录集的特性，无法完全实现select * from func(...)无额外子句的调用，但可以通过以下两种方式接近需求：

方案1：返回JSONB格式结果

将查询结果转为JSONB返回，调用时直接获取结构化的JSON对象，无需指定列定义：

CREATE OR REPLACE FUNCTION public.test_dynamic_data(p_ass_col text, p_test_col text)
RETURNS SETOF jsonb
LANGUAGE plpgsql
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    sql_query TEXT;
BEGIN
    sql_query := format(
        'SELECT DISTINCT to_jsonb(t) FROM (SELECT %s FROM public.test_dynamic_data_tbl WHERE col2 = %L) t',
        string_agg(format('%I', unnest(string_to_array(p_ass_col, ','))), ', '),
        p_test_col
    );
    RETURN QUERY EXECUTE sql_query;
END;
$BODY$;

调用方式：

select * from public.test_dynamic_data('col1,col2','testing');

返回结果为每个行对应的JSONB对象，例如：{"col1":1,"col2":"test1"}

方案2：使用游标返回结果

通过游标返回记录集，调用时需要开启事务获取结果：

CREATE OR REPLACE FUNCTION public.test_dynamic_data(p_ass_col text, p_test_col text, OUT ref refcursor)
LANGUAGE plpgsql
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    sql_query TEXT;
BEGIN
    ref := 'dynamic_data_cursor'; -- 定义游标名
    sql_query := format(
        'SELECT DISTINCT %s FROM public.test_dynamic_data_tbl WHERE col2 = %L',
        string_agg(format('%I', unnest(string_to_array(p_ass_col, ','))), ', '),
        p_test_col
    );
    OPEN ref FOR EXECUTE sql_query;
END;
$BODY$;

调用方式：

BEGIN;
SELECT public.test_dynamic_data('col1,col2','testing');
FETCH ALL FROM dynamic_data_cursor;
COMMIT;

此方式可以直接返回列形式的结果，无需指定列定义，但需要事务包裹。

说明 #

如果必须返回纯列形式的结果且无需任何额外子句，PostgreSQL原生无法实现——因为数据库需要明确知道返回的结构才能解析结果。上述方案是最接近需求的替代方式。

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