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.