7

in sql server I do like this:

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

so I get a query/scalar result displayed

how to this with postgres ?

il_guru
  • 8,383
  • 2
  • 42
  • 51
Omu
  • 69,856
  • 92
  • 277
  • 407
  • 3
    Don't use `@@identity` in SQL Server. Using [SCOPE_IDENTITY()](http://msdn.microsoft.com/en-us/library/ms190315.aspx) instead is safer to avoid potential problems with triggers. – Joe Stefanelli Sep 10 '10 at 19:30

3 Answers3

27

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
  • 117,544
  • 24
  • 142
  • 135
4

A good way is using RETURNING id. Here is a short example 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!

Ian Hazzard
  • 7,661
  • 7
  • 34
  • 60
hermeslm
  • 1,535
  • 16
  • 13
2

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?

Omu
  • 69,856
  • 92
  • 277
  • 407
SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • yes and I want it to be immediately displayed, equivalent for select @@identity – Omu Sep 10 '10 at 19:34
  • so dump in variable and do SELECT variable – SQLMenace Sep 10 '10 at 19:35
  • 1
    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 Sep 11 '10 at 16:17