Date: Tue, 02 Mar 2004 14:59:24 +1100
From: Terence Kearns <terencek@isd.canberra.edu.au>
Subject: Re: returning a recordset from PLpg/SQL
In-reply-to: <404384D0.4000108@joeconway.com>
To: pgsql-sql@postgresql.org
Message-id: <4044069C.1020108@isd.canberra.edu.au>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5)
	Gecko/20030925
References: <4041DCBF.203@isd.canberra.edu.au>
 <14883.1078067508@sss.pgh.pa.us>
	<40435499.7050805@isd.canberra.edu.au>
	<20040301073006.A71391@megazone.bigpanda.com>
	<471.1078161015@sss.pgh.pa.us> <404384D0.4000108@joeconway.com>

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@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_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@postgresql.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