Обсуждение: return setof record from function with dynamic query
-----BEGIN PGP SIGNED MESSAGE-----
Hash: MD5
Hi all,
I've been beating my head against the desk for days on this, and
I've been completely unable to find docs on it. I have a function
(code below) that creates and executes a dynamic query. I want to
pass the results of that query as a recordset to the caller - I can
do it as a refcursor (but via odbc a refcursor just appears as an
empty recordset, no use at all.), but when I use the function code
below I get the error:
ERROR: A column definition list is required for functions returning RECORD
I'd really appreciate it if someone could show me how to pass back
a set of records please???
Toby
CREATE FUNCTION poptions (INTEGER)
RETURNS setof record AS '
DECLARE
-- parameters
p_author_id ALIAS FOR $1;
-- local variables
row_data poem_option_def%ROWTYPE;
newrows record;
i integer;
qString varchar(4000);
BEGIN
qString := ''SELECT p.poem_id,p.poem_title'';
FOR row_data IN SELECT option_id,option_name FROM poem_option_def LOOP
qString := qString || '', (SELECT po.setting FROM poem_option po WHERE po.poem_id = p.poem_id and
po.option_id= '' || row_data.option_id || '') AS '' || row_data.option_name;
END LOOP;
qString := qString || '' FROM poem p WHERE p.author_id = '' || p_author_id;
FOR newrows IN EXECUTE qString loop
RETURN NEXT newrows
END LOOP;
RETURN;
end;
' LANGUAGE 'plpgsql';
---------------------------------------
Life is poetry -
write it in your own words.
---------------------------------------
Toby Tremayne
Technical Team Lead
Code Poet and Zen Master of the Heavy Sleep
Toll Solutions
154 Moray St
Sth Melbourne
VIC 3205
+61 3 9697 2317
0416 048 090
ICQ: 13107913
-----BEGIN PGP SIGNATURE-----
Version: 2.6
iQCVAwUAPgATV0YhrxxXvPlFAQH1NAQAr+sRBxO3fOjaJR/CgZZRMdUYLF26alpE
DyP/V4H0Shf2sJomUFblO6KGcU8x/jYSky8xJNHnFftUDDsyX+nlcI5qNyS8ABtS
BJ3EQq8AaW69S+F6QeKFaoXDR/pSWr36+gZr+KeAy4jUMYGtVkUSEXlDiKl1kZv4
o+/1vtl8gi8=
=wf+S
-----END PGP SIGNATURE-----
On Wed, 18 Dec 2002, Toby Tremayne wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: MD5 > > Hi all, > > I've been beating my head against the desk for days on this, and > I've been completely unable to find docs on it. I have a function > (code below) that creates and executes a dynamic query. I want to > pass the results of that query as a recordset to the caller - I can > do it as a refcursor (but via odbc a refcursor just appears as an > empty recordset, no use at all.), but when I use the function code > below I get the error: > > ERROR: A column definition list is required for functions returning RECORD When you use the function, you need to say something like: select * from func() as foo(col1 int, col2 varchar, ...); Since it's an arbitrary record type, it doesn't know what the types are, so you need to provide it at select time.
On Wed, 18 Dec 2002 17:18:56 +1100 Toby Tremayne <tobyt@toll.com.au> wrote: > ERROR: A column definition list is required for functions returning RECORD > > I'd really appreciate it if someone could show me how to pass back > a set of records please??? > > Toby > > CREATE FUNCTION poptions (INTEGER) > RETURNS setof record AS ' > DECLARE > -- parameters > p_author_id ALIAS FOR $1; > > -- local variables > row_data poem_option_def%ROWTYPE; > newrows record; Possibly, Grant's advice will help you. Maybe around the description of CREATE TYPE. http://archives.postgresql.org/pgsql-hackers/2002-09/msg01741.php Regards, Masaru Sugawara
dynamic type create (was return setof record from function with dynamic query)
От
Toby Tremayne
Дата:
-----BEGIN PGP SIGNED MESSAGE----- Hash: MD5 Hi Masaru, thanks for that - using Grant's instructions I created a new type and set it up that way - works beautifully. New question - is there any way to dynamically create a type? IE build a list of column names for a new type from the results of a select? Toby Thursday, December 19, 2002, 2:32:29 AM, you wrote: MS> On Wed, 18 Dec 2002 17:18:56 +1100 MS> Toby Tremayne <tobyt@toll.com.au> wrote: >> ERROR: A column definition list is required for functions returning RECORD >> >> I'd really appreciate it if someone could show me how to pass back >> a set of records please??? >> >> Toby >> >> CREATE FUNCTION poptions (INTEGER) >> RETURNS setof record AS ' >> DECLARE >> -- parameters >> p_author_id ALIAS FOR $1; >> >> -- local variables >> row_data poem_option_def%ROWTYPE; >> newrows record; MS> Possibly, Grant's advice will help you. Maybe around the description MS> of CREATE TYPE. MS> http://archives.postgresql.org/pgsql-hackers/2002-09/msg01741.php MS> Regards, MS> Masaru Sugawara --------------------------------------- Life is poetry - write it in your own words. --------------------------------------- Toby Tremayne Technical Team Lead Code Poet and Zen Master of the Heavy Sleep Toll Solutions 154 Moray St Sth Melbourne VIC 3205 +61 3 9697 2317 0416 048 090 ICQ: 13107913 -----BEGIN PGP SIGNATURE----- Version: 2.6 iQCVAwUAPgJ34kYhrxxXvPlFAQGcMAP+Mf0i2Votx1Ca80xpY1duQvva8BzqOPL8 alF4qZd7bvc+j09XDQc+z5W1r/DAYG4oDsb1HT+R/oWkFz8+crXw94T/nCfOS9bb lYkUPZ+R5tXlsHEwiTQ1B/mJxwgp1VWDxDHzCG4hJyjBTRNoJ5C6VcJqZtx/P2cU Wl0jEand1/0= =jGX0 -----END PGP SIGNATURE-----