Thread: 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
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
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Maurizio Faini
> Sent: Tuesday, September 23, 2003 9:25 AM
> To: pgsql-general@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
>
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
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, 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>...
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend