Search Postgresql Archives

Re: RULES and QUALIFICATION for INSERT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux