On 1/17/07,
Steve <
cheetah@tanabi.org> wrote:
However, I will look into this and see if I can figure out this
average value. This may be a valid idea, and I'll look some more at it.
It must be, Oracle sells it pretty heavily as a data warehousing feature ;). Oracle calls it a materialized view, and the basic premise is you have a "change" log (called a materialized log by Oracle) and you have a job that runs through the change log and applies the changes to the materialized view.
If you are using aggregates, be careful and make sure you use simple forms of those aggregates. For example, if you are using an "average" function then you should have two columns sum and count instead. Some aggregates are too complex and cannot be represented by this solution and you will find that you can't update the summary tables, so definitely try to stay away from complex aggregates if you do not need them.
Here is a link to a PL/pgSQL effort that tries to simulate materialized views:
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html I don't know how complete it is, and it looks like there was a project started but has been abandoned for the last 3 years.
--
Chad
http://www.postgresqlforums.com/