a dumb question regarding RULES

From: Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: a dumb question regarding RULES
Date: 2011-10-10 14:06:34
Message-ID: 1318255594.8887.31.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I've been using RULES for some time now, and despite the fact, that I
always had to make "experiments" to have the expected results, it worked
for me just fine.

Now I have this simple scenario:
---------------------------------------------------
mbr2=# CREATE TEMP TABLE test(a int, b date);
CREATE TABLE
mbr2=# INSERT INTO test (a) values (22);
INSERT 0 1
mbr2=# CREATE TEMP VIEW testing AS SELECT * from test;
CREATE VIEW
mbr2=# SELECT * from testing;
a | b
----+---
22 |
(1 row)

mbr2=# CREATE RULE chg_testing_1 AS ON UPDATE TO testing where new.b is
not null do instead select '+++';
CREATE RULE
mbr2=# CREATE RULE chg_testing_2 AS ON UPDATE TO testing do instead
select '===';
CREATE RULE
mbr2=# UPDATE testing set a=44;
?column?
----------
===
(1 row)

UPDATE 0
mbr2=# UPDATE testing set a=44, b=current_date;
?column?
----------
===
(1 row)

UPDATE 0
mbr2=#
------------------------------------------

And I'm completely lost how to interpret the outcome:

1. I know, that RULEs countrary to TRIGGERs "rewrite" query, resulting
in actual execution of a different query from originally scheduled

2. while triggers "take" rows of processed data "and do their job
there".

Now, the above example does not behave as I would expect.

I really don't understand why both of the above updates give the same
result here and it boils down to: how come a RULE can have a look at
"NEW.B", if it's supposed to be rewritten *before* any rows are fetched
for processing? When exactly does it "have a look" at the NEW.B? How do
I ensure, that the "DO INSTEAD NOTHING" rule *does*not* get executted
when a conditional rule does?

Can someone give a little explenation here ... or point me to "for
dummies" documentation? (yes, I've been though postgres docs, to no
avail).

I'll apreciate that, really.

-R

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2011-10-10 14:26:26 Re: [GENERAL] how to save a bytea value into a file?
Previous Message Jack Christensen 2011-10-10 14:05:06 Re: Failure controlling PG 9.1 service on Ubuntu 10.04 32-bit