[GENERAL] insert ... returning in plpgsql

[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?


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

Re: [GENERAL] insert ... returning in plpgsql

[Leif Biberg Kristensen]

 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] insert ... returning in plpgsql

[Cédric Villemain]

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


signature.asc
Description: This is a digitally signed message part.

Re: [GENERAL] insert ... returning in plpgsql

[Willy-Bas Loos]

cool, thanks

On Tue, Oct 2, 2012 at 3:13 PM, Cédric Villemain ced...@2ndquadrant.comwrote:

 **

 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




-- 
Quality comes from focus and clarity of purpose -- Mark Shuttleworth