GET LAST ID INSERT

[Maurizio Faini]

I have a little problem.

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

I explain better my question:

in vbscript using sqlserver2000 i can use this code:

varBookmark=rs.Bookmark
rs.Update
rs.Bookmark=varBookmark

in this way in I the new id just inserted!!!

how can i do this with postgres?
there is a way?

thanks

[Oliver Elphick]

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 Ol************@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 ma*******@postgresql.org

[Marek Lewczuk]

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

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of
Maurizio Faini
Sent: Tuesday, September 23, 2003 9:25 AM
To: pg***********@postgresql.org
Subject: [GENERAL] GET LAST ID INSERT
I have a little problem.

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

I explain better my question:

in vbscript using sqlserver2000 i can use this code:

varBookmark=rs.Bookmark
rs.Update
rs.Bookmark=varBookmark

in this way in I the new id just inserted!!!

how can i do this with postgres?
there is a way?

thanks

---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Maurizio Faini]

ol**@lfix.co.uk (Oliver Elphick) wrote in message news:<10**********************@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 Ol************@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 ma*******@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.

[Andrew Sullivan]

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
<an****@libertyrms.info> M2P 2A8
[phone] x110
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Oliver Elphick]

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 Ol************@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 8: explain analyze is your friend

[Maurizio Faini]

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

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


ol**@lfix.co.uk (Oliver Elphick) wrote in message news:<10***********************@linda.lfix.co.uk>. ..

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 Ol************@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 8: explain analyze is your friend