From: Ron Peterson <rpeterso@mtholyoke.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: insert rule doesn't see id field
Date: Mon, 13 Jan 2003 17:11:29 -0500
Message-ID: <20030113221129.GA32653@mtholyoke.edu> (raw)
In-Reply-To: <13558.1041955268@sss.pgh.pa.us>
References: <20030107145422.GE12245@mtholyoke.edu>
<13558.1041955268@sss.pgh.pa.us>
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 ----
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: rpeterso@mtholyoke.edu, tgl@sss.pgh.pa.us
Subject: Re: insert rule doesn't see id field
In-Reply-To: <20030113221129.GA32653@mtholyoke.edu>
* 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