From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Peterson <rpeterso@mtholyoke.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: insert rule doesn't see id field
Date: Tue, 07 Jan 2003 11:01:08 -0500
Message-ID: <13558.1041955268@sss.pgh.pa.us> (raw)
In-Reply-To: <20030107145422.GE12245@mtholyoke.edu>
References: <20030107145422.GE12245@mtholyoke.edu>
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.
The only way to make this example work is to issue the log insertion
from a trigger, not a rule.
> (Problem 2)
> I thought that the idea behind noup was to protect single columns from
> update. However, when I apply the noup trigger as above, I can't
> update /any/ column. Is this the intended behaviour?
Idly looking at the source code for contrib/noupdate/noup.c, I don't
believe that it has ever worked as advertised: it seems to reject any
non-null value for the target column, independently of whether the
value is the same as before (which is what I'd have thought it should
do).
Is anyone interested in fixing it? Or should we just remove it?
If it's been there since 6.4 and you're the first person to try to use
it, as seems to be the case, then I'd have to say that it's a waste of
space in the distribution.
regards, tom lane
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: tgl@sss.pgh.pa.us, rpeterso@mtholyoke.edu
Subject: Re: insert rule doesn't see id field
In-Reply-To: <13558.1041955268@sss.pgh.pa.us>
* 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