On Thu, 2004-09-23 at 18:28, Tim Penhey wrote: > Robert Fitzpatrick wrote: > > >Can someone point me to some more information or perhaps show an example > >of returning a recordset from a plpgsql function. I'd like to send an > >argument or arguments to the function, do some queries to return a set > >of records. I've done several functions that return one value of one > >type, but nothing that returns a set. > > > > > Ah ha. Someone else trying to find out what I had hunted for. I could > not find an example on the web, so I started writing one. > Not fully complete yet, but here is what is there and I hope it helps. > http://www.scorefirst.com/postgresql.html
Thanks to all, that is very helpful. But when I create your function or a small test function, I get the following error. I am running PostgreSQL 7.4.5, do you know what this means or how I can define the list? ERROR: a column definition list is required for functions returning "record" I have another question. I was getting a syntax error when trying to create the function on the page linked above: ERROR: syntax error at or near "INTEGER" at character 64 I made my own test function with trying to put my own variable names in the arguments list and it created without the syntax error, but now back to the first problem. Here is what I have so far: CREATE OR REPLACE FUNCTION "public"."test" (varchar) RETURNS SETOF "pg_catalog"."record" AS' DECLARE row_ RECORD; cursor_ CURSOR FOR SELECT * FROM tblhudreports WHERE rems_id=$1; BEGIN OPEN cursor_; LOOP FETCH cursor_ INTO row_; EXIT WHEN NOT FOUND; RETURN NEXT row_; END LOOP; CLOSE cursor_; RETURN; END; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ohc=# select * from test('800000061'); ERROR: a column definition list is required for functions returning "record" ohc=# -- Robert ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org