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.
