RULES and QUALIFICATION for INSERT
| От | srdjan |
|---|---|
| Тема | RULES and QUALIFICATION for INSERT |
| Дата | |
| Msg-id | 47E9471A.7080509@anche.no обсуждение исходный текст |
| Ответы |
Re: RULES and QUALIFICATION for INSERT
|
| Список | pgsql-general |
Hi to everyone.
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));
insert into a values ('tom',5);
insert into a values ('paul',99);
insert into a values ('jack',1234);
insert into b values ('london','tom');
insert into b values ('rome','paul');
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).
Best regards
Srdjan Matic
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));
insert into a values ('tom',5);
insert into a values ('paul',99);
insert into a values ('jack',1234);
insert into b values ('london','tom');
insert into b values ('rome','paul');
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).
Best regards
Srdjan Matic
В списке pgsql-general по дате отправления: