Thread: How to catch the id in a INSERT INTO ... RETURNING function?

How to catch the id in a INSERT INTO ... RETURNING function?

From

A B

Date:

01 February 2009, 09:54:03

Hi.
I have a table foo(id serial primary key, b int); and I want an insert function

create or replace function insert_to_foo(bvalue integer) returns integer as
declare
   newindex integer;
begin
     ... insert into foo (a,b) values (default,bvalue) returning id
....    <---- THIS LINE
     -- do more with newindex here
     return  newindex;
end;


Well, the problem is that I want the id of the new post to be saved
into the newindex variable for further actions. But how do I catch the
value into the variable?
Should I do:

select id from insert into foo (a,b) values (default,bvalue) returning id;
?

Resp.: How to catch the id in a INSERT INTO ... RETURNING function?

From

Osvaldo Kussama

Date:

01 February 2009, 12:04:24

2009/2/1, A B <gentosaker@gmail.com>:
> Hi.
> I have a table foo(id serial primary key, b int); and I want an insert
> function
>
> create or replace function insert_to_foo(bvalue integer) returns integer as
> declare
>    newindex integer;
> begin
>      ... insert into foo (a,b) values (default,bvalue) returning id
> ....    <---- THIS LINE
>      -- do more with newindex here
>      return  newindex;
> end;
>
>
> Well, the problem is that I want the id of the new post to be saved
> into the newindex variable for further actions. But how do I catch the
> value into the variable?
> Should I do:
>
> select id from insert into foo (a,b) values (default,bvalue) returning id;
> ?
>


Try:

INSERT ... RETURNING expressions INTO [STRICT] target;

38.5.3. Executing a Query with a Single-Row Result
http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html

Osvaldo

Re: How to catch the id in a INSERT INTO ... RETURNING function?

From

Gerhard Heift

Date:

01 February 2009, 12:05:43

On Sun, Feb 01, 2009 at 11:37:52AM +0100, A B wrote:
> Hi.
> I have a table foo(id serial primary key, b int); and I want an insert function
>
> create or replace function insert_to_foo(bvalue integer) returns integer as
> declare
>    newindex integer;
> begin
>      ... insert into foo (a,b) values (default,bvalue) returning id
> ....    <---- THIS LINE
>      -- do more with newindex here

INSERT INTO foo (a, b) VALUES (DEFAULT, bvalue) RETURNING id INTO newindex;

>      return  newindex;
> end;
>
>
> Well, the problem is that I want the id of the new post to be saved
> into the newindex variable for further actions. But how do I catch the
> value into the variable?
> Should I do:
>
> select id from insert into foo (a,b) values (default,bvalue) returning id;
> ?

See in the manual:
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

Regards,
  Gerhard

Attachment

signature.asc