Hello, all
From sql.ru forum:
not important, is it one rule like:
create rule v_del as on delete to v do instead (
delete from o1 where id = old.o1_id;
delete from o2 where id = old.o2_id;
);
or split into two rule like:
create rule v_del1 as on delete to v do instead (
delete from o1 where id = old.o1_id;
);
create rule v_del2 as on delete to v do instead (
delete from o2 where id = old.o2_id;
);
delete from second table (o2) do not delete anything.
test case:
begin;
select version();
create table o1 (id int, val text);
create table o2 (id int, val text);
create view v as select o1.id as o1_id, o1.val as o1_val, o2.id as o2_id,
o2.val as o2_val from o1, o2 where o1.id=o2.id;
create rule v_del as on delete to v do instead (
delete from o1 where id = old.o1_id;
delete from o2 where id = old.o2_id;
);
-- create rule v_del1 as on delete to v do instead (
-- delete from o1 where id = old.o1_id;
-- );
-- create rule v_del2 as on delete to v do instead (
-- delete from o2 where id = old.o2_id;
-- );
insert into o1 values (1, 'o1 1'), (2, 'o1 2'), (3, 'o1 3');
insert into o2 values (1, 'o2 1'), (2, 'o2 2'), (3, 'o2 3');
select * from v;
delete from v where o1_id = 1;
explain analyze delete from v where o2_id = 2;
select * from v;
select * from o1;
select * from o2;
rollback;
=== output ===
version
----------------------------------------------------------------------------------------
PostgreSQL 8.3.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Debian
4.2.3-1)
--- select * from v;
o1_id | o1_val | o2_id | o2_val
-------+--------+-------+--------
1 | o1 1 | 1 | o2 1
2 | o1 2 | 2 | o2 2
3 | o1 3 | 3 | o2 3
(3 rows)
--- delete from v where o1_id = 1;
seb=> DELETE 0
--- explain analyze delete from v where o2_id = 2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Nested Loop (cost=50.76..81.18 rows=216 width=6) (actual time=0.040..0.050
rows=1 loops=1)
-> Nested Loop (cost=25.38..51.48 rows=36 width=14) (actual
time=0.030..0.034 rows=1 loops=1)
-> Seq Scan on o1 (cost=0.00..25.38 rows=6 width=10) (actual
time=0.014..0.015 rows=1 loops=1)
Filter: (id = 2)
-> Materialize (cost=25.38..25.44 rows=6 width=4) (actual
time=0.012..0.014 rows=1 loops=1)
-> Seq Scan on o2 (cost=0.00..25.38 rows=6 width=4) (actual
time=0.007..0.008 rows=1 loops=1)
Filter: (id = 2)
-> Materialize (cost=25.38..25.44 rows=6 width=4) (actual
time=0.007..0.010 rows=1 loops=1)
-> Seq Scan on o1 (cost=0.00..25.38 rows=6 width=4) (actual
time=0.005..0.008 rows=1 loops=1)
Filter: (id = 2)
Total runtime: 0.135 ms
Nested Loop (cost=50.76..81.18 rows=216 width=6) (actual time=0.034..0.034
rows=0 loops=1)
-> Nested Loop (cost=25.38..51.48 rows=36 width=10) (actual
time=0.019..0.023 rows=1 loops=1)
-> Seq Scan on o2 (cost=0.00..25.38 rows=6 width=10) (actual
time=0.008..0.009 rows=1 loops=1)
Filter: (id = 2)
-> Materialize (cost=25.38..25.44 rows=6 width=4) (actual
time=0.009..0.011 rows=1 loops=1)
-> Seq Scan on o2 (cost=0.00..25.38 rows=6 width=4) (actual
time=0.006..0.007 rows=1 loops=1)
Filter: (id = 2)
-> Materialize (cost=25.38..25.44 rows=6 width=4) (actual
time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on o1 (cost=0.00..25.38 rows=6 width=4) (actual
time=0.007..0.007 rows=0 loops=1)
Filter: (id = 2)
Total runtime: 0.083 ms
(23 rows)
--- select * from v;
o1_id | o1_val | o2_id | o2_val
-------+--------+-------+--------
3 | o1 3 | 3 | o2 3
(1 запись)
--- select * from o1; (all correctly deleted)
id | val
----+------
3 | o1 3
(1 запись)
--- select * from o2; (no one deleted)
id | val
----+------
1 | o2 1
2 | o2 2
3 | o2 3
(3 rows)
seb=> ROLLBACK
---
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
