Re: returning setof from insert ?
От | Laura Smith |
---|---|
Тема | Re: returning setof from insert ? |
Дата | |
Msg-id | NFWHSZN3w6lJiGXV0rovh58SQGFjo4pFKp0xpUQrKibcDZ7Qs-ZZhjzODpAgEqFPRx4TGu8egBu30k1cxh4iJfgRNmfF6wgxWI0AKyDCol0=@protonmail.ch обсуждение исходный текст |
Ответ на | Re: returning setof from insert ? (Thomas Kellerer <shammat@gmx.net>) |
Список | pgsql-general |
‐‐‐‐‐‐‐ 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 !
В списке pgsql-general по дате отправления: