Message-Id: <5.1.0.14.0.20020912121233.0224ecc0@cooee.squiz.net>
Date: Thu, 12 Sep 2002 12:21:49 +1000
To: "Beth Gatewood" <beth@vizxlabs.com>, <pgsql-sql@postgresql.org>
From: Chris <csmith@squiz.net>
Subject: Re: writing a function to mimic mysql last_insert_id
In-Reply-To: <GNELIHDDFBOCMGBFGEFOGEDDCEAA.chriskl@familyhealth.com.au>
References: <000701c259b7$f68b3280$0c00000a@bethvizx>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

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.