Re: returning a recordset from PLpg/SQL - Mailing list pgsql-sql

Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.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_datefrom 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
fromexec_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