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


Sat, 27 Oct 2001 13:10:50 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
bug (?) with RULEs with WHERE
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

Sat, 27 Oct 2001 14:04:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug (?) with RULEs with WHERE 
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 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.

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


Tue, 30 Oct 2001 13:57:57 +0100 (CET)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: bug (?) with RULEs with WHERE 