Postgres动态sql和列表结果 [英] Postgres dynamic sql and list result

问题描述

我使用EXECUTE（用于动态sql）和SETOF（结果作为列表返回），但是是错误的：（$ / $）

I used EXECUTE(for dynamic sql) and SETOF(result is returning as list), but it is the wrong :(

create table test as
select 1 id, 'safd' data1,'sagd' data2
union
select 2 id, 'hdfg' data1,'sdsf' data2;

create or replace function test2(a varchar) returns SETOF record as
$BODY$
declare x record;
begin
for x in execute a loop
RETURN NEXT x;
end loop;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

select * from test2('select * from test');

推荐答案

您将必须提前知道返回记录的结构

You will have to know in advance the structure of the returned record

select * from test2('select * from test') s(a int, b text, c text);
 a |  b   |  c   
---+------+------
 1 | safd | sagd
 2 | hdfg | sdsf

或者如果返回的集合将永远是一组测试t然后可以使用Akash提出的解决方案。

Or if the returned set will always be a set of the test table then use the Akash's proposed solution.

这篇关于Postgres动态sql和列表结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！