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...)