pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sequence value of the record just inserted.
8+ messages / 5 participants
[nested] [flat]

* sequence value of the record just inserted.
@ 2004-04-09 14:59 Ben Kim <bkim@coe.tamu.edu>
  2004-04-09 17:20 ` Re: sequence value of the record just inserted. scott.marlowe <scott.marlowe@ihs.com>
  2004-04-09 21:00 ` Re: sequence value of the record just inserted. Jim Seymour <jseymour@LinxNet.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Ben Kim @ 2004-04-09 14:59 UTC (permalink / raw)
  To: pgsql-admin


Dear admins,

I have a table whose primary key is a record_id with serial type. 

I would like to know, when I insert a new row, what was the value of the
record_id that I just inserted. Since this is a multi user application, I
cannot simply select max of the record_id or currval of the sequence.

I would appreciate an advice.

Regards,
Ben Kim




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: sequence value of the record just inserted.
  2004-04-09 14:59 sequence value of the record just inserted. Ben Kim <bkim@coe.tamu.edu>
@ 2004-04-09 17:20 ` scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 8+ messages in thread

From: scott.marlowe @ 2004-04-09 17:20 UTC (permalink / raw)
  To: Ben Kim <bkim@coe.tamu.edu>; +Cc: pgsql-admin

On Fri, 9 Apr 2004, Ben Kim wrote:

> 
> Dear admins,
> 
> I have a table whose primary key is a record_id with serial type. 
> 
> I would like to know, when I insert a new row, what was the value of the
> record_id that I just inserted. Since this is a multi user application, I
> cannot simply select max of the record_id or currval of the sequence.
> 
> I would appreciate an advice.

You want the functions for sequences:

nextval, currval, and setval:

http://www.postgresql.org/docs/7.4/static/functions-sequence.html

nextval and currval are transactionally safe.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: sequence value of the record just inserted.
  2004-04-09 14:59 sequence value of the record just inserted. Ben Kim <bkim@coe.tamu.edu>
@ 2004-04-09 21:00 ` Jim Seymour <jseymour@LinxNet.com>
  1 sibling, 0 replies; 8+ messages in thread

From: jseymour@LinxNet.com @ 2004-04-09 21:00 UTC (permalink / raw)
  To: pgsql-admin

Ben Kim <bkim@coe.tamu.edu> wrote:
> 
> 
> Dear admins,
> 
> I have a table whose primary key is a record_id with serial type. 
> 
> I would like to know, when I insert a new row, what was the value of the
> record_id that I just inserted. Since this is a multi user application, I
> cannot simply select max of the record_id or currval of the sequence.

You certainly can use currval();

In session #1 I do...

jseymour=> create table foo3 (bar serial, baz int);
jseymour=> insert into foo3 (baz) values (1);

This will put "1" in for column "bar".

In session #2 I do...

jseymour=> insert into foo3 (baz) values (1);

This will put "2" in for column "bar".

Now back to session #1...

jseymour=> select currval('foo3_bar_seq');
 currval 
---------
       1
(1 row)

And session #2...

jseymour=> select currval('foo3_bar_seq');
 currval 
---------
       2
(1 row)

HTH,
Jim



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: sequence value of the record just inserted.
@ 2004-04-14 14:34 Ben Kim <bkim@coe.tamu.edu>
  2004-04-15 02:01 ` Re: sequence value of the record just inserted. Greg Sabino Mullane <greg@turnstep.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Ben Kim @ 2004-04-14 14:34 UTC (permalink / raw)
  To: pgsql-admin


Thanks to those who offered help,

The suggested solutions are,
1. Select nextval('myseq'), then use the nextval as the new id to
insert a new record. 
2. Insert a new record, then do select currval('myseq') or select last_val
from myseq; supposed to be safe 
3. use transaction to guarantee safety

As for solution 2, I wonder what the scope of a "session" is. If I call a
perl subroutine from a web page (the subroutine opens a db handle and
closes it at the end of the subroutine), would it count as one session?
I'll need to check more but would appreciate it if anyone has a ready
advice on this aspect.

Also, in perl DBI, the solutions all require executing at least 2 sql
statements.

I initially hoped there'd be a way to get the oid or sequence number at
the same time as executing an INSERT (one sql statement), like I get oid 
in psql. But I use perl DBI, so am not sure how I can get the oid into a
perl variable. 

I appreciate the help.


Regards,
Ben Kim





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: sequence value of the record just inserted.
  2004-04-14 14:34 Re: sequence value of the record just inserted. Ben Kim <bkim@coe.tamu.edu>
@ 2004-04-15 02:01 ` Greg Sabino Mullane <greg@turnstep.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Greg Sabino Mullane @ 2004-04-15 02:01 UTC (permalink / raw)
  To: pgsql-admin


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
> Also, in perl DBI, the solutions all require executing at least
> 2 sql statements.
 
Nothing wrong with that.
 
> I initially hoped there'd be a way to get the oid or sequence number
> at the same time as executing an INSERT (one sql statement), like I
> get oid in psql. But I use perl DBI, so am not sure how I can get the
> oid into a perl variable.
 
See the documentation for pg_oid_status in DBD::Pg.
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404142201
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFAfe07vJuQZxSWSsgRAsFgAKCw3NkMiZiwZb5gjxu1Q+Nj9wwkpACcDj1n
gxSIKAGNJefZaJCmU6+tpgs=
=GLPy
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* sequence value of the record just inserted / DBI
@ 2004-04-14 17:53 Ben Kim <bkim@coe.tamu.edu>
  2004-04-14 19:22 ` Re: sequence value of the record just inserted / DBI Jeremy Buchmann <jeremy@wellsgaming.com>
  2004-04-15 01:55 ` Re: sequence value of the record just inserted / DBI Greg Sabino Mullane <greg@turnstep.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Ben Kim @ 2004-04-14 17:53 UTC (permalink / raw)
  To: pgsql-admin


I hope it is OK to ask the question here.

Perl's DBI has the following function.

$rv = $dbh->last_insert_id($catalog, $schema, $table, $field);  
"Returns a value 'identifying' the row just inserted, if possible."

I wonder what this $catalog is in this context. It's supposed to be
metadata, and CREATE USER has CATALOG|NOCATALOG option, but what would be
$catalog value for a user table in this context?


Thanks in advance.

Ben Kim





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: sequence value of the record just inserted / DBI
  2004-04-14 17:53 sequence value of the record just inserted / DBI Ben Kim <bkim@coe.tamu.edu>
@ 2004-04-14 19:22 ` Jeremy Buchmann <jeremy@wellsgaming.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Jeremy Buchmann @ 2004-04-14 19:22 UTC (permalink / raw)
  To: pgsql-admin

On Apr 14, 2004, at 10:53 AM, Ben Kim wrote:

>
> I hope it is OK to ask the question here.
>
> Perl's DBI has the following function.
>
> $rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
> "Returns a value 'identifying' the row just inserted, if possible."
>
> I wonder what this $catalog is in this context. It's supposed to be
> metadata, and CREATE USER has CATALOG|NOCATALOG option, but what would 
> be
> $catalog value for a user table in this context?

Ben,

I don't know what $catalog is supposed to be, but you may have better 
luck on the DBI-Users list or the PGSQL-INTERFACES list.


--Jeremy




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: sequence value of the record just inserted / DBI
  2004-04-14 17:53 sequence value of the record just inserted / DBI Ben Kim <bkim@coe.tamu.edu>
@ 2004-04-15 01:55 ` Greg Sabino Mullane <greg@turnstep.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Greg Sabino Mullane @ 2004-04-15 01:55 UTC (permalink / raw)
  To: pgsql-admin


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
> Perl's DBI has the following function.
>
> $rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
> "Returns a value 'identifying' the row just inserted, if possible."
>
> I wonder what this $catalog is in this context. It's supposed to be
> metadata, and CREATE USER has CATALOG|NOCATALOG option, but what would be
> $catalog value for a user table in this context?
 
DBD::Pg does not support last_insert_id yet. It's so new, that none
of the DBDs support it yet. When it is implemented in DBD::Pg, the catalog
option will be ignored, as PostgreSQL has no catalog concept (nor do most
other RDBM systems). You will notice that most of the other DBI functions
have the same $catalog, $schema, $table ... design. In that context,
catalog is meant as a higher-level than the schema: you can think of it
as a collection of schemas.
 
Keep in mind the last_insert_id() function is mostly meant as a portability
helper: unless you are porting your script to other databases, just
use nextval (number 2 in your other email) which is perfectly safe.
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404142155
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFAfevzvJuQZxSWSsgRAo1dAJ4s/bYbJjubg9IaC91PlpUF5DFX1wCgxgTa
IMLESlXMRgNm+TLbYFV2+zQ=
=OScZ
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-04-15 02:01 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-09 14:59 sequence value of the record just inserted. Ben Kim <bkim@coe.tamu.edu>
2004-04-09 17:20 ` scott.marlowe <scott.marlowe@ihs.com>
2004-04-09 21:00 ` Jim Seymour <jseymour@LinxNet.com>
2004-04-14 14:34 Re: sequence value of the record just inserted. Ben Kim <bkim@coe.tamu.edu>
2004-04-15 02:01 ` Greg Sabino Mullane <greg@turnstep.com>
2004-04-14 17:53 sequence value of the record just inserted / DBI Ben Kim <bkim@coe.tamu.edu>
2004-04-14 19:22 ` Re: sequence value of the record just inserted / DBI Jeremy Buchmann <jeremy@wellsgaming.com>
2004-04-15 01:55 ` Re: sequence value of the record just inserted / DBI Greg Sabino Mullane <greg@turnstep.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox