James B. Byrne wrote:
> The situation is this. A dependent table relation****p is episodic. In
o=
ther
> words, a product might be available for a period of time, then not
availa=
ble,
> then available again. Or, a firm might be a client for a period, then
no=
t,
> then again. Or a person might be an employee, then not, then again.
Furt=
her,
> past intervals of activity must be preserved.
>=20
> 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:
>=20
> SELECT * FROM table WHERE ("table"."effective_from <=3D "current_date"
AND
> ( "table"."superseded" IS NULL OR
> "table"."superseded" >=3D "current_date" ) )
>=20
> The difficulty arises from the implementation of the Rails generated SQL
> SELECTs which freezes any datetime employed therein to the instant that
t=
he
> model is first evaluated. There is a way around this but it is fairly
ted=
ious
> and has to be repeated in numerous places because of the evaluate once
> difficulty referred to above.
>=20
> It seems to me that there should be a fairly easy way to construct a
func=
tion
> on such a table to derive a BOOLEAN value for a virtual column named
"act=
ive"
> based upon the SELECT criteria given above. However, I am unsure if this
=
is in
> fact possible and, if so, how to do this.
>=20
> Can someone show me how this could be accomplished? And, can someone
corr=
ect
> 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=3D> CREATE TABLE test (id integer PRIMARY KEY,
test-> val text,
test-> effective_from date NOT NULL,
test-> superseded date);
test=3D> CREATE VIEW test_view (id, val, active) AS
test-> SELECT id,
test-> val,
test-> (effective_from <=3D current_date AND (superseded IS NULL
O=
R superseded >=3D current_date))
test-> FROM test;
test=3D> INSERT INTO test (id, val, effective_from, superseded)
test-> VALUES (1, 'one', '2007-01-01', NULL);
test=3D> INSERT INTO test (id, val, effective_from, superseded)
test-> VALUES (2, 'two', '2007-01-01', '2007-12-31');
test=3D> INSERT INTO test (id, val, effective_from, superseded)
test-> VALUES (3, 'three', '2009-01-01', NULL);
test=3D> SELECT * FROM test_view;
id | val | active=20
----+-------+--------
1 | one | t
2 | two | f
3 | three | f
(3 rows)
Yours,
Laurenz Albe
--=20
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
