PostgreSQL version: 8.0.3
OS: Win32 (Win 2003 Server)
There is something strange and counterintuitive about the way that
multiple-action PostgreSQL rules work. In the following rule definition
CREATE RULE _rulename_ AS ON _event_ TO _table_
WHERE _condition_
DO (
_command1_;
_command2_;
...;
);
the condition-testing logic is NOT equivalent to
if ( _condition_ ) {
_command1_;
_command2_;
...;
}
as one would assume, but rather more like
if ( _condition_ ) {
_command1_;
}
if ( _condition_ ) {
_command2_;
}
...
It seems that the _condition_ is checked before each of the actions
in the rule. Thus, if _command1_ causes the _condition_ to become
false, _command2_ will not be executed. Here is a complete example:
<SQL>
CREATE SCHEMA test;
CREATE TABLE test.table1
(
id1 int4 NOT NULL,
data1 text,
flag1 bool DEFAULT false
);
CREATE OR REPLACE VIEW test.view1 AS
SELECT table1.id1, table1.data1, table1.flag1
FROM test.table1;
CREATE OR REPLACE RULE upd AS ON UPDATE TO test.view1
DO INSTEAD NOTHING;
-- I know this rule is awkward. Please bear with me.
CREATE OR REPLACE RULE upd_if AS ON UPDATE TO test.view1
DO (
UPDATE test.table1 SET data1 = new.data1 WHERE table1.id1 =
old.id1;
UPDATE test.table1 SET flag1 = true WHERE table1.id1 = old.id1;
);
INSERT INTO test.table1 (id1, data1) VALUES (1, 'foo');
SELECT * FROM test.view1;
-- id1 data1 flag1
-- ----------------------
-- 1 foo FALSE
--
UPDATE test.view1 SET data1 = 'bar' WHERE view1.id1 = 1;
SELECT * FROM test.view1;
-- id1 data1 flag1
-- ----------------------
-- 1 bar TRUE
--
-- So far so good...
-- Now I add to the "upd_if" rule
-- a condition that checks if a similar record already exists
CREATE OR REPLACE RULE upd_if AS ON UPDATE TO test.view1
WHERE ((SELECT count(*) AS count1 FROM test.table1 WHERE table1.data1 =
new.data1)) = 0
DO (
UPDATE test.table1 SET data1 = new.data1 WHERE table1.id1 =
old.id1;
UPDATE test.table1 SET flag1 = true WHERE table1.id1 = old.id1;
);
-- Start with fresh data
DELETE FROM test.table1;
INSERT INTO test.table1 (id1, data1) VALUES (1, 'foo');
SELECT * FROM test.view1;
-- id1 data1 flag1
-- ----------------------
-- 1 foo FALSE
--
UPDATE test.view1 SET data1 = 'bar' WHERE view1.id1 = 1;
SELECT * FROM test.view1;
-- id1 data1 flag1
-- ----------------------
-- 1 bar FALSE
--
-- Only the first of the 2 commands in the "upd_if" rule was executed!
-- The second command is silently ignored.
--
</SQL>
I haven't found anything in the doc or in the list archives explicitly
addressing this point. Is this a bug or a feature?
Gene
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
