Thread: Returning SETOF RECORD

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();
or the only way is
select * from foo(); ??

thanks a lot

JuanF

Begin forwarded message:

> From: "Patrick Hatcher" <PHatcher@macys.com>
> Date: Mon Aug 25, 2003  11:39:30  AM America/Bogota
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Way to tell what SQL is currently running?
>
> I'm finding that some update process is holding all other transactions
> on a
> particular table.  Is there a way to tell what the Update statement is
> that
> is running?  I tried to use TOP, but it does not provide enough of the
> SQL
> string to help.
> TIA
>
> Patrick Hatcher
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

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