The rule-system seems to evaluate a functional value
of a column anew. Please confim if this is a
documented/intentional behaviour of the rule system?
The order of evaluation of the expresion(s) within
original statemant and rule differ between "on insert"
and "on update" rules.
For details see the following artificial example
logging actions on "tbl" to "log" and using nextval as
an expression (code and output below). As a result the
columns tbl.id and log.id differ, unexpectedly, by
+-1.
regards, Jan
=== SQL ================================
-- EXAMPLE: rule-system revaluates
-- the functionale expressions
CREATE sequence sq ;
CREATE table tbl (
id integer default nextval('sq'),
da char(1) default '-',
ts timestamp default now() );
-- log=tbl without defaults
CREATE table log (
id integer,
da char(1),
ts timestamp );
-- rules for logging actions on "tbl" to "log"
CREATE rule tblog1 AS ON INSERT TO tbl
do insert INTO log (id,da,ts)
values (NEW.id, NEW.da, NEW.ts);
CREATE rule tblog2 AS ON UPDATE TO tbl
do insert INTO log (id,da,ts)
values (NEW.id, NEW.da, NEW.ts);
-- inserts/updates using defaul values
-- using default id :
insert into tbl (da) values ('a');
-- using default id :
insert into tbl (da) values ('b');
-- using explicit expression for id :
insert into tbl (id,da) values (nextval('sq'),'c');
-- using explicit fixed id :
insert into tbl (id,da) values (0,'d');
-- using an expressional but explicit id
update tbl set id=nextval('sq') where id=0;
-- As a result the columns tbl.id and log.id
-- differ by one :
select * from tbl;
select * from log;
-- drop
drop table tbl cascade;
drop table log cascade;
drop sequence sq;
==== OUTPUT ============================
=# select * from tbl;
id | da | ts
----+----+----------------------------
1 | a | 2004-12-14 11:15:21.996594
3 | b | 2004-12-14 11:15:22.002465
5 | c | 2004-12-14 11:15:22.00822
8 | d | 2004-12-14 11:15:22.012329
(4 rows)
=# select * from log;
id | da | ts
----+----+----------------------------
2 | a | 2004-12-14 11:15:21.996594
4 | b | 2004-12-14 11:15:22.002465
6 | c | 2004-12-14 11:15:22.00822
0 | d | 2004-12-14 11:15:22.012329
7 | d | 2004-12-14 11:15:22.012329
(5 rows)