Materialized Views

Materialized View

Materialized views are exported as snapshot "Snapshot Materialized Views" as PostgreSQL only supports full refresh.

If you want to import materialized views in PostgreSQL prior to 9.3, you have to set configuration directive PG_SUPPORTS_MVIEW to 0. In this case Ora2Pg will export all materialized views as explained in this document:

http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views.

When exporting materialized views, Ora2Pg will first add the SQL code to create the "materialized_views" table:

CREATE TABLE materialized_views (
        mview_name text NOT NULL PRIMARY KEY,
        view_name text NOT NULL,
        iname text,
        last_refresh TIMESTAMP WITH TIME ZONE
);

All materialized views will have an entry in this table. It then adds the plpgsql code to create three functions:

create_materialized_view(text, text, text) used to create a materialized view
drop_materialized_view(text) used to delete a materialized view
refresh_full_materialized_view(text) used to refresh a view

Then it adds the SQL code to create the view and the materialized view:

CREATE VIEW mviewname_mview AS
SELECT ... FROM ...;

SELECT create_materialized_view('mviewname','mviewname_mview', change with the name of the column to be used for the index);

The first argument is the name of the materialized view, the second is the name of the view on which the materialized view is based, and the third is the column name on which the index should be built (typically the primary key). This column is not automatically deduced so you need to replace its name.

As mentioned above, Ora2Pg only supports snapshot materialized views so the table will be entirely refreshed by first truncating the table and then loading all data again from the view:

refresh_full_materialized_view('mviewname');

To drop the materialized view, you just have to call the drop_materialized_view() function with the name of the materialized view as a parameter.