Materialized Views

There are several ways that we have created to create and maintain materialized views within Postgres. You can have it updated nightly via IS's cron job, via a trigger when certain tables are changed, or execute a function when you want it updated (such as logging into a program).

To create a materialized view, this is what you need to do:

• Create a query that display what you want in the materialized view.
• Create a table with all the fields that are in the view. It is important that you make sure that you get all the data types correct.
• Add a line to tools.cs_materialized_views for your new materialized view. Set sort_order = 0.If you need a SERIAL field, see the mv_fields instructions below.
• Test your materialized view. SELECT * FROM tools.cs_refresh_mviews(mv_id);
• Make your view to automatically refresh. Do one of the following.
  • Change the sort_order to a number. The materialized view will now be automatically updated every night based on the sort_order.
  • Add a trigger to your source table(s) to update the materialized view when their data is changed.
  • Have your program call the refresh routine when it wants the materialized view updated. SELECT * FROM tools.cs_refresh_mviews(mv_id);

Functions

tools.cs_refresh_mviews()

SELECT * FROM tools.cs_refresh_mviews();

This just calls tools.cs_refresh_mviews(mv_id) with all the materialized views that have a sort order > 0 and in the order of the sort order.

tools.cs_refresh_mviews(mv_id)

SELECT * FROM tools.cs_refresh_mviews(mv_id);

This refreshes the specific view for which you have supplied the mv_id value. It first makes a copy of the existing view into the (mv_schema).t(mv_name). If the materialized view failed, it copies that data from the temp table back to the materialized view.

tools.cs_refresh_mviews_t(mv_id)

CREATE TRIGGER "schemaname_tablename_tr" AFTER INSERT OR UPDATE OR DELETE 
ON "schemaname"."tablename" FOR EACH STATEMENT 
EXECUTE PROCEDURE SELECT * FROM tools.cs_refresh_mviews_t(mv_id);

This is a trigger that can be attached attached to any table, so that when data is changed in the table the materialized view gets re-created. Internally this called the tools.cs_refresh_mviews(mv_id).

Table

tools.cs_materialized_views

• mv_id
  • This is a autonumber / serial id. It is unique to the row and is what allows you to refresh the view using tools.cs_refresh_mviews(mv_id).
• mv_schema
  • This is schema name that you want the materialized view stored in. You should use matviews by default.
• mv_name
  • This is table name that you want the materialized view stored in.
• mv_fields (Optional)
  • This is a comma separated list of the fields name to be written to and being returned by the query. Must be in the same order as the query and the same number of fields. This is so that you can have a autonumber / serial ID in the table that is generated when the materialized view is created.
• mv_query
  • This is the query that will return the results to be written into the materialized view.
• sort_key
  • When the daily cron job runs, it processes the materialized views in this order. Set this field to "0" blank if you do not want the cron job to update your view. This would be the case if your view was being updated via the trigger function.
• replication_time (Automatically Created)
  • This is the date and time that the materialized view was last updated.

db.main

mv_id	mv_schema	mv_name	mv_fields	mv_query	sort_key	replication_time
1	matviews	mv_crf_for_protocol_id		SELECT * FROM views.crf_for_protocol_id_mv();	1
2	matviews	mv_crf_for_tla		SELECT * FROM views.crf_for_tla_mv();	2
4	matviews	mv_employee_phone_list		SELECT * FROM views.employee_phone_list();	5
5	matviews	mv_protocol_milestone_dates		SELECT * FROM views.protocol_milestone_dates_mv;	3
6	matviews	mv_protocol_milestones_dfperms		SELECT * FROM views.protocol_milestones_dfperms_mv;	6
8	matviews	mv_mpmybt_employee_data		SELECT * FROM views.mpmybt_employee_data;	8
9	protimp	protocols		select * from protimp.protocols_mv();	4

sqltest.main

mv_id	mv_schema	mv_name	mv_fields	mv_query	sort_key	replication_time
1	matviews	mv_crf_for_protocol_id		SELECT * FROM views.crf_for_protocol_id_mv();	1
2	matviews	mv_crf_for_tla		SELECT * FROM views.crf_for_tla_mv();	2
4	matviews	mv_employee_phone_list		SELECT * FROM views.employee_phone_list();	5
5	matviews	mv_protocol_milestone_dates		SELECT * FROM views.protocol_milestone_dates_mv;	3
6	matviews	mv_protocol_milestones_dfperms		SELECT * FROM views.protocol_milestones_dfperms_mv;	6
8	matviews	mv_mpmybt_employee_data		SELECT * FROM views.mpmybt_employee_data;	8
9	protimp	protocols		select * from protimp.protocols_mv();	4

-- LloydAlbin - 25 Mar 2009