CREATE SCHEMA __cat_tools;

CREATE FUNCTION __cat_tools.exec(
  sql text
) RETURNS void LANGUAGE plpgsql AS $body$
BEGIN
  RAISE DEBUG 'sql = %', sql;
  EXECUTE sql;
END
$body$;

CREATE FUNCTION __cat_tools.create_function(
  function_name text
  , args text
  , options text
  , body text
  , grants text DEFAULT NULL
  , comment text DEFAULT NULL
) RETURNS void LANGUAGE plpgsql AS $body$
DECLARE
  c_simple_args CONSTANT text := cat_tools.function__arg_types_text(args);

  create_template CONSTANT text := $template$
CREATE OR REPLACE FUNCTION %s(
%s
) RETURNS %s AS
%L
$template$
  ;

  revoke_template CONSTANT text := $template$
REVOKE ALL ON FUNCTION %s(
%s
) FROM public;
$template$
  ;

  grant_template CONSTANT text := $template$
GRANT EXECUTE ON FUNCTION %s(
%s
) TO %s;
$template$
  ;

  comment_template CONSTANT text := $template$
COMMENT ON FUNCTION %s(
%s
) IS %L;
$template$
  ;

BEGIN
  PERFORM __cat_tools.exec( format(
      create_template
      , function_name
      , args
      , options -- TODO: Force search_path if options ~* 'definer'
      , body
    ) )
  ;
  PERFORM __cat_tools.exec( format(
      revoke_template
      , function_name
      , c_simple_args
    ) )
  ;

  IF grants IS NOT NULL THEN
    PERFORM __cat_tools.exec( format(
        grant_template
        , function_name
        , c_simple_args
        , grants
      ) )
    ;
  END IF;

  IF comment IS NOT NULL THEN
    PERFORM __cat_tools.exec( format(
        comment_template
        , function_name
        , c_simple_args
        , comment
      ) )
    ;
  END IF;
END
$body$;


-- NOTE: Changes already applied on the path up to 0.2.3:
-- ALTER DEFAULT PRIVILEGES, pg_class_v fix, pg_attribute_v fix, pg_extension_v fix,
-- pg_extension__get recreation, cat_tools.column recreation are all skipped here.
-- They land in 0.2.2 (fresh install) / the 0.2.2→0.2.3 rebuild, so a database
-- reaching 0.2.3 by any route already has them.

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L165
SELECT __cat_tools.create_function(
  '_cat_tools.function__arg_to_regprocedure'
  , 'arguments text, function_suffix text, api_function_name text'
  , 'pg_catalog.regprocedure LANGUAGE plpgsql'
  , $body$
DECLARE
  /*
   * Template for creating a temporary function with the user-provided argument
   * signature. This allows us to leverage PostgreSQL's parser to validate and
   * extract argument information without permanently creating a function.
   * Using plpgsql language for the temp function to handle any return type.
   */
  c_template CONSTANT text := $fmt$CREATE FUNCTION pg_temp.cat_tools__function__%s__temp_function(
    %s
  ) RETURNS %s LANGUAGE plpgsql AS 'BEGIN RETURN; END'
  $fmt$;

  temp_proc pg_catalog.regprocedure;
  sql text;
BEGIN
  /*
   * Security check: Ensure current_user == session_user to detect SECURITY DEFINER context
   * This prevents SQL injection attacks through elevated privileges.
   */
  IF current_user != session_user THEN
    RAISE EXCEPTION USING
      ERRCODE = '28000' -- invalid_authorization_specification
      , MESSAGE = 'potential use of SECURITY DEFINER detected'
      , DETAIL = format('current_user is %s, session_user is %s', current_user, session_user)
      , HINT = 'Helper functions must not be called from SECURITY DEFINER context.';
  END IF;
  sql := format(
    c_template
    , function_suffix
    , arguments
    , 'void'
  );
  --RAISE DEBUG 'Executing SQL %', sql;
  DECLARE
    v_type pg_catalog.regtype;
  BEGIN
    EXECUTE sql;
  EXCEPTION WHEN invalid_function_definition THEN
    v_type := (regexp_matches( SQLERRM, 'function result type must be ([^ ]+) because of' ))[1];
    sql := format(
      c_template
      , function_suffix
      , arguments
      , v_type
    );
    EXECUTE sql;
  END;

  /*
   * Get new OID. *This must be done dynamically!* Otherwise we get stuck
   * with a CONST oid after first compilation. The regproc cast ensures there's
   * only one function with this name. The cast to regprocedure is for the sake
   * of the DROP down below.
   */
  EXECUTE format(
    $$SELECT 'pg_temp.cat_tools__function__%s__temp_function'::pg_catalog.regproc::pg_catalog.regprocedure$$
    , function_suffix
  ) INTO temp_proc;

  RETURN temp_proc;
END
$body$
  , 'cat_tools__usage'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L233
SELECT __cat_tools.create_function(
  '_cat_tools.function__drop_temp'
  , 'p_regprocedure pg_catalog.regprocedure, api_function_name text'
  , 'void LANGUAGE plpgsql'
  , $body$
BEGIN
  /*
   * Security check: Ensure current_user == session_user to detect SECURITY DEFINER context
   * This prevents SQL injection attacks through elevated privileges.
   */
  IF current_user != session_user THEN
    RAISE EXCEPTION USING
      ERRCODE = '28000' -- invalid_authorization_specification
      , MESSAGE = 'potential use of SECURITY DEFINER detected'
      , DETAIL = format('API function %s must not be called from a SECURITY DEFINER function', api_function_name)
      , HINT = 'We detect SECURITY DEFINER context by comparing current_user and session_user, which can cause false positives if SET ROLE is used';
  END IF;

  EXECUTE 'DROP ROUTINE ' || p_regprocedure;
END
$body$
  , 'cat_tools__usage'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L254
/*
 * Both helpers above are created via __cat_tools.create_function, which applies
 * the REVOKE-from-PUBLIC + GRANT-to-cat_tools__usage automatically (and keeps it
 * in sync with the fresh-install script). Only the schema USAGE grant remains.
 */
GRANT USAGE ON SCHEMA _cat_tools TO cat_tools__usage;

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L280
ALTER TYPE cat_tools.relation_type ADD VALUE 'partitioned table';
ALTER TYPE cat_tools.relation_type ADD VALUE 'partitioned index';

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L294
ALTER TYPE cat_tools.relation_relkind ADD VALUE 'p';
ALTER TYPE cat_tools.relation_relkind ADD VALUE 'I';

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L299
CREATE TYPE cat_tools.routine_prokind AS ENUM(
  'f' -- function
  , 'p' -- procedure
  , 'a' -- aggregate
  , 'w' -- window
);
COMMENT ON TYPE cat_tools.routine_prokind IS $$Valid values for `pg_proc.prokind`$$;

CREATE TYPE cat_tools.routine_type AS ENUM(
  'function'
  , 'procedure'
  , 'aggregate'
  , 'window'
);
COMMENT ON TYPE cat_tools.routine_type IS $$Types of routines stored in `pg_proc`$$;

CREATE TYPE cat_tools.routine_proargmode AS ENUM(
  'i' -- in
  , 'o' -- out
  , 'b' -- inout
  , 'v' -- variadic
  , 't' -- table
);
COMMENT ON TYPE cat_tools.routine_proargmode IS $$Valid values for `pg_proc.proargmodes` elements$$;

CREATE TYPE cat_tools.routine_argument_mode AS ENUM(
  'in'
  , 'out'
  , 'inout'
  , 'variadic'
  , 'table'
);
COMMENT ON TYPE cat_tools.routine_argument_mode IS $$Argument modes for function/procedure parameters$$;

CREATE TYPE cat_tools.routine_provolatile AS ENUM(
  'i' -- immutable
  , 's' -- stable
  , 'v' -- volatile
);
COMMENT ON TYPE cat_tools.routine_provolatile IS $$Valid values for `pg_proc.provolatile`$$;

CREATE TYPE cat_tools.routine_volatility AS ENUM(
  'immutable'
  , 'stable'
  , 'volatile'
);
COMMENT ON TYPE cat_tools.routine_volatility IS $$Volatility levels for functions/procedures$$;

CREATE TYPE cat_tools.routine_proparallel AS ENUM(
  's' -- safe
  , 'r' -- restricted
  , 'u' -- unsafe
);
COMMENT ON TYPE cat_tools.routine_proparallel IS $$Valid values for `pg_proc.proparallel`$$;

CREATE TYPE cat_tools.routine_parallel_safety AS ENUM(
  'safe'
  , 'restricted'
  , 'unsafe'
);
COMMENT ON TYPE cat_tools.routine_parallel_safety IS $$Parallel safety levels for functions/procedures$$;

CREATE TYPE cat_tools.routine_argument AS (
  argument_name text
  , argument_type pg_catalog.regtype
  , argument_mode cat_tools.routine_argument_mode
  , argument_default text
);
COMMENT ON TYPE cat_tools.routine_argument IS $$Detailed information about a single function/procedure argument$$;

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L372
SELECT __cat_tools.create_function(
  'cat_tools.relation__kind'
  , 'relkind cat_tools.relation_relkind'
  , 'cat_tools.relation_type LANGUAGE sql STRICT IMMUTABLE'
  , $body$
/*
 * The c/f/m arms were previously mapped backwards (c->materialized view,
 * f->composite type, m->foreign table), disagreeing with pg_class.relkind
 * semantics documented in src/include/catalog/pg_class.h. Correct mapping is
 * c=composite type, f=foreign table, m=materialized view.
 */
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'
  WHEN 'p' THEN 'partitioned table'
  WHEN 'I' THEN 'partitioned index'
END::cat_tools.relation_type
$body$
  , 'cat_tools__usage'
  , 'Mapping from <pg_class.relkind> to a <cat_tools.relation_type>'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L394
SELECT __cat_tools.create_function(
  'cat_tools.relation__relkind'
  , 'kind cat_tools.relation_type'
  , 'cat_tools.relation_relkind LANGUAGE sql STRICT IMMUTABLE'
  , $body$
/*
 * The composite type / foreign table / materialized view arms were previously
 * mapped backwards (composite type->f, foreign table->m, materialized view->c),
 * disagreeing with pg_class.relkind semantics documented in
 * src/include/catalog/pg_class.h. Correct mapping is composite type=c,
 * foreign table=f, materialized view=m.
 */
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'
  WHEN 'partitioned table' THEN 'p'
  WHEN 'partitioned index' THEN 'I'
END::cat_tools.relation_relkind
$body$
  , 'cat_tools__usage'
  , 'Mapping from <cat_tools.relation_type> to a <pg_class.relkind> value'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L416
SELECT __cat_tools.create_function(
  'cat_tools.relation__relkind'
  , 'kind text'
  , 'cat_tools.relation_relkind LANGUAGE sql STRICT IMMUTABLE'
  , $body$SELECT cat_tools.relation__relkind(kind::cat_tools.relation_type)$body$
  , 'cat_tools__usage'
  , 'Mapping from <cat_tools.relation_type> to a <pg_class.relkind> value'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L425
SELECT __cat_tools.create_function(
  'cat_tools.relation__kind'
  , 'relkind text'
  , 'cat_tools.relation_type LANGUAGE sql STRICT IMMUTABLE'
  , $body$SELECT cat_tools.relation__kind(relkind::cat_tools.relation_relkind)$body$
  , 'cat_tools__usage'
  , 'Mapping from <cat_tools.relation_type> to a <pg_class.relkind> value'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L434
SELECT __cat_tools.create_function(
  'cat_tools.routine__type'
  , 'prokind cat_tools.routine_prokind'
  , 'cat_tools.routine_type LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE'
  , $body$
SELECT CASE prokind
  WHEN 'f' THEN 'function'
  WHEN 'p' THEN 'procedure'
  WHEN 'a' THEN 'aggregate'
  WHEN 'w' THEN 'window'
END::cat_tools.routine_type
$body$
  , 'cat_tools__usage'
  , 'Mapping from cat_tools.routine_prokind to cat_tools.routine_type'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L459
CREATE CAST ("char" AS cat_tools.routine_prokind)    WITH INOUT AS IMPLICIT;
CREATE CAST ("char" AS cat_tools.routine_proargmode)  WITH INOUT AS IMPLICIT;
CREATE CAST ("char" AS cat_tools.routine_provolatile) WITH INOUT AS IMPLICIT;
CREATE CAST ("char" AS cat_tools.routine_proparallel) WITH INOUT AS IMPLICIT;

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L463
SELECT __cat_tools.create_function(
  'cat_tools.routine__argument_mode'
  , 'proargmode cat_tools.routine_proargmode'
  , 'cat_tools.routine_argument_mode LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE'
  , $body$
SELECT CASE proargmode
  WHEN 'i' THEN 'in'
  WHEN 'o' THEN 'out'
  WHEN 'b' THEN 'inout'
  WHEN 'v' THEN 'variadic'
  WHEN 't' THEN 'table'
END::cat_tools.routine_argument_mode
$body$
  , 'cat_tools__usage'
  , 'Mapping from cat_tools.routine_proargmode to cat_tools.routine_argument_mode'
);

SELECT __cat_tools.create_function(
  'cat_tools.routine__volatility'
  , 'provolatile cat_tools.routine_provolatile'
  , 'cat_tools.routine_volatility LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE'
  , $body$
SELECT CASE provolatile
  WHEN 'i' THEN 'immutable'
  WHEN 's' THEN 'stable'
  WHEN 'v' THEN 'volatile'
END::cat_tools.routine_volatility
$body$
  , 'cat_tools__usage'
  , 'Mapping from cat_tools.routine_provolatile to cat_tools.routine_volatility'
);

SELECT __cat_tools.create_function(
  'cat_tools.routine__parallel_safety'
  , 'proparallel cat_tools.routine_proparallel'
  , 'cat_tools.routine_parallel_safety LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE'
  , $body$
SELECT CASE proparallel
  WHEN 's' THEN 'safe'
  WHEN 'r' THEN 'restricted'
  WHEN 'u' THEN 'unsafe'
END::cat_tools.routine_parallel_safety
$body$
  , 'cat_tools__usage'
  , 'Mapping from cat_tools.routine_proparallel to cat_tools.routine_parallel_safety'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L502
SELECT __cat_tools.create_function(
  'cat_tools.routine__arg_types'
  , $$func pg_catalog.regprocedure$$
  , $$pg_catalog.regtype[] LANGUAGE sql STABLE$$
  , $body$
SELECT string_to_array(proargtypes::text,' ')::pg_catalog.regtype[]
FROM pg_proc
WHERE oid = $1::pg_catalog.regproc
$body$
  , 'cat_tools__usage'
  , 'Returns all argument types for a function as an array of regtype'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L519
SELECT __cat_tools.create_function(
  'cat_tools.routine__arg_names'
  , $$func pg_catalog.regprocedure$$
  , $$text[] LANGUAGE sql STABLE$$
  , $body$
SELECT
  CASE
    WHEN proargnames IS NULL THEN
      -- No named arguments, return array of NULLs matching proargtypes length
      CASE
        WHEN pronargs > 0 THEN
          array_fill(NULL::text, ARRAY[pronargs])
        ELSE
          '{}'::text[]
      END
    WHEN proargmodes IS NULL THEN
      -- All arguments are IN mode, proargnames and proargtypes align
      array(
        SELECT CASE WHEN name = '' THEN NULL ELSE name END
        FROM unnest(proargnames) AS name
      )
    ELSE
      -- Mixed argument modes, need to filter names to match proargtypes
      array(
        SELECT
          CASE
            WHEN i <= array_length(proargnames, 1) AND proargnames[i] != '' THEN proargnames[i]
            ELSE NULL
          END
        FROM unnest(proargmodes) WITH ORDINALITY AS t(mode, i)
        WHERE mode IN ('i', 'b', 'v')
      )
  END
FROM pg_proc
WHERE oid = $1::pg_catalog.regproc
$body$
  , 'cat_tools__usage'
  , 'Returns all argument names for a function as an array of text. Empty strings are converted to NULL.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L561
SELECT __cat_tools.create_function(
  'cat_tools.routine__arg_types_text'
  , $$func pg_catalog.regprocedure$$
  , $$text LANGUAGE sql STABLE$$
  , $body$
SELECT array_to_string(cat_tools.routine__arg_types($1), ', ')
$body$
  , 'cat_tools__usage'
  , 'Returns all argument types for a function as a comma-separated text string'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L574
SELECT __cat_tools.create_function(
  'cat_tools.routine__arg_names_text'
  , $$func pg_catalog.regprocedure$$
  , $$text LANGUAGE sql STABLE$$
  , $body$
SELECT array_to_string(cat_tools.routine__arg_names($1), ', ')
$body$
  , 'cat_tools__usage'
  , 'Returns all argument names for a function as a comma-separated text string'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L587
SELECT __cat_tools.create_function(
  'cat_tools.routine__parse_arg_types'
  , $$arguments text$$
  , $$pg_catalog.regtype[] LANGUAGE plpgsql$$
  , $body$
DECLARE
  c_temp_proc CONSTANT pg_catalog.regprocedure := _cat_tools.function__arg_to_regprocedure(arguments, 'arg_types', 'cat_tools.routine__parse_arg_types');
  result pg_catalog.regtype[];
BEGIN
  result := cat_tools.routine__arg_types(c_temp_proc);

  -- Clean up the temporary function
  PERFORM _cat_tools.function__drop_temp(c_temp_proc, 'cat_tools.routine__parse_arg_types');

  RETURN result;
END
$body$
  , 'cat_tools__usage'
  , 'Returns argument types for a function argument body as an array. Unlike a
  normal regprocedure cast, this function accepts anything that is valid when
  defining a function.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L612
SELECT __cat_tools.create_function(
  'cat_tools.routine__parse_arg_names'
  , $$arguments text$$
  , $$text[] LANGUAGE plpgsql$$
  , $body$
DECLARE
  c_temp_proc CONSTANT pg_catalog.regprocedure := _cat_tools.function__arg_to_regprocedure(arguments, 'arg_names', 'cat_tools.routine__parse_arg_names');
  result text[];
BEGIN
  result := cat_tools.routine__arg_names(c_temp_proc);

  -- Clean up the temporary function
  PERFORM _cat_tools.function__drop_temp(c_temp_proc, 'cat_tools.routine__parse_arg_names');

  RETURN result;
END
$body$
  , 'cat_tools__usage'
  , 'Returns argument names for a function argument body as an array. Only
  includes IN, INOUT, and VARIADIC arguments (matching routine__parse_arg_types
  behavior). Unnamed arguments appear as NULL in the result array.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L637
SELECT __cat_tools.create_function(
  'cat_tools.routine__parse_arg_types_text'
  , $$arguments text$$
  , $$text LANGUAGE sql$$
  , $body$
SELECT array_to_string(cat_tools.routine__parse_arg_types($1), ', ')
$body$
  , 'cat_tools__usage'
  , 'Returns argument types for a function argument body as text. Unlike a
  normal regprocedure cast, this function accepts anything that is valid when
  defining a function.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L653
SELECT __cat_tools.create_function(
  'cat_tools.routine__parse_arg_names_text'
  , $$arguments text$$
  , $$text LANGUAGE sql$$
  , $body$
SELECT array_to_string(cat_tools.routine__parse_arg_names($1), ', ')
$body$
  , 'cat_tools__usage'
  , 'Returns argument names for a function argument body as text. Only
  includes IN, INOUT, and VARIADIC arguments (matching routine__parse_arg_types_text
  behavior). Unnamed arguments appear as empty strings in the result.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L670
SELECT __cat_tools.create_function(
  'cat_tools.function__arg_types'
  , $$arguments text$$
  , $$pg_catalog.regtype[] LANGUAGE plpgsql$$
  , $body$
BEGIN
  RAISE WARNING 'function__arg_types() is deprecated, use routine__parse_arg_types instead';

  RETURN cat_tools.routine__parse_arg_types(arguments);
END
$body$
  , 'cat_tools__usage'
  , 'DEPRECATED: Use routine__parse_arg_types instead.
  Returns argument types for a function argument body as regtype[]. Only
  includes IN, INOUT, and VARIADIC arguments.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L689
SELECT __cat_tools.create_function(
  'cat_tools.function__arg_types_text'
  , $$arguments text$$
  , $$text LANGUAGE plpgsql$$
  , $body$
BEGIN
  RAISE WARNING 'function__arg_types_text() is deprecated, use routine__parse_arg_types_text instead';

  RETURN cat_tools.routine__parse_arg_types_text(arguments);
END
$body$
  , 'cat_tools__usage'
  , 'DEPRECATED: Use routine__parse_arg_types_text instead.
  Returns argument types for a function argument body as text. Only
  includes IN, INOUT, and VARIADIC arguments.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L708
SELECT __cat_tools.create_function(
  'cat_tools.regprocedure'
  , $$
  function_name text
  , arguments text$$
  , $$pg_catalog.regprocedure LANGUAGE sql$$
  , $body$
SELECT format(
  '%s(%s)'
  , $1
  , cat_tools.routine__parse_arg_types_text($2)
)::pg_catalog.regprocedure
$body$
  , 'cat_tools__usage'
  , 'Returns a regprocedure for a given function name and arguments. Unlike a
  normal regprocedure cast, arguments can contain anything that is valid when
  defining a function.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L742
ALTER TYPE cat_tools.object_type ADD VALUE 'partitioned table' AFTER 'foreign table';
ALTER TYPE cat_tools.object_type ADD VALUE 'partitioned index' AFTER 'partitioned table';

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L890
SELECT __cat_tools.create_function(
  'cat_tools.object__catalog'
  , 'object_type cat_tools.object_type'
  , 'pg_catalog.regclass LANGUAGE sql STRICT IMMUTABLE'
  , $body$
SELECT (
  'pg_catalog.'
  || CASE
    WHEN object_type = ANY( array[
    'table'
    , 'index'
    , 'sequence'
    , 'toast table'
    , 'view'
    , 'materialized view'
    , 'composite type'
    , 'foreign table'
    , 'partitioned table'
    , 'partitioned index'
      ]::cat_tools.object_type[] )
    THEN 'pg_class'
    WHEN object_type = ANY( '{domain constraint,table constraint}'::cat_tools.object_type[] )
      THEN 'pg_constraint'
    WHEN object_type = ANY( '{aggregate,function}'::cat_tools.object_type[] )
      THEN 'pg_proc'
    WHEN object_type::text LIKE '% column'
      THEN 'pg_attribute'
    ELSE CASE object_type
      -- Unusual cases
      WHEN 'default value' THEN 'pg_attrdef'
      WHEN 'large object' THEN 'pg_largeobject'
      WHEN 'operator class' THEN 'pg_opclass'
      WHEN 'operator family' THEN 'pg_opfamily'
      WHEN 'operator of access method' THEN 'pg_amop'
      WHEN 'function of access method' THEN 'pg_amproc'
      WHEN 'rule' THEN 'pg_rewrite'
      WHEN 'schema' THEN 'pg_namespace'
      WHEN 'text search parser' THEN 'pg_ts_parser'
      WHEN 'text search dictionary' THEN 'pg_ts_dict'
      WHEN 'text search template' THEN 'pg_ts_template'
      WHEN 'text search configuration' THEN 'pg_ts_config'
      WHEN 'role' THEN 'pg_authid'
      WHEN 'foreign-data wrapper' THEN 'pg_foreign_data_wrapper'
      WHEN 'server' THEN 'pg_foreign_server'
      WHEN 'user mapping' THEN 'pg_user_mapping'
      WHEN 'default acl' THEN 'pg_default_acl'
      WHEN 'event trigger' THEN 'pg_event_trigger' -- SED: REQUIRES 9.3!
      WHEN 'access method' THEN 'pg_am'
      ELSE 'pg_' || object_type::text
      END
    END
  )::pg_catalog.regclass
$body$
  , 'cat_tools__usage'
  , 'Returns catalog table that is used to store <object_type> objects'
);



-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L1202
SELECT __cat_tools.create_function(
  '_cat_tools._pg_sv_column_array'
  , 'OID, SMALLINT[]'
  , 'NAME[] LANGUAGE sql STABLE'
  , $$
    SELECT ARRAY(
        SELECT a.attname
          FROM unnest($2) WITH ORDINALITY AS t(attnum, i)
          JOIN pg_catalog.pg_attribute a ON a.attnum = t.attnum
         WHERE attrelid = $1
         ORDER BY i
    )
$$
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L1582
SELECT __cat_tools.create_function(
  'cat_tools.relation__is_temp'
  , 'relation pg_catalog.regclass'
  , $$boolean LANGUAGE sql STRICT STABLE$$
  , $body$
SELECT relnamespace::pg_catalog.regnamespace::text ~ '^pg_temp'
FROM pg_catalog.pg_class 
WHERE oid = $1
$body$
  , 'cat_tools__usage'
  , $$Returns true if the relation is a temporary table (lives in a schema that starts with 'pg_temp').$$
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L1597
SELECT __cat_tools.create_function(
  'cat_tools.relation__is_catalog'
  , 'relation pg_catalog.regclass'
  , $$boolean LANGUAGE sql STRICT STABLE$$
  , $body$
SELECT relnamespace::pg_catalog.regnamespace::text = 'pg_catalog'
FROM pg_catalog.pg_class 
WHERE oid = $1
$body$
  , 'cat_tools__usage'
  , 'Returns true if the relation is in the pg_catalog schema.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L1610
SELECT __cat_tools.create_function(
  'cat_tools.relation__column_names'
  , 'relation pg_catalog.regclass'
  , $$text[] LANGUAGE sql STRICT STABLE$$
  , $body$
SELECT array_agg(quote_ident(attname) ORDER BY attnum)
FROM pg_catalog.pg_attribute
WHERE attrelid = $1
  AND attnum > 0
  AND NOT attisdropped
$body$
  , 'cat_tools__usage'
  , 'Returns an array of quoted column names for a relation in ordinal position order.'
);

-- https://github.com/jnasbyupgrade/cat_tools/blob/new_functions/sql/cat_tools.sql.in#L1702
SELECT __cat_tools.create_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[]
$$
  , $$record STABLE LANGUAGE plpgsql$$
  , $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$
  , 'cat_tools__usage'
  , 'Provide details about a trigger.'
);


DROP FUNCTION __cat_tools.exec(
  sql text
);
DROP FUNCTION __cat_tools.create_function(
  function_name text
  , args text
  , options text
  , body text
  , grants text
  , comment text
);
DROP SCHEMA __cat_tools;

/*
 * Converge the ACL of the five enum types that predate 0.2.2 (constraint_type,
 * procedure_type, relation_type, relation_relkind, object_type) to the
 * fresh-install state. ALTER DEFAULT PRIVILEGES (near the top of
 * sql/cat_tools.sql.in) only applies to objects created AFTER it runs; these
 * types were created back in 0.2.0/0.2.1, so the 0.2.0->0.2.2 and 0.2.1->0.2.2
 * update scripts never granted USAGE on them, and neither does 0.2.2->0.2.3 --
 * a 0.2.0/0.2.1-origin database is still missing the grant on reaching 0.2.3.
 * Both already-released scripts are immutable, so the fix converges forward
 * here instead, on the first still-unpublished update script downstream of the
 * gap. A fresh install at any version creates these types AFTER the statement
 * and already has the grant, so GRANT's idempotency makes this a no-op there.
 */
GRANT USAGE ON TYPE
  cat_tools.constraint_type
  , cat_tools.procedure_type
  , cat_tools.relation_type
  , cat_tools.relation_relkind
  , cat_tools.object_type
  TO cat_tools__usage;