Re: returning setof from insert ?

On Wed, Jul 14, 2021 at 1:22 PM Laura Smith
<n5d9xq3ti233xiyif2vp(at)protonmail(dot)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 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 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/