From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Mon, 13 Jan 2003 17:11:29 -0500
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: insert rule doesn't see id field
Message-ID: <20030113221129.GA32653@mtholyoke.edu>
Mail-Followup-To: Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-sql@postgresql.org
References: <20030107145422.GE12245@mtholyoke.edu>
	<13558.1041955268@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <13558.1041955268@sss.pgh.pa.us>
User-Agent: Mutt/1.3.28i
Organization: Mount Holyoke College

On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote:
> Ron Peterson <rpeterso@mtholyoke.edu> writes:
> > CREATE RULE person_insert AS
> > ON INSERT TO person
> > DO
> >     INSERT INTO person_log ( name_last, name_first, mod_type, person_id )
> >     VALUES ( new.name_last, new.name_first, 'I', new.id );
> > [where id is a serial column]
> 
> > My insert rule creates a record in person_log just fine.  It inserts
> > values for all of the fields except person_id.  Why doesn't new.id
> > contain a value?
> 
> This is a bug in 7.2.*.  It's fixed in 7.3.  However, your rule will
> still not work the way you would like, because rules are macros: the
> default expression for id will get evaluated once in the rule and once
> in your original query, leading to two different sequence numbers
> getting inserted.

I just installed 7.3.1.  It works now, as you say, but it breaks if the
id field being updated by an insert rule references the id field it's
logging.  And like you say, the rule also updates the sequence - not a
killer, but not so great.

I started writing a trigger.  Meanwhile I'm just going to log updates
and deletes.  After all, if a record has never been updated or deleted,
what's to audit?

One thing's tripping me up a bit while writing a trigger (in C - I like
C).  I'd like the trigger function arguments to specify an attribute
mapping from the table being logged to the log table - e.g. logfun
('logtable', 'oldatt1', 'newatt1', 'oldatt2', 'newatt2', etc.).  I
thought I'd be good and check that the types and field lengths match
before attempting to insert the log record.  I can find out this info
for the relation pulling the trigger easy enought, but how would I go
about getting this info when all I have is the table/field name?  I
could create and execute a SQL query something like the following, but
is that really the way to get at this info in C code?

select
  relname,
  attname,
  typname,
  typlen
from
  pg_class c,
  pg_attribute a,
  pg_type t
where
  c.oid=a.attrelid and
  a.atttypid=t.oid and
  get just the relevant record(s);

-- 
Ron Peterson                          -o)
Network & Systems Manager             /\\
Mount Holyoke College                _\_v
http://www.mtholyoke.edu/~rpeterso   ----