bug (?) with RULEs with WHERE
I cannot use RULEs with WHERE clauses. What's wrong? Is this a bug? I also
had this problem with 7.1.1. The documentation says this should work.
foo=# SELECT version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
foo=# CREATE TABLE a(foo integer);
CREATE
foo=# CREATE TABLE b(foo integer);
CREATE
foo=# CREATE VIEW c AS SELECT foo FROM a;
CREATE
foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b;
CREATE
foo=# INSERT INTO c VALUES (5);
ERROR: Cannot insert into a view without an appropriate rule
foo=# INSERT INTO c VALUES (6);
ERROR: Cannot insert into a view without an appropriate rule
TIA, Zoltan
--
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
Import Notes
Reply to msg id not found: 200110270152.f9R1mQ366649@rs.postgresql.org
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
foo=# CREATE TABLE a(foo integer);
CREATE
foo=# CREATE TABLE b(foo integer);
CREATE
foo=# CREATE VIEW c AS SELECT foo FROM a;
CREATE
foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b;
CREATE
foo=# INSERT INTO c VALUES (5);
ERROR: Cannot insert into a view without an appropriate rule
You didn't provide a rule covering the new.foo<>5 case.
In practice, you *must* have an unconditional INSTEAD rule present for
any view operation you want to allow. It can be DO INSTEAD NOTHING,
and then you can do all your useful work in conditional rules, but the
unconditional rule must be there. Else the system thinks that perhaps
the insert into the view would really happen.
regards, tom lane
On Sat, 27 Oct 2001, Tom Lane wrote:
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
[...]
foo=# CREATE RULE d AS ON INSERT TO c WHERE new.foo=5 DO INSTEAD SELECT foo FROM b;
CREATE
foo=# INSERT INTO c VALUES (5);
ERROR: Cannot insert into a view without an appropriate ruleYou didn't provide a rule covering the new.foo<>5 case.
In practice, you *must* have an unconditional INSTEAD rule present for
any view operation you want to allow. It can be DO INSTEAD NOTHING,
and then you can do all your useful work in conditional rules, but the
unconditional rule must be there. Else the system thinks that perhaps
the insert into the view would really happen.
Thank you, I see. It works now. But in 7.1.1 on a rather complex view I
experienced that the RULE has been executed as many times as many rows the
view contains, although I added a WHERE to filter the rows: in fact it
should have been executed only once. In 7.1.3 this problem doesn't
occur. Has anything been changed since 7.1.1 in this code?
So I'm migrating to 7.1.3 now. But currently I'm still having problems
with user authentication (I get "Password authentication failed for user
'xxxx'." errors). I always used
INSERT INTO pg_shadow...
Is this changed? With
ALTER USER...
it works, of course. Do you suggest stopping use "INSERT INTO
pg_shadow..."?
TIA, Zoltan
Kov\'acs, Zolt\'an
kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz