Thread: 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)
From
Toby Tremayne
Date:
-----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-----