Exporting Views as PostgreSQL Tables

Exporting views as PostgreSQL tables

You can export any Oracle view as a PostgreSQL table simply by setting the TYPE configuration option to TABLE to get the corresponding create table statement. Or use type COPY or INSERT to export the corresponding data. To allow this, you have to specify your views in the VIEW_AS_TABLE configuration option.

Then if Ora2Pg finds the view, it will extract its schema (if TYPE=TABLE) into a PG create table form, then it will extract the data (if TYPE=COPY or INSERT) following the view schema.

For example, with the following view:

CREATE OR REPLACE VIEW product_prices (category_id, product_count, low_price, high_price) AS
SELECT  category_id, COUNT(*) as product_count,
    MIN(list_price) as low_price,
    MAX(list_price) as high_price
 FROM   product_information
GROUP BY category_id;

Setting VIEW_AS_TABLE to product_prices and using export type TABLE, will force Ora2Pg to detect columns' returned types and to generate a create table statement:

CREATE TABLE product_prices (
        category_id bigint,
        product_count integer,
        low_price numeric,
        high_price numeric
);

Data will be loaded following the COPY or INSERT export type and the view declaration.

You can use the ALLOW and EXCLUDE directives in addition to filter other objects to export.