/*
 * NOTE: All pg_temp objects must be dropped at the end of the script!
 * Otherwise the eventual DROP CASCADE of pg_temp when the session ends will
 * also drop the extension! Instead of risking problems, create our own
 * "temporary" schema instead.
 */
CREATE SCHEMA __cat_tools;

CREATE FUNCTION __cat_tools.omit_column(
  rel text
  , omit name[] DEFAULT array['oid']
) RETURNS text LANGUAGE sql STABLE AS $body$
SELECT array_to_string(array(
    SELECT attname
      FROM pg_attribute a
      WHERE attrelid = rel::regclass
        AND NOT attisdropped
        AND attnum >= 0
        AND attname != ALL( omit )
      ORDER BY attnum
    )
  , ', '
)
$body$;

/*
 * CONDITIONAL REPAIR: rebuild the _cat_tools.pg_class_v dependency chain ONLY
 * when it is actually broken, leaving a correct chain (and any user objects that
 * depend on the public views) untouched.
 *
 * Background: the published 0.2.2 could be reached two ways.
 *   - A FRESH 0.2.2 install builds the views with the fixed omit_column
 *     (`!= ALL`), which correctly strips relhasoids/relhaspkey from
 *     _cat_tools.pg_class_v. These views are already correct.
 *   - The 0.2.0->0.2.2 / 0.2.1->0.2.2 update scripts used the BUGGY omit_column
 *     (`!= ANY`, a no-op for a multi-element omit list), so relhasoids/relhaspkey
 *     were never stripped. Once such a database is binary-pg_upgraded to PG12+
 *     (where pg_class has no relhasoids/relhaspkey), the view references
 *     non-existent catalog columns and is broken.
 *
 * Fingerprint: a column literally named relhasoids on _cat_tools.pg_class_v means
 * this is a buggy-update-path build that still needs repair. This single signal
 * is sufficient: a broken-via-update database can only exist on PG<=10 (that is
 * the only major where relhasoids exists to be captured), so a 0.2.2 database on
 * PG12+ is necessarily a fresh, correct install and MUST be left alone.
 *
 * When the fingerprint is absent we do NOTHING to the views. The old
 * unconditional DROP+CREATE broke anyone with a hard dependency on the public
 * views (cat_tools.pg_class_v, cat_tools.column, cat_tools.pg_class()) even on a
 * fresh, correct install; guarding it fixes that.
 *
 * The drops and recreates live inside a DO block: DROP VIEW/FUNCTION cannot sit
 * inside a plain IF, so they run as EXECUTEd dynamic SQL. CREATE OR REPLACE VIEW
 * cannot drop columns, so the chain is dropped in reverse dependency order (no
 * CASCADE, so a DROP fails loudly on a user dependency) and recreated forward
 * with the corrected column list. cat_tools.pg_class() is dropped because it
 * RETURNS the cat_tools.pg_class_v rowtype.
 */
DO $do$
BEGIN
  IF NOT EXISTS (
    SELECT 1
      FROM pg_attribute
      WHERE attrelid = '_cat_tools.pg_class_v'::regclass
        AND attname = 'relhasoids'
        AND NOT attisdropped
        AND attnum > 0
  ) THEN
    RETURN;
  END IF;

  EXECUTE 'DROP VIEW cat_tools.column';
  EXECUTE 'DROP VIEW _cat_tools.column';
  EXECUTE 'DROP VIEW _cat_tools.pg_attribute_v';
  EXECUTE 'DROP FUNCTION cat_tools.pg_class(pg_catalog.regclass)';
  EXECUTE 'DROP VIEW cat_tools.pg_class_v';
  EXECUTE 'DROP VIEW _cat_tools.pg_class_v';

  EXECUTE format($fmt$
CREATE VIEW _cat_tools.pg_class_v AS
  SELECT c.oid AS reloid
      , %s
      , n.nspname AS relschema
    FROM pg_class c
      LEFT JOIN pg_namespace n ON( n.oid = c.relnamespace )
;
$fmt$
    , __cat_tools.omit_column('pg_catalog.pg_class', array['oid', 'relhasoids', 'relhaspkey'])
  );
  EXECUTE 'REVOKE ALL ON _cat_tools.pg_class_v FROM public';

  EXECUTE $sql$
CREATE VIEW cat_tools.pg_class_v AS
  SELECT *
    FROM _cat_tools.pg_class_v
    WHERE NOT pg_is_other_temp_schema(relnamespace)
      AND relkind IN( 'r', 'v', 'f' )
;
$sql$;
  EXECUTE 'GRANT SELECT ON cat_tools.pg_class_v TO cat_tools__usage';

  EXECUTE $sql$
CREATE FUNCTION cat_tools.pg_class(
  rel pg_catalog.regclass
) RETURNS cat_tools.pg_class_v LANGUAGE sql STABLE AS $body$
SELECT * FROM cat_tools.pg_class_v WHERE reloid = $1
$body$;
$sql$;
  EXECUTE 'REVOKE ALL ON FUNCTION cat_tools.pg_class(pg_catalog.regclass) FROM public';
  EXECUTE 'GRANT EXECUTE ON FUNCTION cat_tools.pg_class(pg_catalog.regclass) TO cat_tools__usage';

  /*
   * On PG11+, pg_attribute gained attmissingval (pseudo-type anyarray, not usable in views).
   * Include it cast to text[] on PG11+; expose as NULL::text[] on older versions.
   */
  EXECUTE format($fmt$
CREATE VIEW _cat_tools.pg_attribute_v AS
  SELECT %s
      , c.*
      , t.oid AS typoid
      , %s
      , a.attmissingval::text::text[] AS attmissingval -- SED: REQUIRES 11!
      , NULL::text[] AS attmissingval -- SED: PRIOR TO 11!
    FROM pg_attribute a
      LEFT JOIN _cat_tools.pg_class_v c ON ( c.reloid = a.attrelid )
      LEFT JOIN pg_type t ON ( t.oid = a.atttypid )
;
$fmt$
    /*
     * attmissingval is explicitly included above (cast to text[] via SED markers).
     * Omit it here so it doesn't appear twice, and omit oid to avoid conflicts on PG12+.
     */
    , __cat_tools.omit_column('pg_catalog.pg_attribute', array['oid', 'attmissingval', 'attcacheoff'])
    , __cat_tools.omit_column('pg_catalog.pg_type')
  );
  EXECUTE 'REVOKE ALL ON _cat_tools.pg_attribute_v FROM public';

  /*
   * Rebuild _cat_tools.column with computed columns first and all pg_attribute_v
   * columns (via %s) last. This ordering lets future CREATE OR REPLACE VIEW
   * upgrades extend the pg_attribute_v column list at the end without disturbing
   * existing column positions. See issue 13 for restoring logical ordering.
   */
  EXECUTE format($fmt$
CREATE VIEW _cat_tools.column AS
  SELECT pg_catalog.format_type(typoid, atttypmod) AS column_type
    , CASE typtype
        WHEN 'd' THEN pg_catalog.format_type(typbasetype, typtypmod)
        WHEN 'e' THEN 'text'
        ELSE pg_catalog.format_type(typoid, atttypmod)
      END AS base_type
    , pk.conkey AS pk_columns
    , ARRAY[attnum] <@ pk.conkey AS is_pk_member
    , (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
          FROM pg_catalog.pg_attrdef d
          WHERE d.adrelid = a.attrelid
            AND d.adnum = a.attnum
            AND a.atthasdef
        ) AS column_default
    , %s
    FROM _cat_tools.pg_attribute_v a
      LEFT JOIN pg_constraint pk
        ON ( reloid = pk.conrelid )
          AND pk.contype = 'p'
;
$fmt$
    , __cat_tools.omit_column('_cat_tools.pg_attribute_v')
  );
  EXECUTE 'REVOKE ALL ON _cat_tools.column FROM public';

  EXECUTE $sql$
CREATE VIEW cat_tools.column AS
  SELECT *
    FROM _cat_tools.column
    WHERE NOT pg_is_other_temp_schema(relnamespace)
      AND attnum > 0
      AND NOT attisdropped
      AND relkind IN( 'r', 'v', 'f' )
      AND (
        pg_has_role(SESSION_USER, relowner, 'USAGE'::text)
        OR has_column_privilege(SESSION_USER, reloid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)
      )
    ORDER BY relschema, relname, attnum
;
$sql$;
  EXECUTE 'GRANT SELECT ON cat_tools.column TO cat_tools__usage';
END
$do$;

-- Drop temporary helper objects
DROP FUNCTION __cat_tools.omit_column(
  rel text
  , omit name[]
);
DROP SCHEMA __cat_tools;

/*
 * Correct the c/f/m relkind mapping per pg_class.h in both functions.
 * CREATE OR REPLACE preserves existing grants.
 */

CREATE OR REPLACE FUNCTION cat_tools.relation__kind(
  relkind cat_tools.relation_relkind
) RETURNS cat_tools.relation_type LANGUAGE sql STRICT IMMUTABLE AS $body$
/* relkind values per pg_class.h. */
SELECT CASE relkind
  WHEN 'r' THEN 'table'
  WHEN 'i' THEN 'index'
  WHEN 'S' THEN 'sequence'
  WHEN 't' THEN 'toast table'
  WHEN 'v' THEN 'view'
  WHEN 'c' THEN 'composite type'
  WHEN 'f' THEN 'foreign table'
  WHEN 'm' THEN 'materialized view'
END::cat_tools.relation_type
$body$;

CREATE OR REPLACE FUNCTION cat_tools.relation__relkind(
  kind cat_tools.relation_type
) RETURNS cat_tools.relation_relkind LANGUAGE sql STRICT IMMUTABLE AS $body$
/* Mapping per pg_class.h, same as relation__kind() above. */
SELECT CASE kind
  WHEN 'table' THEN 'r'
  WHEN 'index' THEN 'i'
  WHEN 'sequence' THEN 'S'
  WHEN 'toast table' THEN 't'
  WHEN 'view' THEN 'v'
  WHEN 'composite type' THEN 'c'
  WHEN 'foreign table' THEN 'f'
  WHEN 'materialized view' THEN 'm'
END::cat_tools.relation_relkind
$body$;

/*
 * Converge trigger__parse to the fresh-install body. The published 0.2.0->0.2.2
 * / 0.2.1->0.2.2 update scripts create this function with a body that hardcodes
 * ' EXECUTE PROCEDURE ' and omits the empty-args guard; a fresh install has the
 * corrected body. PG11+ emits EXECUTE FUNCTION, so match either keyword, and
 * guard empty args (otherwise "cannot determine type of empty array" when
 * parsing a trigger). CREATE OR REPLACE preserves grants and the comment and has
 * no dependents to break, so no DROP is needed; it is a no-op on a fresh 0.2.2
 * (already correct) and a repair on the update path. The signature is identical
 * to the fresh 0.2.3 definition, and the body below is copied verbatim from
 * sql/cat_tools.sql.in.
 */
CREATE OR REPLACE FUNCTION cat_tools.trigger__parse(
  trigger_oid oid
  , OUT trigger_table regclass
  , OUT timing text
  , OUT events text[]
  , OUT defer text
  , OUT row_statement text
  , OUT when_clause text
  , OUT trigger_function regprocedure
  , OUT function_arguments text[]
) RETURNS record STABLE LANGUAGE plpgsql AS $body$
DECLARE
  r_trigger pg_catalog.pg_trigger;
  v_triggerdef text;
  v_create_stanza text;
  v_on_clause text;
  v_execute_clause text;

  v_work text;
  v_array text[];
BEGIN
  /*
   * Do this first to make sure trigger exists.
   *
   * TODO: After we no longer support < 9.6, test v_triggerdef for NULL instead
   * using the extra block here.
   */
  BEGIN
    SELECT * INTO STRICT r_trigger FROM pg_catalog.pg_trigger WHERE oid = trigger_oid;
  EXCEPTION WHEN no_data_found THEN
    RAISE EXCEPTION 'trigger with OID % does not exist', trigger_oid
      USING errcode = 'undefined_object' -- 42704
    ;
  END;
  trigger_table := r_trigger.tgrelid;
  trigger_function := r_trigger.tgfoid;

  v_triggerdef := pg_catalog.pg_get_triggerdef(trigger_oid, true);

  v_create_stanza := format(
    'CREATE %sTRIGGER %I '
    , CASE WHEN r_trigger.tgconstraint=0 THEN '' ELSE 'CONSTRAINT ' END
    , r_trigger.tgname
  );
  -- Strip CREATE [CONSTRAINT] TRIGGER ... off
  v_work := replace( v_triggerdef, v_create_stanza, '' );

  -- Get BEFORE | AFTER | INSTEAD OF
  timing := split_part( v_work, ' ', 1 );
  timing := timing || CASE timing WHEN 'INSTEAD' THEN ' OF' ELSE '' END;

  -- Strip off timing clause
  v_work := replace( v_work, timing || ' ', '' );

  -- Get array of events (INSERT, UPDATE [OF column, column], DELETE, TRUNCATE)
  v_on_clause := ' ON ' || r_trigger.tgrelid::pg_catalog.regclass || ' ';
  v_array := regexp_split_to_array( v_work, v_on_clause );
  events := string_to_array( v_array[1], ' OR ' );
  -- Get everything after ON table_name
  v_work := v_array[2];
  RAISE DEBUG 'v_work "%"', v_work;

  -- Strip off FROM referenced_table if we have it
  IF r_trigger.tgconstrrelid<>0 THEN
    v_work := replace(
      v_work
      , 'FROM ' || r_trigger.tgconstrrelid::pg_catalog.regclass || ' '
      , ''
    );
  END IF;
  RAISE DEBUG 'v_work "%"', v_work;

  /*
   * Get function arguments. PG11+ uses "EXECUTE FUNCTION"; older versions use
   * "EXECUTE PROCEDURE". Note: ::regproc returns the internal pg_temp_N schema
   * name while pg_get_triggerdef uses the pg_temp alias, so we match on EXECUTE
   * PROCEDURE/FUNCTION + any non-space chars (the function name) rather than the
   * specific function name.
   */
  v_execute_clause := E' EXECUTE (?:PROCEDURE|FUNCTION) \\S+\\(';
  v_array := regexp_split_to_array( v_work, v_execute_clause );
  EXECUTE CASE
      WHEN trim(rtrim(v_array[2], ')')) = '' THEN 'SELECT ARRAY[]::text[]'
      ELSE format('SELECT array[ %s ]', rtrim( v_array[2], ')' ))
    END
    INTO function_arguments
  ;
  RAISE DEBUG 'v_array[2] "%"', v_array[2];
  -- Get everything prior to EXECUTE PROCEDURE ...
  v_work := v_array[1];
  RAISE DEBUG 'v_work "%"', v_work;

  row_statement := (regexp_matches( v_work, 'FOR EACH (ROW|STATEMENT)' ))[1];

  -- Get [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
  v_array := regexp_split_to_array( v_work, 'FOR EACH (ROW|STATEMENT)' );
  RAISE DEBUG 'v_work = "%", v_array = "%"', v_work, v_array;
  defer := rtrim(v_array[1]);

  IF r_trigger.tgqual IS NOT NULL THEN
    when_clause := rtrim(
      (regexp_split_to_array( v_array[2], E' WHEN \\(' ))[2]
      , ')'
    );
  END IF;

  RAISE DEBUG
$$v_create_stanza = "%"
  v_on_clause = "%"
  v_execute_clause = "%"$$
    , v_create_stanza
    , v_on_clause
    , v_execute_clause
  ;

  RETURN;
END
$body$;

-- vi: expandtab ts=2 sw=2