Thread: writing a function to mimic mysql last_insert_id

writing a function to mimic mysql last_insert_id

From
"Beth Gatewood"
Date:
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



Re: writing a function to mimic mysql last_insert_id

From
Josh Berkus
Date:
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



Re: writing a function to mimic mysql last_insert_id

From
"Ross J. Reedstrom"
Date:
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>



Re: writing a function to mimic mysql last_insert_id

From
"Beth Gatewood"
Date:
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
>



Re: writing a function to mimic mysql last_insert_id

From
Josh Berkus
Date:
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



Re: writing a function to mimic mysql last_insert_id

From
"Christopher Kings-Lynne"
Date:
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



Re: writing a function to mimic mysql last_insert_id

From
Chris
Date:
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.



Re: writing a function to mimic mysql last_insert_id

From
"Beth Gatewood"
Date:
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
>



Re: writing a function to mimic mysql last_insert_id

From
Joe Conway
Date:
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