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);