Forum OpenACS Q&A: Response to Postgres feature questions

Posted by Andrew Piskorski on
Daryl, your query is definitely the way to go in cases where it does
the job, but it is not as general as the nasty hack I posted above.
You're doing the join with date arithmetic, by subtracting 1 day from
the date.  That works fine as long as the previous date is always
exactly 1 day in the past.  But if you have a price on a Monday, and
the previous price was on the Friday before, that's not going to do
the job.

Hm, maybe a true geek would come up with some cool mathematical
transform to map the dates you actually have to a series of integers
without any gaps, kind of like the way the Julian Day Numbers are
series of integers without gaps, and each integer maps 1-to-1 to each
day in the calendar.

Don, that's good to know about non-thread-safe languages being ok to
embed in Postgres - I didn't realize that.  I saw comments in the Docs
about how the libpq C library is thread safe as of Postgres 7.0, and
the guy who embedded R (Duncan Lang) seemed to highlight its
non-thread-safety as an issue, so I just assumed it was a problem.
(Now I'll have to put embedding language interpretors into Postgres on
my list of Cool Things to Learn...)