Re: RULE with conditional behaviour?
От | Tom Lane |
---|---|
Тема | Re: RULE with conditional behaviour? |
Дата | |
Msg-id | 8489.1015697478@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: RULE with conditional behaviour? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
I wrote: > If the above approach seems too complex, another possibility is to write > the rule attached to the view as something simple that invokes an action > you know will fire a trigger. For example, > ON UPDATE TO view DO INSTEAD INSERT INTO base-table VALUES(needed-values) This approach may not work well if your view is a join and so you might need to update multiple base tables. There is a sneaky way to get around that, which is to use a dummy table that has no other purpose than to be a place where you can fire a trigger. For example, ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT NEW.* Here, dummy-table is a real table (not a view), but it will never have any rows in it, because you'll give it an on-insert trigger that always suppresses the insert. Now, that trigger has access to a full row of the "updated view" and so it can go off and do whatever is needed to the underlying base tables. If you did it just like this then you'd actually need three dummy tables, one each for insert, update, and delete actions on the view. If that seems like overkill, consider ON INSERT TO view DO INSTEAD INSERT INTO dummy-table SELECT 1, NEW.* ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT 2, NEW.* ON DELETE TO view DO INSTEAD INSERT INTO dummy-table SELECT 3, OLD.* Now the insert trigger can look at the first column to decide what to do. (Note you can't do anything useful with update or delete triggers on the dummy table; it'll never have any rows so they'd never fire.) In general, the columns of the dummy table are exactly the parameter list you need to pass to your trigger, and so you can make 'em whatever you like. For example, to correctly update a view you might need both the old and new states of the (virtual) view row. No problem: ON UPDATE TO view DO INSTEAD INSERT INTO dummy-table SELECT OLD.*, NEW.* where dummy-table is declared with the necessary column set. I have not actually had occasion to use this approach myself, but here is a simple example showing that it would work: regression=# select * from b1; f1 | f2 -------------+------------- 0 | 0 123456 | 61728 -123456 | -61728 2147483647 | 1073741823 -2147483647 | -1073741823 (5 rows) regression=# select * from b2; f1 | f2 -------------+------------- 0 | 2 123456 | 123458 -123456 | -123454 2147483647 | -2147483647 -2147483647 | -2147483645 (5 rows) regression=# create view vvv as regression-# select b1.f1, b1.f2, b2.f2 as f22 from b1 join b2 using (f1); CREATE regression=# select * from vvv; f1 | f2 | f22 -------------+-------------+------------- -2147483647 | -1073741823 | -2147483645 -123456 | -61728 | -123454 0 | 0 | 2 123456 | 61728 | 123458 2147483647 | 1073741823 | -2147483647 (5 rows) -- Note that this update makes no sense in terms of either base table alone: regression=# update vvv set f22 = 43 where f2 = 61728 ; ERROR: Cannot update a view without an appropriate rule regression=# create table vvv_dummy (f1 int, f2 int, f22 int, regression(# new_f1 int, new_f2 int, new_f22 int); CREATE regression=# create rule vvv_update as on update to vvv do instead regression-# insert into vvv_dummy select old.*, new.*; CREATE regression=# update vvv set f22 = 43 where f2 = 61728 ; UPDATE 0 regression=# select * from vvv_dummy; f1 | f2 | f22 | new_f1 | new_f2 | new_f22 --------+-------+--------+--------+--------+--------- 123456 | 61728 | 123458 | 123456 | 61728 | 43 (1 row) I didn't bother to make an ON INSERT trigger for vvv_dummy, but if I had one, it would have received the data shown here as inserted into vvv_dummy. In short: by using a dummy table you can get the effect of a trigger applied to a view. regards, tom lane
В списке pgsql-general по дате отправления: