pg_tle--1.0.0.sql [341:532]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
) TO pgtle_admin;

CREATE TYPE pgtle.pg_tle_features as ENUM ('passcheck');
CREATE TYPE pgtle.password_types as ENUM ('PASSWORD_TYPE_PLAINTEXT', 'PASSWORD_TYPE_MD5', 'PASSWORD_TYPE_SCRAM_SHA_256');

CREATE TABLE pgtle.feature_info(
	feature pgtle.pg_tle_features,
	schema_name text,
	proname text,
	obj_identity text NOT NULL,
  PRIMARY KEY(feature, schema_name, proname));

SELECT pg_catalog.pg_extension_config_dump('pgtle.feature_info', '');

GRANT SELECT on pgtle.feature_info TO PUBLIC;

-- Helper function to register features in the feature_info table
CREATE FUNCTION pgtle.register_feature(proc regproc, feature pgtle.pg_tle_features)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
pg_proc_relid oid;
proc_oid oid;
schema_name text;
nspoid oid;
proname text;
proc_schema_name text;
ident text;

BEGIN
	SELECT oid into nspoid FROM pg_catalog.pg_namespace
	where nspname = 'pg_catalog';

	SELECT oid into pg_proc_relid from pg_catalog.pg_class
	where relname = 'pg_proc' and relnamespace = nspoid;

	SELECT pg_namespace.nspname, pg_proc.oid, pg_proc.proname into proc_schema_name, proc_oid, proname FROM
	pg_catalog.pg_namespace, pg_catalog.pg_proc
	where pg_proc.oid = proc AND pg_proc.pronamespace = pg_namespace.oid;

	SELECT identity into ident FROM pg_catalog.pg_identify_object(pg_proc_relid, proc_oid, 0);

	INSERT INTO pgtle.feature_info VALUES (feature, proc_schema_name, proname, ident);
END;
$$;

-- Helper function to softly fail if we try to register a function that already exists
CREATE FUNCTION pgtle.register_feature_if_not_exists(proc regproc, feature pgtle.pg_tle_features)
RETURNS bool
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pgtle.register_feature(proc, feature);
  RETURN TRUE;
EXCEPTION
  -- only catch the unique violation. let all other exceptions pass through.
  WHEN unique_violation THEN
    RETURN FALSE;
END;
$$;

-- Helper function to delete from table
CREATE FUNCTION pgtle.unregister_feature(proc regproc, feature pgtle.pg_tle_features)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
	pg_proc_relid oid;
	proc_oid oid;
	schema_name text;
	nspoid oid;
	proc_name text;
	proc_schema_name text;
	ident text;
	row_count bigint;
BEGIN
	SELECT oid into nspoid
  FROM pg_catalog.pg_namespace
	WHERE nspname = 'pg_catalog';

	SELECT oid into pg_proc_relid
  FROM pg_catalog.pg_class
	WHERE
		relname = 'pg_proc' AND
		relnamespace = nspoid;

	SELECT
		pg_namespace.nspname,
		pg_proc.oid,
		pg_proc.proname
  INTO
		proc_schema_name,
		proc_oid,
		proc_name
	FROM pg_catalog.pg_namespace, pg_catalog.pg_proc
	WHERE
		pg_proc.oid = proc AND
		pg_proc.pronamespace = pg_namespace.oid;

	DELETE FROM pgtle.feature_info
	WHERE
		feature_info.feature = $2 AND
		feature_info.schema_name = proc_schema_name AND
		feature_info.proname = proc_name;

	GET DIAGNOSTICS row_count = ROW_COUNT;

	IF ROW_COUNT = 0 THEN
    RAISE EXCEPTION 'Could not unregister "%": does not exist.', $1 USING ERRCODE = 'no_data_found';
  END IF;
END;
$$;

-- Helper to softly fail if we try to unregister a function that does not exist
CREATE FUNCTION pgtle.unregister_feature_if_exists(proc regproc, feature pgtle.pg_tle_features)
RETURNS bool
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pgtle.unregister_feature(proc, feature);
  RETURN TRUE;
EXCEPTION
  -- only catch the error that no data was found
  WHEN no_data_found THEN
    RETURN FALSE;
END;
$$;

-- Revoke privs from PUBLIC
REVOKE EXECUTE ON FUNCTION pgtle.register_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION pgtle.register_feature_if_not_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION pgtle.unregister_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION pgtle.unregister_feature_if_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

-- Grant privs to pgtle_admin
GRANT EXECUTE ON FUNCTION pgtle.register_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

GRANT EXECUTE ON FUNCTION pgtle.register_feature_if_not_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

GRANT EXECUTE ON FUNCTION pgtle.unregister_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

GRANT EXECUTE ON FUNCTION pgtle.unregister_feature_if_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

-- Prevent function from being dropped if referenced in table
CREATE FUNCTION pgtle.pg_tle_feature_info_sql_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
obj RECORD;
num_rows int;

BEGIN
	FOR obj IN SELECT * FROM pg_catalog.pg_event_trigger_dropped_objects()

	LOOP
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



pg_tle--1.1.1.sql [585:776]:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
) TO pgtle_admin;

CREATE TYPE pgtle.pg_tle_features as ENUM ('passcheck');
CREATE TYPE pgtle.password_types as ENUM ('PASSWORD_TYPE_PLAINTEXT', 'PASSWORD_TYPE_MD5', 'PASSWORD_TYPE_SCRAM_SHA_256');

CREATE TABLE pgtle.feature_info(
	feature pgtle.pg_tle_features,
	schema_name text,
	proname text,
	obj_identity text NOT NULL,
  PRIMARY KEY(feature, schema_name, proname));

SELECT pg_catalog.pg_extension_config_dump('pgtle.feature_info', '');

GRANT SELECT on pgtle.feature_info TO PUBLIC;

-- Helper function to register features in the feature_info table
CREATE FUNCTION pgtle.register_feature(proc regproc, feature pgtle.pg_tle_features)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
pg_proc_relid oid;
proc_oid oid;
schema_name text;
nspoid oid;
proname text;
proc_schema_name text;
ident text;

BEGIN
	SELECT oid into nspoid FROM pg_catalog.pg_namespace
	where nspname = 'pg_catalog';

	SELECT oid into pg_proc_relid from pg_catalog.pg_class
	where relname = 'pg_proc' and relnamespace = nspoid;

	SELECT pg_namespace.nspname, pg_proc.oid, pg_proc.proname into proc_schema_name, proc_oid, proname FROM
	pg_catalog.pg_namespace, pg_catalog.pg_proc
	where pg_proc.oid = proc AND pg_proc.pronamespace = pg_namespace.oid;

	SELECT identity into ident FROM pg_catalog.pg_identify_object(pg_proc_relid, proc_oid, 0);

	INSERT INTO pgtle.feature_info VALUES (feature, proc_schema_name, proname, ident);
END;
$$;

-- Helper function to softly fail if we try to register a function that already exists
CREATE FUNCTION pgtle.register_feature_if_not_exists(proc regproc, feature pgtle.pg_tle_features)
RETURNS bool
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pgtle.register_feature(proc, feature);
  RETURN TRUE;
EXCEPTION
  -- only catch the unique violation. let all other exceptions pass through.
  WHEN unique_violation THEN
    RETURN FALSE;
END;
$$;

-- Helper function to delete from table
CREATE FUNCTION pgtle.unregister_feature(proc regproc, feature pgtle.pg_tle_features)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
	pg_proc_relid oid;
	proc_oid oid;
	schema_name text;
	nspoid oid;
	proc_name text;
	proc_schema_name text;
	ident text;
	row_count bigint;
BEGIN
	SELECT oid into nspoid
  FROM pg_catalog.pg_namespace
	WHERE nspname = 'pg_catalog';

	SELECT oid into pg_proc_relid
  FROM pg_catalog.pg_class
	WHERE
		relname = 'pg_proc' AND
		relnamespace = nspoid;

	SELECT
		pg_namespace.nspname,
		pg_proc.oid,
		pg_proc.proname
  INTO
		proc_schema_name,
		proc_oid,
		proc_name
	FROM pg_catalog.pg_namespace, pg_catalog.pg_proc
	WHERE
		pg_proc.oid = proc AND
		pg_proc.pronamespace = pg_namespace.oid;

	DELETE FROM pgtle.feature_info
	WHERE
		feature_info.feature = $2 AND
		feature_info.schema_name = proc_schema_name AND
		feature_info.proname = proc_name;

	GET DIAGNOSTICS row_count = ROW_COUNT;

	IF ROW_COUNT = 0 THEN
    RAISE EXCEPTION 'Could not unregister "%": does not exist.', $1 USING ERRCODE = 'no_data_found';
  END IF;
END;
$$;

-- Helper to softly fail if we try to unregister a function that does not exist
CREATE FUNCTION pgtle.unregister_feature_if_exists(proc regproc, feature pgtle.pg_tle_features)
RETURNS bool
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pgtle.unregister_feature(proc, feature);
  RETURN TRUE;
EXCEPTION
  -- only catch the error that no data was found
  WHEN no_data_found THEN
    RETURN FALSE;
END;
$$;

-- Revoke privs from PUBLIC
REVOKE EXECUTE ON FUNCTION pgtle.register_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION pgtle.register_feature_if_not_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION pgtle.unregister_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

REVOKE EXECUTE ON FUNCTION pgtle.unregister_feature_if_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) FROM PUBLIC;

-- Grant privs to pgtle_admin
GRANT EXECUTE ON FUNCTION pgtle.register_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

GRANT EXECUTE ON FUNCTION pgtle.register_feature_if_not_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

GRANT EXECUTE ON FUNCTION pgtle.unregister_feature
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

GRANT EXECUTE ON FUNCTION pgtle.unregister_feature_if_exists
(
  proc regproc,
  feature pgtle.pg_tle_features
) TO pgtle_admin;

-- Prevent function from being dropped if referenced in table
CREATE FUNCTION pgtle.pg_tle_feature_info_sql_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
obj RECORD;
num_rows int;

BEGIN
	FOR obj IN SELECT * FROM pg_catalog.pg_event_trigger_dropped_objects()

	LOOP
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -



