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