Updatable view does not work [oops, quite long!] - Mailing list pgsql-novice

Hi,

I write a little piece of software for me and my girl friend. I have
just a simple table encapsulated by a view such that we cannot see each
other's data.

This works fine so far. We insert into to the view which redirects the
insert by a rule to the base table. Perfect.

However, I have written two more rules, one for updates and one for
deletes. But those only work if all the fields have values. They do
nothing with effect on the base table when applied on rows with NULL in
the fields.

Has somebody got any ideas?

Cheers,

Thiemo

Some tests
----------
bash-2.05b$ psql -d passwort -h nyffeltrach.thiam.ch -U passwort_test
Passwort:
Willkommen bei psql 7.3.2, dem interaktiven PostgreSQL-Terminal.

Geben Sie ein:  \copyright für Urheberrechtsinformationen
                 \h für Hilfe über SQL-Anweisungen
                 \? für Hilfe über interne Anweisungen
                 \g oder Semikolon, um eine Abfrage auszuführen
                 \q um zu beenden

SSL-Verbindung (Verschlüsslungsmethode: EDH-RSA-DES-CBC3-SHA, Bits: 168)

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
(2 Zeilen)

passwort=> insert into passwort (programm, link, schluessel,
seriennummer, user_id, passwort, bemerkungen, gueltig_bis) values ('1',
'2', '3', '4', '5', '6', '7', to_date('2003-06-02', 'YYYY-MM--DD'));
INSERT 33984 1

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
  1        | 2    | 3          | 4            | 5       | 6        | 7
          | 2003-06-02
(3 Zeilen)

passwort=> delete from passwort where programm = 'Test';
DELETE 0

passwort=> delete from passwort where programm = '1' and link = '2' and
schluessel = '3' and seriennummer = '4' and user_id = '5' and passwort =
'6' and bemerkungen = '7' and gueltig_bis = to_date('2003-06-02',
'YYYY-MM--DD');
DELETE 1

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
(2 Zeilen)

passwort=> update passwort set link = '?' where programm = 'Test';
UPDATE 0

passwort=> update passwort set link = '?' where programm = 'Test' and
link is null and schluessel is null and seriennummer is null and user_id
= 'test' and passwort is null and bemerkungen is null and gueltig_bis is
null;
UPDATE 0

passwort=> delete from passwort where programm = 'Test' and link is null
and schluessel is null and seriennummer is null and user_id = 'test' and
passwort is null and bemerkungen = 'sets' and gueltig_bis is null;
DELETE 0

passwort=> select * from passwort where programm = 'Test' and link is
null and schluessel is null and seriennummer is null and user_id =
'test' and passwort is null and bemerkungen = 'sets' and gueltig_bis is
null;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
(1 Zeile)

passwort=> insert into passwort (programm, link, schluessel,
seriennummer, user_id, passwort, bemerkungen, gueltig_bis) values ('1',
'2', '3', '4', '5', '6', '7', to_date('2003-06-02', 'YYYY-MM--DD'));
INSERT 33985 1

passwort=> update passwort set programm = '10' where programm = '1' and
link = '2' and schluessel = '3' and seriennummer = '4' and user_id = '5'
and passwort = '6' and bemerkungen = '7' and gueltig_bis =
to_date('2003-06-02', 'YYYY-MM--DD');
UPDATE 1

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
  10       | 2    | 3          | 4            | 5       | 6        | 7
          | 2003-06-02
(3 Zeilen)



Table
-----
CREATE TABLE pwd (
     pwd_id           INT2           DEFAULT nextval('s_pwd')
   , programm         VARCHAR(30)
   , link             VARCHAR(70)
   , schluessel       VARCHAR(30)
   , seriennummer     VARCHAR(30)
   , user_id          VARCHAR(70)
   , passwort         VARCHAR(30)
   , bemerkungen      VARCHAR(255)
   , guetlig_bis      DATE
   , benutzer         VARCHAR(30)    NOT NULL
   , CONSTRAINT pk_pwd PRIMARY KEY (pwd_id)
   , CONSTRAINT uk_pwd UNIQUE (programm, link, seriennummer, user_id)
);

View
----
CREATE VIEW AS
    SELECT
         pwd.programm
       , pwd.link
       , pwd.schluessel
       , pwd.seriennummer
       , pwd.user_id
       , pwd.passwort
       , pwd.bemerkungen
       , pwd.gueltig_bis
    FROM pwd
    WHERE (pwd.benutzer = "varchar"("current_user"()))
;

delete Rule
-----------
CREATE RULE ru_v_passwort_del
    AS ON DELETE TO passwort
       DO INSTEAD
          DELETE FROM pwd
       WHERE (
              (
           (
            (
             (
              (
               (
                (
                 (pwd.programm = old.programm)
                 AND
                 (pwd.link = old.link)
                )
                AND
                (pwd.schluessel = old.schluessel)
               )
               AND
               (pwd.seriennummer = old.seriennummer)
              )
              AND
              (pwd.user_id = old.user_id)
             )
             AND
             (pwd.passwort = old.passwort)
            )
            AND
            (pwd.bemerkungen = old.bemerkungen)
           )
           AND
           (pwd.gueltig_bis = old.gueltig_bis)
          )
          AND
          (pwd.benutzer = ("current_user"())::character varying)
         )
;

update Rule
-----------
CREATE RULE ru_v_passwort_upd
    AS ON UPDATE TO passwort
       DO INSTEAD
          UPDATE pwd
        SET
          programm = new.programm
        , link = new.link
        , schluessel = new.schluessel
        , seriennummer = new.seriennummer
        , user_id = new.user_id
        , passwort = new.passwort
        , bemerkungen = new.bemerkungen
        , gueltig_bis = new.gueltig_bis
     WHERE (
        (
         (
          (
           (
            (
             (
              (
               (pwd.programm = old.programm)
               AND
               (pwd.link = old.link)
              )
              AND
              (pwd.schluessel = old.schluessel)
             )
             AND
            (pwd.seriennummer = old.seriennummer)
           )
           AND
           (pwd.user_id = old.user_id)
          )
          AND
          (pwd.passwort = old.passwort)
         )
         AND
         (pwd.bemerkungen = old.bemerkungen)
        )
        AND
        (pwd.gueltig_bis = old.gueltig_bis)
           )
           AND
           (pwd.benutzer = ("current_user"())::character varying)
          )
;

Grants
------
insert on pwd to ...;
update, insert and delete on passwort to ...;

--
root ist die Wurzel allen Übels