Markus,
Thank you for your hint!
I spent the better part of last night working on this and finally was
able to get it to work the way I wanted. The short version: I am
continually amazed by the flexibility in Postgres, this isn't the sort of
thing I'd want to go back years from now and digest what I did but this
really has given me the best of both worlds: *very* quick query times
(4631ms down to 2 ms!) and when the data changes the trigger which does
the update is very quick, which beats my previous plan which involved
rerunning the query again and then caching the result for subsequent
queries....
Really this is great stuff! I simply cannot thank you (and all the other
folks on this list who have helped me) enough!
- Greg
>PostgreSQL doesn't provide pre-configured support for materialized views
>as such, but using some PL/pgSQL and triggers, one can easily implement
>any kind of materialized view as seen fit for the specific intended
>purpose (Snapshot, Eager, Lazy, Very Lazy).
>
>You may find an excellent tutorial on materialized views with PostgreSQL
>here: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
>
>> -----Ursprüngliche Nachricht-----
>> Von: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von
>> Net Virtual Mailing Lists
>> Gesendet: Samstag, 6. November 2004 16:49
>> An: Matteo Beccati
>> Betreff: Re: [GENERAL] Can this be indexed?
>>
>> I am not clear how to use a trigger for this, I will need to
>> look into that....
>>
>> It is my understanding that Postgres does not have
>> materialized views though (which I believe would solve this
>> problem nicely) - am I mistaken?...
>>
>>
>> - Greg
>>