On Mon, 10 Jul 2000, Tom Lane wrote:
> However, I still prefer the SELECT nextval() followed by INSERT approach
> over INSERT followed by SELECT currval(). It just feels cleaner.
Just an aside. We use a system similar to MySQL's "auto_increment" system to
get the value. What we do is have a function that will return CURRVAL of the
first defaulted int4 column of the table in question. This query gets the
default clause:
SELECT d.adsrc, a.attnum, a.attname
FROM pg_class c, pg_attribute a, pg_attrdef d, pg_type t
WHERE c.relname = ? AND a.attnum > 0 AND a.attrelid = c.oid AND d.adrelid = c.oid AND a.atthasdef = true AND
d.adnum= a.attnum AND a.atttypid = t.oid AND t.typname = 'int4'
ORDER BY a.attnum
LIMIT 1
Then we just pull out the part in the nextval('.....') and return the currval
of that string. Works like a charm. This is done in perl, so when we need the
last insert id, we just call:
$id = get_insert_id($dbh, $table);
Anyways, its easy enough to get at the information this way without making your
application depend on OID values. Yes, you might still get bunt by triggers.
I am not sure if there is an easy solution to that.
Mike
