I love MATERIALIZED VIEWs. But we needed a method for recording deltas from REFRESHes, and that's not supported. So I coded up my own version of materialized views, in PlPgSQL, that does provide a history feature.
Besides a history feature, this includes the ability to record changes made to a materialized view's materialization table, which means I can have triggers that update the materialized view. We use this for updating a view whose query is a bit slow. Some triggers are also slow (well, they're fast, but used in transactions that might potentially run fire these triggers many times), in which case I mark a "view" as needing a refresh. Other triggers are fast and directly update the "view". https://github.com/twosigma/postgresql-contrib https://github.com/twosigma/postgresql-contrib/blob/master/pseudo_mat_views.sql https://raw.githubusercontent.com/twosigma/postgresql-contrib/master/pseudo_mat_views.sql I'd be willing to do some of the work of integrating this more closely with PG, but I may need some pointers (but hopefully not much hand- holding). Ideally we could have CREATE MATERIALIZED VIEW syntax like this: CREATE MATERIALIZED VIEW schema_name.view_name [ ( <column-name> [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS <query> WITH [ [ UNLOGGED ] HISTORY TABLE [ schema_name.view_name_history ], ] [ PRIMARY KEY ( <column-name> [, ...] ), ] [ [ NO ] DATA ]; Of particular interest may be the fact that the FULL OUTER JOIN that PG does for REFRESH CONCURRENTLY, and which I copied here, doesn't deal well with views that have NULLs in any columns used in the join. It would be nice to have an equijoin that uses IS NOT DISTINCT FROM rather than just =, and then refreshing could use such a join in order to deal properly with NULLs. Any help with integration, or comments, even flames, are welcomed, but keep in mind that this is my first foray into making a contribution to PG, so please do be kind. Pointers to C and SQL style guides and standards for in-tree code would be particularly helpful. Thanks! Nico -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers