Re: Returning SETOF RECORD

On Mon, 25 Aug 2003, Juan F Diaz wrote:

> Hi, Im trying to return a setof record (plpgsql) but this error message
> appears and i dont know what to do.
> ERROR:  A column definition list is required for functions returning
> RECORD
>
> The function is the following (there may or may not be data in the
> table(s)) right now there is no data in the table (it should return
> null or something).
>
> DECLARE
>         Libro ALIAS FOR $1;
>         Refe ALIAS FOR $2;
>         res RECORD;
>     BEGIN
>         FOR res IN select color, alto, ancho from referencias where
> IdReferencia like Refe and IdLibro                like Libro LOOP
>             RETURN NEXT res;
>         END LOOP;
>         return;
>     END;
>
> And another question, is it possible to call a function that returns a
> setof anything like this:
> select foo();

From some of the languages you can do this, but not plpgsql.

> select * from foo(); ??

If the function returns a setof record, you need to give it the column
name/type information in the query, like:
select * from foo() as bar(color text, alto text, ancho text);