Re: Re: postgres TODO

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