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