--- Willy-Bas Loos <[EMAIL PROTECTED]> wrote: > maybe you would find "materialized views" interesting. > http://www.google.com/search?q=materialized+view+postgresql > > > On 12/1/06, Matthew Peter <[EMAIL PROTECTED]> wrote: > > > > Is it possible to put an query result into memory? Like SELECT * from > > table WHERE > > [...] CACHE cache_name TIMEOUT '1 hour'::interval; So if "cache_name" > > exists with > > the same SQL statement, the result would be fetched from the cache, > > refreshing and > > updating the cache with fresh results when it expires? Reducing disk > > reads, query > > times, etc. > > > >
That is basically the idea but talk about a headache. Too many functions and triggers to handle a single view none the less. Rather, why not write an function to use SELECT INTO and put the new tables in a schema named "cache." Drop and recreate the schema cached tables of the views and wallah. Making this process cleanly abstracted into the background with 4 additional words would be a beautiful thing. eg, SELECT * from table WHERE [...] CACHE cache_name TIMEOUT interval; Since it's a cache, it doesn't need to be updated until the TIMEOUT expires and permissions can be inherited by the VIEW that creates it, etc. Or if that is that an SQL-spec no-no? Maybe... CREATE CACHED VIEW on view_name as view_name_cache TIMEOUT interval; Oh ya. Like CREATE UNIQUE INDEX. Now querying from view_name_cache will not effect querying from the original view view_name for fresh data! Internally implemented the cached views could be put in a schema like pg_cache, in RAM, etc. Doesn't really matter. Would just be nice to have something seamless, clean, upgrade agnostic, and easy! Thoughts? ____________________________________________________________________________________ Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
