Re: Rules on select

As a new user, I recently had the same problem. I then realized what the
problem is.

You want to select from sharp_p1 instead of selecting from sharp. That
means you never actually select from sharp. Since nothing was selected,
postgres can't do anything useful with the WHERE clause. To put it another
way, how can the db know which rows match the WHERE clause when it never
actually looks at the table?

I hope this helps explain WHY it doesn't work. Hopefully someone else
can suggest an alternative.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Mariusz Czu³ada
Sent: Monday, January 17, 2005 7:29 AM
To: Postgres General
Subject: [GENERAL] Rules on select

Hi,

I tried to create rule on a view. It should contain a WHERE clause.
Unfortunately it does not work:

a_4m=# CREATE RULE sharp_p1_rule AS ON SELECT TO sharp
a_4m-# WHERE part_key = 1
a_4m-# DO
a_4m-# INSTEAD SELECT * FROM sharp_p1;
ERROR: ON SELECT rule may not use OLD

a_4m=# CREATE RULE sharp_p1_rule AS ON SELECT TO sharp
a_4m-# WHERE NEW.part_key = 1
a_4m-# DO
a_4m-# INSTEAD SELECT * FROM sharp_p1;
ERROR: relation "*NEW*" does not exist

a_4m=# CREATE RULE sharp_p1_rule AS ON SELECT TO sharp
a_4m-# WHERE OLD.part_key = 1
a_4m-# DO
a_4m-# INSTEAD SELECT * FROM sharp_p1;
ERROR: ON SELECT rule may not use OLD

Is it my mistake, or some feature I did not find in the docs?

regards,

Mariusz

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html