From: Joe Conway <mail@joeconway.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: terence.kearns@canberra.edu.au, Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: returning a recordset from PLpg/SQL
Date: Mon, 01 Mar 2004 10:45:36 -0800
Message-ID: <404384D0.4000108@joeconway.com> (raw)
In-Reply-To: <471.1078161015@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com
Subject: Re: returning a recordset from PLpg/SQL
In-Reply-To: <404384D0.4000108@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox