Re: NEW variable values in actions in rules

Tom ~

Your answers (below) to my questions about the values of NEW columns in
trigger functions was very clarifying for me (as well as being correct!).
Now I'm having similar problems with NEW values in actions that are embedded
in rules, and the answers don't seem to be the same.

I have a table "person_h" with a not-null column "effective_date_and_time"
that defaults to CURRENT_TIMESTAMP.

I have a view "person" with the following rule defined on it:

CREATE RULE on_insert AS
    ON INSERT TO person
    DO INSTEAD (
        INSERT INTO person_i (person_id, birth_date)
            VALUES (nextval('pop_seq'::text), new.birth_date);
        INSERT INTO person_h (person_id, ...
effective_date_and_time,             ...)
        VALUES (currval('pop_seq'::text), last_name, ...
new.effective_date_and_time, new.last_name, ...);

where the "..."s are some other columns not of interest here.

So now when I do this query:

insert into public.person (last_name) values ('Jones');

I get this error:

PostgreSQL Error Code: (1)
ERROR:  null value in column "effective_date_and_time" violates not-null
constraint--0 Rows Affected

So the query processor seems not to be behaving as you described in
answering my question 2 below: it is not taking the
NEW.effective_date_and_time value to be "whatever the default is for the
column".

If I submit the same "INSERT INTO person_h..." query directly, rather than
as part of the rule, it works fine.

Help?

~ TIA
~ Ken


> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, January 05, 2006 7:49 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] NEW variable values in trigger functions
>
> "Ken Winter" <ken@sunward.org> writes:
> > 1. What is the value of the NEW variable for a column that is not
> mentioned
> > in an UPDATE statement?  Is it NULL?  If not NULL, what?
>
> No, it's whatever value is going to be assigned to the new row (which in
> this particular case would be the same as the OLD value).
>
> > 2. Same questions re the value of a NEW variable that is not assigned a
> > value in an INSERT statement.
>
> Same answer: whatever value is due to go into the row (in this case,
> whatever the default is for the column).
>
> > 3. If an UPDATE query set a column to DEFAULT, what value does a trigger
> > function see for the column's NEW variable?
>
> Same answer.
>
>             regards, tom lane