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