Re: writing a function to mimic mysql last_insert_id

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.