oracle-databasepostgresqlora2pgaws-sct

I want to convert a user defined datatype from ORACLE in to a working POSTGRE user defined data?


TYPE group_opt_cov_rec IS RECORD (
   n_product_version_id          PRODUCT_COVERAGE_GROUP.PRODUCT_VERSION_ID%TYPE := Null 
   n_group_id                PRODUCT_COVERAGE_GROUP.GROUP_ID%TYPE := Null ,
   n_opt_group_cost            PRODUCT_COVERAGE_GROUP.GROUP_PRICE%TYPE := Null,
   n_group_maximum            PRODUCT_COVERAGE_GROUP.GROUP_MAXIMUM%TYPE := Null,
   nt_opt_grp_member_cov_id      prod_types.TYPE_NUMBER_ARRAY := prod_types.TYPE_NUMBER_ARRAY(), 
   nt_opt_grp_member_cov_qty      prod_types.TYPE_NUMBER_ARRAY := prod_types.TYPE_NUMBER_ARRAY(),
   st_opt_grp_member_required      prod_types.TYPE_STRING_ARRAY := prod_types.TYPE_STRING_ARRAY()
-- nt_opt_grp_member_cov_id         common_func.NUM_TABLE := common_func.EMPTY_NUM_TABLE,
-- nt_opt_grp_member_cov_qty      common_func.NUM_TABLE := common_func.EMPTY_NUM_TABLE,
-- st_opt_grp_member_required      common_func.STRING_TABLE := common_func.EMPTY_STRING_TABLE
);

The above code is a user-defined data type from Oracle, and I am unable to convert it into a user-defined data type on PostgreSQL. Please help!


Solution

  • Pretty much the same thing would be a composite type in PostgreSQL, and for the arrays use arrays.

    CREATE TYPE group_opt_cov_rec AS (
       n_product_version_id       bigint,
       n_group_id                 bigint,
       n_opt_group_cost           numeric,
       n_group_maximum            numeric,
       nt_opt_grp_member_cov_id   bigint[],
       nt_opt_grp_member_cov_qty  bigint[],
       st_opt_grp_member_required text[]
    );
    

    I had to guess the data types, replace them as appropriate. If you really need those default values, play with domains over base data types.