Thread: returning setof from insert ?

Hi,

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

On Wed, Jul 14, 2021 at 1:22 PM Laura Smith
<n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:
>
> Hi,
>
> 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
reallywant 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 can write that either as:

RETURN NEXT v_row;

(the NEXT being the missing keyword)


Or just the whole thing as
RETURN QUERY INSERT INTO ... RETURNING *

and get rid of the variable completely, if the function is that trivial.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

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

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐

On Wednesday, July 14th, 2021 at 1:14 PM, Thomas Kellerer <shammat@gmx.net> wrote:

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


Thank you for the tip on making it cleaner with sql. As you say, no real need for PL/pgSQL in this instance, but I'll
tryto remember RETURN NEXT in the future !