‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Wednesday, July 14th, 2021 at 1:14 PM, Thomas Kellerer <shammat@xxxxxxx> 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 !
