Re: Rules to provide a virtual column

James B. Byrne wrote:
> The situation is this. A dependent table relationship is episodic. In other
> words, a product might be available for a period of time, then not available,
> then available again. Or, a firm might be a client for a period, then not,
> then again. Or a person might be an employee, then not, then again. Further,
> past intervals of activity must be preserved.
>
> The way that we handle this is through two columns in the dependent table;
> effective_from and superseded_after. Thus an active row is retrieved via the
> following code:
>
> SELECT * FROM table WHERE ("table"."effective_from <= "current_date" AND
> ( "table"."superseded" IS NULL OR
> "table"."superseded" >= "current_date" ) )
>
> The difficulty arises from the implementation of the Rails generated SQL
> SELECTs which freezes any datetime employed therein to the instant that the
> model is first evaluated. There is a way around this but it is fairly tedious
> and has to be repeated in numerous places because of the evaluate once
> difficulty referred to above.
>
> It seems to me that there should be a fairly easy way to construct a function
> on such a table to derive a BOOLEAN value for a virtual column named "active"
> based upon the SELECT criteria given above. However, I am unsure if this is in
> fact possible and, if so, how to do this.
>
> Can someone show me how this could be accomplished? And, can someone correct
> my use of current_date in the example given above if require?

I am not sure if that can solve your problem, but you could use views:

test=> CREATE TABLE test (id integer PRIMARY KEY,
test-> val text,
test-> effective_from date NOT NULL,
test-> superseded date);

test=> CREATE VIEW test_view (id, val, active) AS
test-> SELECT id,
test-> val,
test-> (effective_from <= current_date AND (superseded IS NULL OR superseded >= current_date))
test-> FROM test;

test=> INSERT INTO test (id, val, effective_from, superseded)
test-> VALUES (1, 'one', '2007-01-01', NULL);
test=> INSERT INTO test (id, val, effective_from, superseded)
test-> VALUES (2, 'two', '2007-01-01', '2007-12-31');
test=> INSERT INTO test (id, val, effective_from, superseded)
test-> VALUES (3, 'three', '2009-01-01', NULL);

test=> SELECT * FROM test_view;
id | val | active
----+-------+--------
1 | one | t
2 | two | f
3 | three | f
(3 rows)

Yours,
Laurenz Albe