Re: returning setof from insert ?

[Thomas Kellerer <shammat@xxxxxxx>]

Laura Smith schrieb am 14.07.2021 um 13:22:
> A bit of pl/pgsql writer's block going on here ...
>
> Postgres complains "RETURN cannot have a parameter in function returning set" in relation to the below. I don't really want to have to "RETURNS TABLE" because that means I have to enumerate all the table columns.
>
> I'm sure I'm missing something simple here !
>
> CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$
> DECLARE
> v_row bar%ROWTYPE;
> BEGIN
> insert into bar(f,b) values(foo,bar) returning * into v_row;
> return v_row;
> END;
> $$ language plpgsql;
>

You need to use RETURN NEXT:

    CREATE OR REPLACE FUNCTION foobar(foo text,bar text)
      RETURNS SETOF bar
    AS $$
    DECLARE
      v_row bar%ROWTYPE;
    BEGIN
      insert into bar(f,b) values(foo,bar) returning * into v_row;
      return next v_row;
    END;
    $$
    language plpgsql;

But you don't need PL/pgSQL for this or store the result in a variable:

    CREATE OR REPLACE FUNCTION foobar(foo text,bar text)
      RETURNS SETOF bar
    AS $$
      insert into bar(f,b) values(foo,bar)
      returning *;
    $$
    language sql;