PostgreSQL中如何通过函数返回动态结构的表查询结果?
PostgreSQL动态返回不同结构查询结果解决方案
你遇到的核心限制是PostgreSQL的PL/pgSQL函数要求返回的表结构必须在定义阶段静态确定,无法根据入参动态调整返回列的数量、字段类型,因此你当前想直接在RETURNS TABLE环节写动态结构的思路无法直接实现,以下是3种可行的落地方案:
方案1:统一返回结构,多余字段补空
这是兼容性最好的常用方案,你可以合并两个查询的字段为统一的返回结构,不匹配的分支对应字段返回NULL即可:
CREATE OR REPLACE FUNCTION func1( a int ) -- 此处字段类型替换为你实际表对应字段的类型即可 RETURNS TABLE(col1 对应a1/a2的字段类型, col2 对应b1/b2的字段类型, col3 对应c1/c2的字段类型, col4 对应d2的字段类型) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ begin if (a=1) then return query select a1, b1, c1, NULL::对应d2的字段类型 from table1; else return query select a2, b2, c2, d2 from table2; end if; END; $BODY$;
调用时如果入参a=1,直接忽略第四列即可。
方案2:返回JSONB动态结构
如果不想固定返回结构,可以直接把查询结果序列化为JSONB类型返回,调用方自行解析即可,完全不受列数、字段类型限制:
CREATE OR REPLACE FUNCTION func1( a int ) RETURNS SETOF jsonb LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ begin if (a=1) then return query select to_jsonb(t) from (select a1,b1,c1 from table1) t; else return query select to_jsonb(t) from (select a2,b2,c2,d2 from table2) t; end if; END; $BODY$;
适合上层应用可以直接处理JSON格式的场景。
方案3:返回REFCURSOR游标
你也可以返回游标类型,调用方根据入参值自行读取对应结构的结果:
CREATE OR REPLACE FUNCTION func1( a int, ref_name refcursor default 'res_cursor' ) RETURNS refcursor LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ begin open ref_name for if (a=1) then select a1,b1,c1 from table1; else select a2,b2,c2,d2 from table2; end if; return ref_name; END; $BODY$;
调用时需要在事务内操作:
BEGIN; SELECT func1(1); FETCH ALL IN "res_cursor"; COMMIT;
内容的提问来源于stack exchange,提问作者Arcil Tralleis
相关产品推荐
相关产品推荐

