GET LAST ID INSERT

On Tue, 2003-09-23 at 09:24, Maurizio Faini wrote:

there is a way to get last id inserted into db or i have to make a new
query?

Typically, you will have created the table with a SERIAL column, whose
default value is taken from a sequence:

junk=# CREATE TABLE xxx (
junk(# id SERIAL PRIMARY KEY,
junk(# yyy TEXT
junk(# );
NOTICE: CREATE TABLE will create implicit sequence "xxx_id_seq" for
SERIAL column "xxx.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"xxx_pkey" for table "xxx"
CREATE TABLE

Insert a row:
junk=# INSERT INTO xxx VALUES (DEFAULT, 'ljhldwtuy');
INSERT 1341077 1

Now get the value just created by the sequence:
junk=# SELECT currval('xxx_id_seq');
currval
---------
1
(1 row)

junk=# SELECT * FROM xxx WHERE id = 1;
id | yyy
----+-----------
1 | ljhldwtuy
(1 row)

Another way to do it, is to use the value returned by the INSERT
statement. If the table has oids, the first number returned (1341077 in
the example above) is the oid of the row just inserted. However, this
only works when a single row is inserted in a table with oids.

junk=# SELECT * FROM xxx WHERE oid = 1341077;
id | yyy
----+-----------
1 | ljhldwtuy
(1 row)

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And we know that all things work together for good to
them that love God, to them who are the called
according to his purpose."
Romans 8:28

I'm using PHP in my App, and PHP have last_oid function - using this
function I've wrote function in plpgsql to get last inserted id:

CREATE FUNCTION "public"."last_id" (bigint, varchar, varchar) RETURNS
text AS'
DECLARE
var_result RECORD;
var_query VARCHAR;
var_id TEXT;
BEGIN
var_query := ''SELECT '' || $3 ||'' AS __id FROM '' || $2 || '' WHERE
oid = '' || $1 || '';'';
IF $1 > 0 THEN
FOR var_result IN EXECUTE var_query LOOP
var_id := var_result.__id;
END LOOP;
IF var_id > 0 THEN
RETURN var_id;
ELSE
RETURN NULL;
END IF;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY
INVOKER;

Where:
Var $1 is "oid number"
Var $2 is "table_name"
Var $3 is "id_field_name"

Of course we can change this function and get table name automaticaly...
But in my case there is was need for this... But you can try.

Best wishes,
ML

olly@lfix.co.uk (Oliver Elphick) wrote in message news:<1064381733.6612.13.camel@linda.lfix.co.uk>...

On Tue, 2003-09-23 at 09:24, Maurizio Faini wrote:

there is a way to get last id inserted into db or i have to make a new
query?

Typically, you will have created the table with a SERIAL column, whose
default value is taken from a sequence:

junk=# CREATE TABLE xxx (
junk(# id SERIAL PRIMARY KEY,
junk(# yyy TEXT
junk(# );
NOTICE: CREATE TABLE will create implicit sequence "xxx_id_seq" for
SERIAL column "xxx.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"xxx_pkey" for table "xxx"
CREATE TABLE

Insert a row:
junk=# INSERT INTO xxx VALUES (DEFAULT, 'ljhldwtuy');
INSERT 1341077 1

Now get the value just created by the sequence:
junk=# SELECT currval('xxx_id_seq');
currval
---------
1
(1 row)

junk=# SELECT * FROM xxx WHERE id = 1;
id | yyy
----+-----------
1 | ljhldwtuy
(1 row)

Another way to do it, is to use the value returned by the INSERT
statement. If the table has oids, the first number returned (1341077 in
the example above) is the oid of the row just inserted. However, this
only works when a single row is inserted in a table with oids.

junk=# SELECT * FROM xxx WHERE oid = 1341077;
id | yyy
----+-----------
1 | ljhldwtuy
(1 row)

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And we know that all things work together for good to
them that love God, to them who are the called
according to his purpose."
Romans 8:28

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Thank you very much!
I tried to use CURRVAL, but it gives me this error:
IS NOT YET DEFINED IN THIS SECTION

this is what i do:
1) INSERT (id,....) VALUES (nextval('...id_seq'),.....etc...
2) SELECT currval('...id_seq');
and now there is error.
3) get rs
4) another insert query with rs current val

why this not work?
where i make a mistake?

thank you.

On Wed, Sep 24, 2003 at 09:04:49AM +0100, Marek Lewczuk wrote:

BEGIN
var_query := ''SELECT '' || $3 ||'' AS __id FROM '' || $2 || '' WHERE
oid = '' || $1 || '';'';

Note that tables can be created without oids, and oids are not
automatically indexed. So this function might not work in future,
and will possibly be very slow.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Wed, 2003-09-24 at 11:49, Maurizio Faini wrote:

I tried to use CURRVAL, but it gives me this error:
IS NOT YET DEFINED IN THIS SECTION

That indicates that in the current session you haven't yet used
nextval() on the id specified in the currval() call.

this is what i do:
1) INSERT (id,....) VALUES (nextval('...id_seq'),.....etc...
2) SELECT currval('...id_seq');
and now there is error.
3) get rs
4) another insert query with rs current val

why this not work?
where i make a mistake?

I can't see what is wrong here. Please post a transcript of the actual
session; your editing has wiped out whatever you did wrong.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And we know that all things work together for good to
them that love God, to them who are the called
according to his purpose."
Romans 8:28

I've found my mistake: I was closing my connection between first and
second point....

Now works.....thank you very much!!!!!

olly@lfix.co.uk (Oliver Elphick) wrote in message news:<1064426468.6612.375.camel@linda.lfix.co.uk>...