\timing on
START TRANSACTION;
SET local client_min_messages = NOTICE;
SET local log_min_messages = NOTICE;

--sys.all_tab_columns 91.614
--should keep same as rollback_catalog_maindb_91_614.sql rollback_catalog_otherdb_91_614.sql
CREATE OR REPLACE VIEW sys.all_tab_columns AS
SELECT
    CAST(owner.rolname AS VARCHAR2(64)) AS owner,
    CAST(tbl.relname AS VARCHAR2(64)) AS table_name,
    CAST(col.attname AS VARCHAR2(64)) AS column_name,
    CAST(ty.typname AS VARCHAR2(128)) AS data_type,
    CAST(col.attnum AS integer) AS column_id,
    CAST(
        (CASE (col.attlen < 0 AND ty.typname = 'varchar' AND spa.nspname = 'pg_catalog')
            WHEN TRUE THEN (col.atttypmod - 4) ::integer
            ELSE col.attlen
        END) AS integer) AS data_length,
    des.description AS comments,
    CAST(sta.stawidth AS NUMBER) AS avg_col_len,
    CASE
        WHEN (col.attnotnull) THEN 'n'::bpchar
        ELSE 'y'::bpchar
    END AS nullable,
    CASE
        WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod != -1) THEN (((col.atttypmod - 4) >> 16) & 65535)::integer
        ELSE null
    END AS data_precision,
    CASE
        WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod != -1) THEN ((col.atttypmod - 4) & 65535)::integer
        WHEN (ty.typname = 'numeric' AND spa.nspname = 'pg_catalog' AND col.atttypmod = -1) THEN null
        ELSE 0::integer
    END AS data_scale,
    CASE
        WHEN (ty.typname = 'varchar' OR ty.typname = 'nvarchar2'  OR ty.typname = 'bpchar' OR ty.typname = 'char' AND spa.nspname = 'pg_catalog') THEN (col.atttypmod - 4)::number
        ELSE 0 ::number
    END AS char_length,
    CAST(sch.nspname AS VARCHAR2(64)) AS schema,
    CAST((CASE tbl.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' END) AS text) AS kind
FROM pg_catalog.pg_class tbl
INNER JOIN pg_catalog.pg_attribute col ON (col.attrelid = tbl.oid AND col.attnum > 0 AND col.attisdropped = false)
LEFT JOIN pg_catalog.pg_namespace sch ON (sch.oid = tbl.relnamespace)
LEFT JOIN pg_catalog.pg_statistic sta ON (sta.starelid = col.attrelid AND sta.starelkind = 'c' AND sta.staattnum = col.attnum AND sta.stainherit = false)
LEFT JOIN pg_catalog.pg_type ty ON (ty.oid = col.atttypid)
LEFT JOIN pg_catalog.pg_description des ON (des.objoid = col.attrelid AND des.classoid = 'pg_catalog.pg_class'::regclass AND des.objsubid = col.attnum)
LEFT JOIN pg_catalog.pg_authid owner ON (tbl.relowner = owner.oid)
LEFT JOIN pg_catalog.pg_namespace spa ON (spa.oid = ty.typnamespace)
WHERE tbl.relkind IN ('r', 'v') AND (pg_catalog.pg_has_role(tbl.relowner, 'usage')
OR pg_catalog.has_table_privilege(tbl.oid, 'select, insert, update, delete, truncate, references, trigger, analyze')
OR pg_catalog.has_any_column_privilege(tbl.oid, 'select, insert, update, references'));

--sys.all_ind_columns 91.614
--should keep same as rollback_catalog_maindb_91_614.sql rollback_catalog_otherdb_91_614.sql
CREATE OR REPLACE VIEW SYS.ALL_IND_COLUMNS AS
    SELECT
        CAST(a1.rolname AS VARCHAR2(64)) AS INDEX_OWNER,
        CAST(c1.relname AS VARCHAR2(64)) AS INDEX_NAME,
        CAST(a2.rolname AS VARCHAR2(64)) AS TABLE_OWNER,
        CAST(c2.relname AS VARCHAR2(64)) AS TABLE_NAME,
        b.attname AS COLUMN_NAME,
        b.attnum AS COLUMN_POSITION
    FROM pg_class c1
        INNER JOIN pg_authid a1 ON (a1.oid = c1.relowner)
        INNER JOIN pg_attribute b ON (c1.oid = b.attrelid)
        INNER JOIN pg_index i ON (i.indexrelid = c1.oid)
        INNER JOIN pg_class c2 ON (c2.oid = i.indrelid)
        INNER JOIN pg_authid a2 ON (a2.oid = c2.relowner)
    WHERE c1.relkind = 'i'
        AND (pg_catalog.pg_has_role(c1.relowner, 'usage')
            OR pg_catalog.has_table_privilege(c1.oid, 'select, insert, update, delete, truncate, references, trigger, analyze')
            OR pg_catalog.has_any_column_privilege(c1.oid, 'select, insert, update, references'));

--sys.all_ind_expressions 91.614
--should keep same as rollback_catalog_maindb_91_614.sql rollback_catalog_otherdb_91_614.sql
CREATE OR REPLACE VIEW SYS.ALL_IND_EXPRESSIONS AS
    SELECT
        CAST(a1.rolname AS VARCHAR2(64)) AS INDEX_OWNER,
        CAST(c1.relname AS VARCHAR2(64)) AS INDEX_NAME,
        CAST(a2.rolname AS VARCHAR2(64)) AS TABLE_OWNER,
        CAST(c2.relname AS VARCHAR2(64)) AS TABLE_NAME,
        pg_catalog.pg_get_indexdef(i.indexrelid, b.attnum::int, true) AS COLUMN_EXPRESSION,
        b.attnum AS COLUMN_POSITION
    FROM pg_class c1
        INNER JOIN pg_authid a1 ON (a1.oid = c1.relowner)
        INNER JOIN pg_index i ON (i.indexrelid = c1.oid)
        INNER JOIN pg_class c2 ON (c2.oid = i.indrelid)
        INNER JOIN pg_authid a2 ON (a2.oid = c2.relowner)
        INNER JOIN pg_attribute b ON (c1.oid = b.attrelid)
    WHERE c1.relkind = 'i'
        AND i.indkey[b.attnum - 1] = '0'
        AND (pg_catalog.pg_has_role(c1.relowner, 'usage')
                OR pg_catalog.has_table_privilege(c1.oid, 'select, insert, update, delete, truncate, references, trigger, analyze')
                OR pg_catalog.has_any_column_privilege(c1.oid, 'select, insert, update, references'));

COMMIT;
