Re: Returning recordsets with functions

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