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

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

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