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