Albe Laurenz wrote:
srdjan wrote:
I'll try to explain my problem with an example.
-- I've got 2 tables and one view
CREATE TABLE a (name varchar(20) primary key, num integer);
CREATE TABLE b (town varchar(15), name varchar(20) references a(name));
CREATE VIEW vvv AS SELECT * FROM a NATURAL JOIN b;
-- I've crated a rule in this way
CREATE RULE rrr AS ON INSERT TO vvv
WHERE NEW.name = 'tom'
DO INSTEAD
INSERT INTO a VALUES (NEW.name, NEW.num);
Trying a simple INSERT INTO vvv, I receive this message:
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
If I've understood well, the qualification (WHERE NEW.name = 'tom') is the condition under which the rule has to be executed.
Only if the condition is met, the rule is executed.
I noticed that if I remove the qualification, the rule works, but doing so
I am not able anymore to test the condition. (I could overcame this problem
with a trigger, but I'd prefer if someone could explain me how to do this with rules).
What is the desired response to
INSERT INTO vvv (name, town, num) VALUES ('Karl', 'Leipzig', 18);
Should this generate an error message, do nothing, or insert something?
Yours,
Laurenz Albe
In this case you shouldn't be able to do this insert.
Mine in only a example but in reality I've created the view for security reasons (the view uses function
current_user and allows users to see only the rows that satisfy particular requirements).
The insert is invoked on the view, but in fact it works only on the first table.
The qualification should test if the NEW value inserted for a specific attribute is or not in a specific pool/range of allowed values [in my sample the only case which is allowed in
when NEW.name = 'tom']
Regards
Srdjan Matic