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
Or if the returned set will always be a set of the test table then use the Akash's proposed solution.
replace
create or replace function test2(a varchar) returns SETOF RECORD as
with
create or replace function test2(a varchar) returns SETOF test as
^^^^ name of table (it specifies the datatypes of the set)
You need to add some OUT params.
CREATE FUNCTION test2(a character varying, OUT id integer, OUT data1 text, OUT data2 text) RETURNS SETOF record
LANGUAGE plpgsql
AS $$
begin
RETURN QUERY EXECUTE a;
end;
$$;
