Chapter 5. Usage Examples

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

When exporting materialized view, ora2pgpro 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 PL/pgSQL code to create tree functions:

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 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 build (aka most of the time the primary key). This column is not automatically deduced so you need to replace its name.

As said above, ora2pgpro only supports snapshot materialized views so the table will be entirely refreshed by issuing first a truncate of the table and then by load again all data from the view:

refresh_full_materialized_view('mviewname');

To drop the materialized view, call the drop_materialized_view() function with the name of the materialized view as parameter.