Select / display the last entered serial id in postgres

Question

in sql server I like this:

insert into foo(name) values('bob') select @@identity; 

so I get the displayed query / scalar result

how to do it with postgres?

Answer 1

Get a specific sequence:

 SELECT currval('name_of_your_sequence'); 

Get the last value from the last used sequence:

 SELECT lastval(); 

Check also the manual: http://www.postgresql.org/docs/current/static/functions-sequence.html

Edit: you can also use RETURNING in your INSERT:

 INSERT INTO foo(id, name) VALUES(DEFAULT, 'bob') RETURNING id; 

Answer 2

A good way is to use a RETURNING id. Here is a quick example of using PL / pgSQL:

  DECLARE nivel1 RECORD; resultId BIGINT; BEGIN FOR nivel1 IN SELECT * FROM primary_table LOOP INSERT INTO second_table(id, field2, field3) VALUES (DEFAULT, "value2", "value3") RETURNING id INTO resultId; RAISE NOTICE 'Inserted id: %s', quote_literal(resultId); END LOOP; RETURN; END 

It works for me!

Answer 3

This will

GET DIAGNOSTICS YourParam = RESULT_OID;

See here http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html scroll down to 37.6.6. Obtaining result status

Poll Editor: I tried this:

 create or replace function aaa() returns int as $$ declare a int; begin insert into oameni values(default, 'aaa'); get diagnostics a = result_oid; return a; end; $$ language plpgsql; 

it always returns 0, you know what is wrong here?