This might be a little off topic and it may show a degree of naivety on my
part but I have a small problem with coding a Ruby on Rails application
and it
seems to me that the best answer may reside in the database itself.
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?
Regards,
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@[EMAIL PROTECTED]
& Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
