returning setof from insert ?

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 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/

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;

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

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 try to remember RETURN NEXT in the future !