Thread: writing a function to mimic mysql last_insert_id
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
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 BerkusAglio Database SolutionsSan Francisco
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>
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
>
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 BerkusAglio Database SolutionsSan Francisco
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
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.
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 >
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