Thread: plpgsql : column definition list

The result of the function test() should be a list of integer coming from a
column "ech_id" from a recursive function
data.sp_ech_recurs_desc_a(integer).

I have the following message:
ERROR:  a column definition list is required for functions returning
"record"

How to define this list in this function ?

Thanks
--------
CREATE OR REPLACE FUNCTION test()
  RETURNS SETOF record AS
'
declare
    parent integer;
    rec RECORD;
begin
    ... a "SELECT INTO parent ..." giving a value to the variable parent
...
    FOR rec IN SELECT ech_id from data.sp_ech_recurs_desc_a(parent) LOOP
        RETURN NEXT rec;
    END LOOP;
return;
end
'
  LANGUAGE 'plpgsql' VOLATILE;
------------

Masse Jacques wrote:
> I have the following message:
> ERROR:  a column definition list is required for functions returning
> "record"

> CREATE OR REPLACE FUNCTION test()
>   RETURNS SETOF record AS
make this
    RETURNS SETOF int AS

> '
> declare
>     parent integer;
>     rec RECORD;
> begin
>     ... a "SELECT INTO parent ..." giving a value to the variable parent
> ...
>     FOR rec IN SELECT ech_id from data.sp_ech_recurs_desc_a(parent) LOOP
>         RETURN NEXT rec;
and make this
        RETURN NEXT rec.ech_id;

You only want to return type "record" for composite (multicolumn) types
that are not knowable in advance of writing the query (i.e. that might
vary from call to call).

HTH,

Joe