From: Chris <csmith@squiz.net>
To: Beth Gatewood <beth@vizxlabs.com>
To: pgsql-sql@postgresql.org
Subject: Re: writing a function to mimic mysql last_insert_id
Date: Thu, 12 Sep 2002 12:21:49 +1000
Message-ID: <5.1.0.14.0.20020912121233.0224ecc0@cooee.squiz.net> (raw)
In-Reply-To: <GNELIHDDFBOCMGBFGEFOGEDDCEAA.chriskl@familyhealth.com.au>
References: <000701c259b7$f68b3280$0c00000a@bethvizx>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: csmith@squiz.net, beth@vizxlabs.com
Subject: Re: writing a function to mimic mysql last_insert_id
In-Reply-To: <5.1.0.14.0.20020912121233.0224ecc0@cooee.squiz.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox