Обсуждение: implementing refcounts across tables.

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."