Hello.
I have time-varying relation variables similar to the ones described in
this Rick Snodgrass article:
http://www.informix.com.ua/articles/tempref/tempref.htm
An example:
CREATE TABLE departments (
department bigint primary key not null,
name text not null,
start_date timestamp not null default now(),
end_date timestamp
);
CREATE TABLE projects (
project bigint primary key not null,
department bigint not null,
name text not null,
start_date timestamp not null default now(),
end_date timestamp
);
I also have views which query only the active rows:
CREATE VIEW active_departments AS
SELECT *
FROM departments
WHERE end_date IS NULL;
The behavior, which I currently achieve laboriously through hand-written
triggers and partial indexes, is:
1) Insertion of an active project requires the existence of an active
department
2) Deactivation of an active department will cascade with a deactivation
of the associated active projects
where "active" means any tuple whose end_date is NULL
I'm thinking of modifying the backend to achieve these results
declaratively, rather than the manner I'm using now. For a small number
of relation variables, custom triggers aren't that bad. However, in the
hundreds it becomes a bit of a bear. In addition, I'd like the
deactivation of a tuple value in a temporal relation to optionally
result in its deletion, if the deactivation failed to cause the
cascading deactivation of other tuples in referencing non-temporal
relations.
If I did write up something to achieve this by modifying the backend,
would it have any chance of being accepted? Or, given the above design
and requirements, is there a way I could achieve the appropriate effects
declaratively that I've missed?
Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
