Обсуждение: insert ... returning in plpgsql
(postgres 9.1)
I was doing something like this in a plpgsql function, but i got a Syntax Error.
t_var:=(insert into table1(field2) values ('x') returning field1);
Is there no support for using RETURNING in insert, update, delete queries to fill a variable in plpgsql?
Here's some code. Returns/Notices as --comment:
create table table1(field1 serial primary key, field2 text not null);
--NOTICE: CREATE TABLE will create implicit sequence "table1_field1_seq" for serial column "table1.field1"
--NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
--Query returned successfully with no result in 224 ms.
create or replace function test(p_val text) returns integer as $$
declare
t_out integer;
begin
t_out:=(insert into table1(field2) values ($1) returning field1);
return t_out;
end $$
language plpgsql strict;
--ERROR: syntax error at or near "into"
--LINE 6: t_out:=(insert into table1(field2) values ($1) returning fi...
insert into table1(field2) values ('a') returning field1
--1
Cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
Tirsdag 2. oktober 2012 15.01.08 skrev Willy-Bas Loos : > Hi, > (postgres 9.1) > I was doing something like this in a plpgsql function, but i got a Syntax > Error. > > t_var:=(insert into table1(field2) values ('x') returning field1); > > Is there no support for using RETURNING in insert, update, delete queries > to fill a variable in plpgsql? Try insert into table1(field2) values ('x') returning field1 into t_var regards, Leif
Le mardi 2 octobre 2012 15:01:08, Willy-Bas Loos a écrit :
> Hi,
> (postgres 9.1)
> I was doing something like this in a plpgsql function, but i got a Syntax
> Error.
>
> t_var:=(insert into table1(field2) values ('x') returning field1);
>
> Is there no support for using RETURNING in insert, update, delete queries
> to fill a variable in plpgsql?
Use the following :
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;
http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html
Else if you have multiple rows do something like:
FOR my_update IN UPDATE ... RETURNING * LOOP
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Вложения
Le mardi 2 octobre 2012 15:01:08, Willy-Bas Loos a écrit :
> Hi,
> (postgres 9.1)
> I was doing something like this in a plpgsql function, but i got a Syntax
> Error.
>
> t_var:=(insert into table1(field2) values ('x') returning field1);
>
> Is there no support for using RETURNING in insert, update, delete queries
> to fill a variable in plpgsql?
Use the following :
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;
http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html
Else if you have multiple rows do something like:
FOR my_update IN UPDATE ... RETURNING * LOOP
--
Cédric Villemain +33 (0)6 20 30 22 52
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth