Stephan Szabo <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
