Think of this problem as handling unix style filesystem hardlinks.
Consider two tables:
create table link (
data_id int4,
link_date timestamp
);
create table data (
date_id int4 PRIMARY KEY,
data_txt text
);
We may have multiple "link" rows pointing at the same data.
What I would like to implement is a rule that when a row is deleted
from "link" then if no other rows in "link" reference a row in "data"
then the row in data is deleted as well.
Here's what I've tried:
create rule
cascade_clean_data
as on delete to link
do delete from data
where
data_id = OLD.data_id
AND '1' = (select count(*) from link where data_id = OLD.data_id)
;
I've also tried '0' for the second conditional, but niether seem to
work.
So, is there a way to accomplish this automatic refcounting either
with rules or some other trick?
Also, I think the count(*) is a bad idea because we only need to know
if a single entry besideds the one we are deleteing exists, not the
actual count.
Any suggestions?
thanks,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."