INSERT with return value

[jafotinatos]

Hello all,

I want to do an insert statement in postgres that returns the value of the id (sequence) that was automatically created, all in one step.

For example i ahve the table like so

create table myTable (
serial id, date entry_date, integer value )

and get something like this:

INSERT INTO myTable (entry_date,value) VALUES ('4/5/1999',4) RETURNING id;

Any ideas anyone? Thanks!

 

[ceco]

no there is no RETURNING in postgresql like in oracle ;-(

you can do it with procedure
or just by call to current val of the sequence (which is not one step)

 

[szmgg]

Hello!
I've done it this way:
select nexval('public.partners_id_seq'::text) as blabla
(blabla will never be same)


Then:
insert into partners (id, name) values (blabla, 'Minta Béla')

I don't know how to use variables in pgsql, so blabla is only a something.

 

[ceco]

here

http://www.postgresql.org/docs/7.4/interactive/plpgsql.html

is a quite good documentation of plpgsql

variavles you decalre with

DECLARE
quantity integer;
name varchar;

before the BEGIN statment
___________________________
about the sequences, you can do it the other way if you use serial as datatype of id, but basicly it is the same

insert into partners (name) values ('Minta Béla');
select currval('public.partners_id_seq'::text) as blah;