Thread: What type is required to capture the RETURNING from anUpdate?

Within 8.4, If I have an update like the following :


   EXECUTE $c$
    UPDATE field_seasons dr
        SET season_id = ds.season_id, field_id = ds.field_id
    FROM data_set ds
    WHERE ds.data_set_id = dr.data_set_id
    AND ds.season_id = _season_id
    RETURNING field_seasons_id
    $c$
   INTO _id_array;


in a pgsql FUNCTION... What is the type on the variable that can accept
the list of id's?

integer[] doesn't work.  It gives back "22P02 array value must start
with "{" or dimension information."


Roxanne

rox <rox@tara-lu.com> writes:
> Within 8.4, If I have an update like the following :
>    EXECUTE $c$
>     UPDATE field_seasons dr
>         SET season_id = ds.season_id, field_id = ds.field_id
>     FROM data_set ds
>     WHERE ds.data_set_id = dr.data_set_id
>     AND ds.season_id = _season_id
>     RETURNING field_seasons_id
>     $c$
>    INTO _id_array;

> in a pgsql FUNCTION... What is the type on the variable that can accept
> the list of id's?

There is none, because it isn't a list it's a row set.

Personally I'd try something like

    FOR id IN UPDATE ... RETURNING field_seasons_id
    LOOP
        ... do something with id ...
    END LOOP

(see "Looping Through Query Results" in the plpgsql manual).
AFAICS there's no need to use EXECUTE here.

            regards, tom lane