CODEWITHSUNDEEP
postgresqldynamicrecord
Virus
Virus

Reputation: 167

Postgres dynamic sql and list result

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');

Upvotes: 0

Views: 686

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

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.

Upvotes: 1

Akash
Akash

Reputation: 5012

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)

Upvotes: 1

Nick
Nick

Reputation: 565

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;
$$;

Upvotes: 0

Related Questions