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

> -- 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?
>

Actually the rule is executing as expecting without executing at all
when if founds no rows matching your condition.

but because you don't specify anything it's executing your query and
ALSO the actions in the rule if the condition were pass...

http://www.postgresql.org/docs/8.0/s...reaterule.html
ALSO
ALSO indicates that the commands should be executed in addition to the
original command.

If neither ALSO nor INSTEAD is specified, ALSO is the default.

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 2005-08-04, "Eugene Shekhtman" <postgre@xenomics.com> wrote:
> 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.

The absolute first thing you must learn about using rules in postgresql
is that _rules are not procedural logic_, i.e. you can't express them as
"if (a) then do B". Rules rewrite the command _before_ anything is
executed (indeed before anything is even planned). At the time of
rewriting, there is no way to know whether the WHERE clause of a rule will
be matched, so the rule is always expanded the same way, and the WHERE
clause becomes part of the rewritten command.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services