Re: returning a recordset from PLpg/SQL

I've read through all the example code you've provided thoroughly and
there are definately some useful ideas there. I changed the design of
the document_attribute_values table to only have one field, a text
field, to store the value. As your examples demosntrates, I can simply
cast the text value to whatever the entity_attribute record [for that
value] specifies.

I also think a 2-step approach is the way to go.

Thanks.

Joe Conway wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au