pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwriting a function to mimic mysql last_insert_id
9+ messages / 6 participants
[nested] [flat]
* writing a function to mimic mysql last_insert_id
@ 2002-09-11 17:23 Beth Gatewood <beth@vizxlabs.com>
2002-09-11 17:30 ` Re: writing a function to mimic mysql last_insert_id Josh Berkus <josh@agliodbs.com>
2002-09-11 17:32 ` Re: writing a function to mimic mysql last_insert_id Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-12 02:05 ` Re: writing a function to mimic mysql last_insert_id Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-09-12 02:21 ` Re: writing a function to mimic mysql last_insert_id Chris <csmith@squiz.net>
0 siblings, 4 replies; 9+ messages in thread
From: Beth Gatewood @ 2002-09-11 17:23 UTC (permalink / raw)
To: pgsql-sql
Hi all-
I am wondering if I can get some input on the logic that I would need to
code a function that would mimic mysql's last_insert_id()..
Here is what last_insert_id() does in mysql:
Returns the last automatically generated value that was inserted into an
AUTO_INCREMENT column.
mysql> SELECT LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a
per-connection basis. It will not be changed by another client. It will not
even be changed if you update another AUTO_INCREMENT column with a non-magic
value (that is, a value that is not NULL and not 0). If you insert many rows
at the same time with an insert statement, LAST_INSERT_ID() returns the
value for the first inserted row. The reason for this is so that you it
makes it possible to easily reproduce the same INSERT statement against some
other server. If expr is given as an argument to LAST_INSERT_ID(), then the
value of the argument is returned by the function, and is set as the next
value to be returned by LAST_INSERT_ID(). This can be used to simulate
sequences: First create the table:
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Then the table can be used to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
You can generate sequences without calling LAST_INSERT_ID(), but the utility
of using the function this way is that the ID value is maintained in the
server as the last automatically generated value (multi-user safe). You can
retrieve the new ID as you would read any normal AUTO_INCREMENT value in
MySQL. For example, LAST_INSERT_ID() (without an argument) will return the
new ID. The C API function mysql_insert_id() can also be used to get the
value. Note that as mysql_insert_id() is only updated after INSERT and
UPDATE statements, so you can't use the C API function to retrieve the value
for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or
SET.
FROM: http://www.mysql.com/doc/en/Miscellaneous_functions.html
Thanks for any pointers on how to tackle this problem (and if it is possible
;-)
I am thinking that I need to store in a table for each transaction whatever
autoincremented values were created (which they are for all my primary
keys)????
-Beth
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
@ 2002-09-11 17:30 ` Josh Berkus <josh@agliodbs.com>
2002-09-11 17:43 ` Re: writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
3 siblings, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2002-09-11 17:30 UTC (permalink / raw)
To: Beth Gatewood <beth@vizxlabs.com>; pgsql-sql
Beth,
> I am wondering if I can get some input on the logic that I would need to
> code a function that would mimic mysql's last_insert_id()..
Um, what about CURRVAL('sequence-name')?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
2002-09-11 17:30 ` Re: writing a function to mimic mysql last_insert_id Josh Berkus <josh@agliodbs.com>
@ 2002-09-11 17:43 ` Beth Gatewood <beth@vizxlabs.com>
2002-09-11 17:53 ` Re: writing a function to mimic mysql last_insert_id Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Beth Gatewood @ 2002-09-11 17:43 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-sql
Josh and Ross-
Thanks...I am well aware of currval and sequence use, and for me too this
would be the easiest solution ;-)
However, we are in the midst of porting a mysql database to pgsql. The perl
application that is tied to this mysql database heavily utilizes
last_insert_id. In an effort to save the perl developers the pain of having
to go in and modify a bunch of their SQL to call out to seq.currval, I
thought I would attempt to mimic this function.
Perhaps there is another way to approach this than writing this type of
function?
Thanks!
Beth
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Wednesday, September 11, 2002 10:31 AM
> To: Beth Gatewood; pgsql-sql@postgresql.org
> Subject: Re: [SQL] writing a function to mimic mysql last_insert_id
>
>
>
> Beth,
>
> > I am wondering if I can get some input on the logic that I
> would need to
> > code a function that would mimic mysql's last_insert_id()..
>
> Um, what about CURRVAL('sequence-name')?
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
2002-09-11 17:30 ` Re: writing a function to mimic mysql last_insert_id Josh Berkus <josh@agliodbs.com>
2002-09-11 17:43 ` Re: writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
@ 2002-09-11 17:53 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2002-09-11 17:53 UTC (permalink / raw)
To: Beth Gatewood <beth@vizxlabs.com>; pgsql-sql
Beth,
> However, we are in the midst of porting a mysql database to pgsql. The perl
> application that is tied to this mysql database heavily utilizes
> last_insert_id. In an effort to save the perl developers the pain of having
> to go in and modify a bunch of their SQL to call out to seq.currval, I
> thought I would attempt to mimic this function.
>
> Perhaps there is another way to approach this than writing this type of
> function?
Not really. CURRVAL requires a sequence name as a parameter because, in
Postgres, one can have more than one sequence for a table. Further, in
Postgres, you can update more than one table at a time using a function,
trigger, or rule; how would any function know which sequence id to return?
While it would be relatively easy for a function to look up what seqeunces
were related to a table in the pg_* tables, you are left with the difficult
task of determining what statement the user last ran. This would, I think,
involve hacking MVCC to some degree.
If you're serious about pursuing this, I suggest posting your question on
PGSQL-HACKERS to get help with hacking MVCC to determine a connection's last
action. I have no idea how easy or difficult this would be.
A second possibility would be writing triggers for all tables that place a
value into a temporary table that can be read back by your custom function.
You may find it less work, however, to do a search-and-replace on calls to
last_inser_id(). But if you do write a replacement function, please post it
on TechDocs!
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
@ 2002-09-11 17:32 ` Ross J. Reedstrom <reedstrm@rice.edu>
3 siblings, 0 replies; 9+ messages in thread
From: Ross J. Reedstrom @ 2002-09-11 17:32 UTC (permalink / raw)
To: Beth Gatewood <beth@vizxlabs.com>; +Cc: pgsql-sql
Beth -
Take a look at pgsql's sequences. They do pretty much exactly what
you want, here. There's even a 'pseudo type' serial that sets up
an int column with a default value coming from a sequence.
Sequences are more loosely couple to 'auto increment' columns than
in mysql or other database systems. This is sometimes a bit of a pain
(in order to retreive the lsat value, you need to know the name of the
sequence, not just the table.column) but also more powerful: you can set
up columns in several tables that draw from one sequence, for example,
which will guarantee non-colliding values.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
On Wed, Sep 11, 2002 at 10:23:35AM -0700, Beth Gatewood wrote:
> Hi all-
>
> I am wondering if I can get some input on the logic that I would need to
> code a function that would mimic mysql's last_insert_id()..
<snip description of mysql's auto increment feature>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
@ 2002-09-12 02:05 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-09-12 02:37 ` Re: writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
3 siblings, 1 reply; 9+ messages in thread
From: Christopher Kings-Lynne @ 2002-09-12 02:05 UTC (permalink / raw)
To: Beth Gatewood <beth@vizxlabs.com>; pgsql-sql
But in Postgres, you can have as many "autoincrement" columns in a table as
you want - how will you handle that?
And what's wrong with select currval()?
Chris
> I am wondering if I can get some input on the logic that I would need to
> code a function that would mimic mysql's last_insert_id()..
>
> Here is what last_insert_id() does in mysql:
> Returns the last automatically generated value that was inserted into an
> AUTO_INCREMENT column.
> mysql> SELECT LAST_INSERT_ID();
> -> 195
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
2002-09-12 02:05 ` Re: writing a function to mimic mysql last_insert_id Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-09-12 02:37 ` Beth Gatewood <beth@vizxlabs.com>
2002-09-12 03:07 ` Re: writing a function to mimic mysql last_insert_id Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 9+ messages in thread
From: Beth Gatewood @ 2002-09-12 02:37 UTC (permalink / raw)
To: 'Christopher Kings-Lynne' <chriskl@familyhealth.com.au>; pgsql-sql
well, I know that I will have only a single sequence that will generate the
primary key per table. So basically, this type of function, for me needs
only to return the value of the primary key.
I believe I mentioned in one of my posts the motivation behind not wanting
to use currval()...which was to trying to avoid having the developers make a
lot of sql revisions to their application.
Thanks-Beth
> -----Original Message-----
> From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
> Sent: Wednesday, September 11, 2002 7:05 PM
> To: Beth Gatewood; pgsql-sql@postgresql.org
> Subject: RE: [SQL] writing a function to mimic mysql last_insert_id
>
>
> But in Postgres, you can have as many "autoincrement" columns
> in a table as
> you want - how will you handle that?
>
> And what's wrong with select currval()?
>
> Chris
>
> > I am wondering if I can get some input on the logic that I
> would need to
> > code a function that would mimic mysql's last_insert_id()..
> >
> > Here is what last_insert_id() does in mysql:
> > Returns the last automatically generated value that was
> inserted into an
> > AUTO_INCREMENT column.
> > mysql> SELECT LAST_INSERT_ID();
> > -> 195
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
2002-09-12 02:05 ` Re: writing a function to mimic mysql last_insert_id Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-09-12 02:37 ` Re: writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
@ 2002-09-12 03:07 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 9+ messages in thread
From: Joe Conway @ 2002-09-12 03:07 UTC (permalink / raw)
To: Beth Gatewood <beth@vizxlabs.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-sql
Beth Gatewood wrote:
> well, I know that I will have only a single sequence that will generate the
> primary key per table. So basically, this type of function, for me needs
> only to return the value of the primary key.
>
> I believe I mentioned in one of my posts the motivation behind not wanting
> to use currval()...which was to trying to avoid having the developers make a
> lot of sql revisions to their application.
Maybe you could use the *same* sequence for the primary key of all the tables,
say "my_global_seq" (it is bigint as of 7.2 I think), and then wrap a
last_insert_id() (or whatever it is called) function around a call to
currval('my_global_seq').
HTH,
Joe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: writing a function to mimic mysql last_insert_id
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
@ 2002-09-12 02:21 ` Chris <csmith@squiz.net>
3 siblings, 0 replies; 9+ messages in thread
From: Chris @ 2002-09-12 02:21 UTC (permalink / raw)
To: Beth Gatewood <beth@vizxlabs.com>; pgsql-sql
Hi Beth,
Here's a function I use to do the same sort of thing - but you need to
supply it with a table name to get it - in the case of standard inserts
it's pretty easy since it's always the third word (so if you're using an
abstraction layer it's easy to change).
It can be written a lot better but it works for me and it was my first
function so :P
This works for 7.1 and the 7.2 series.. but it doesn't cope with errors
very well:
timesheets=# SELECT last_insert_id('task');
NOTICE: Error occurred while executing PL/pgSQL function last_insert_id
NOTICE: line 12 at for over select rows
ERROR: task_taskid_seq.currval is not yet defined in this session
Tweaks appreciated :) I probably don't need half the variables in there but
I haven't revisited it since I got it working.
CREATE FUNCTION "last_insert_id" (character varying) RETURNS text AS '
DECLARE
tbl ALIAS FOR $1;
idxnme TEXT;
idval RECORD;
idv TEXT;
seq RECORD;
seqname TEXT;
BEGIN
FOR seq IN SELECT substring(substring(d.adsrc for 128),
strpos(substring(d.adsrc for 128),''\\'''')+1, (strpos(substring(d.adsrc
for 128),''\\''::'') - strpos(substring(d.adsrc for 128),''\\'''')-1)) as
seq_name FROM pg_attrdef d, pg_class c WHERE c.relname = tbl::text AND
c.oid = d.adrelid AND d.adnum = 1 LOOP
seqname=seq.seq_name;
END LOOP;
FOR idval IN SELECT currval(seqname) AS id LOOP
idv := idval.id;
END LOOP;
RETURN idv;
END;
' LANGUAGE 'plpgsql';
Chris.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-09-12 03:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-11 17:23 writing a function to mimic mysql last_insert_id Beth Gatewood <beth@vizxlabs.com>
2002-09-11 17:30 ` Josh Berkus <josh@agliodbs.com>
2002-09-11 17:43 ` Beth Gatewood <beth@vizxlabs.com>
2002-09-11 17:53 ` Josh Berkus <josh@agliodbs.com>
2002-09-11 17:32 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-09-12 02:05 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-09-12 02:37 ` Beth Gatewood <beth@vizxlabs.com>
2002-09-12 03:07 ` Joe Conway <mail@joeconway.com>
2002-09-12 02:21 ` Chris <csmith@squiz.net>
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