insert ... returning in plpgsql

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: insert ... returning in plpgsql
Date: 2012-10-02 13:01:08
Message-ID: CAHnozThMZYygzmcGQNDuVBb6jW=2j7ZvytR=8eKdWarVW8jhdg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Leif Biberg Kristensen 2012-10-02 13:06:32 Re: insert ... returning in plpgsql
Previous Message Mr Dash Four 2012-10-02 09:36:50 Re: strange permission error