Re: returning a recordset from PLpg/SQL

Tom Lane wrote:
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>>On Tue, 2 Mar 2004, Terence Kearns wrote:
>>>Well I haven't yet done anything because I couldn't get anything to
>>>compile which returned SETOF RECORD..
>
>>As a starting point, SETOF "RECORD" is different from SETOF RECORD given
>>PostgreSQL's fold case to lower case for unquoted names.
>
> Also, you can hardly expect a function to return a rowtype that doesn't
> even exist until the function executes --- how the heck is the parser
> supposed to make sense of the calling query? So the "execute create
> type" part of this is nonsense, I'm afraid. The SETOF RECORD mechanism
> will let you return a rowtype that is not known fully at the time the
> function is written, but the rowtype does have to be known when the
> calling query is parsed.
>
> You might be able to replace the CREATE TYPE with an anonymous record
> type in the calling query:
>
> select ...
> from details_for_profile(...) as x(doc_id int4,
> doc_title varchar(256),
> ...);

A small improvement is to do a two-step process. From your app, you
first SELECT a function call that returns an SQL statement as a text
string, specific to att_data_type. Then you execute that as a second
step. For example:
--8<--------------------------

create table hdr(hid int primary key, context text);
insert into hdr values(1, 'test1');
insert into hdr values(2, 'test2');

create table att(aid int primary key, attname text, atttype text);
insert into att values (1, 'test_date', 'timestamp');
insert into att values (2, 'height', 'float8');
insert into att values (3, 'width', 'float8');
insert into att values (4, 'color', 'text');

create table det(
did int primary key,
hid int references hdr,
aid int references att,
val text
);
insert into det values(1,1,1,'15-mar-2004');
insert into det values(2,1,2,'3.14159');
insert into det values(3,1,3,'2.8');
insert into det values(4,1,4,'blue');
insert into det values(5,2,1,'16-mar-2004');
insert into det values(6,2,2,'2.34');
insert into det values(7,2,3,'3.28');
insert into det values(8,2,4,'red');

create or replace function exec_sql(int) returns setof record as '
DECLARE
lookup_row record;
v_atttype text := '''';
rec record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_atttype := lookup_row.atttype;
END LOOP;

FOR rec IN execute ''SELECT h.hid, h.context, d.val::'' || v_atttype ||
'' FROM hdr h, att a, det d '' ||
'' WHERE d.hid = h.hid and d.aid = a.aid and a.aid = '' || $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' language plpgsql;

create or replace function write_sql(int) returns text as '
DECLARE
v_attname text := '''';
v_atttype text := '''';
v_result text;
lookup_row record;
BEGIN
FOR lookup_row IN SELECT * FROM att WHERE aid = $1
LOOP
v_attname := lookup_row.attname;
v_atttype := lookup_row.atttype;
END LOOP;
v_result := ''select hid, context, '' || v_attname ||
'' from exec_sql('' || $1 || '') as t(hid int, context
text, '' ||
v_attname || '' '' || v_atttype || '')'';
return v_result;
END;
' language plpgsql;

regression=# select write_sql(1);
write_sql
--------------------------------------------------------------------------------------------------
select hid, context, test_date from exec_sql(1) as t(hid int, context
text, test_date timestamp)
(1 row)

regression=# select hid, context, test_date from exec_sql(1) as t(hid
int, context text, test_date timestamp);
hid | context | test_date
-----+---------+---------------------
1 | test1 | 2004-03-15 00:00:00
2 | test2 | 2004-03-16 00:00:00
(2 rows)

regression=# select write_sql(2);
write_sql
-----------------------------------------------------------------------------------------
select hid, context, height from exec_sql(2) as t(hid int, context
text, height float8)
(1 row)

regression=# select hid, context, height from exec_sql(2) as t(hid int,
context text, height float8);
hid | context | height
-----+---------+---------
1 | test1 | 3.14159
2 | test2 | 2.34
(2 rows)

regression=# select write_sql(3);
write_sql
---------------------------------------------------------------------------------------
select hid, context, width from exec_sql(3) as t(hid int, context
text, width float8)
(1 row)

regression=# select hid, context, width from exec_sql(3) as t(hid int,
context text, width float8);
hid | context | width
-----+---------+-------
1 | test1 | 2.8
2 | test2 | 3.28
(2 rows)

regression=# select write_sql(4);
write_sql
-------------------------------------------------------------------------------------
select hid, context, color from exec_sql(4) as t(hid int, context
text, color text)
(1 row)

regression=# select hid, context, color from exec_sql(4) as t(hid int,
context text, color text);
hid | context | color
-----+---------+-------
1 | test1 | blue
2 | test2 | red
(2 rows)

--8<--------------------------

Hopefully this is close enough to what you are trying to do that it will
give you some ideas.

HTH,

Joe