ansaurus

Question

select/display last inserted serial id in posgres

Answer 1

+1 A:

It would be

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 the Result Status

asker edit: 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's wrong here?

SQLMenace 2010-09-10 19:33:28

yes and I want it to be immediately displayed, equivalent for select @@identity

Omu 2010-09-10 19:34:42

so dump in variable and do SELECT variable

SQLMenace 2010-09-10 19:35:55

@SQLMenace I've edit you answer, could you take a look

Omu 2010-09-10 19:50:47

RESULT_OID does not return the value of the SERIAL column, it returns the internal OID of the record, if it exists. Two things: (1) the OID should NEVER be used as a record ID, it is for internal use only and (2) In later versions of PostgreSQL, OIDs are turned of for tables by default. That most likely explains your return value of zero (from the linked docs): "RESULT_OID is only useful after an INSERT command into a table containing OIDs" The proper way to get the result value is to use the RETURNING clause.

Matthew Wood 2010-09-11 16:17:39

Answer 2

+1 A:

Get a specific sequence:

SELECT currval('name_of_your_sequence');

Get the last value from the last sequence used:

SELECT lastval();

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

Edit: You could also use RETURNING in your INSERT:

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

Frank Heikens 2010-09-10 19:47:51

ansaurus

tags:

views:

answers:

select/display last inserted serial id in posgres

related questions