SET LOCAL client_min_messages = WARNING;

\echo This extension must be loaded via 'CREATE EXTENSION object_reference;'

\echo You really, REALLY do NOT want to try and load this via psql!!!

\echo It will FAIL during pg_dump! \quit

-- This BS is because count_nulls is relocatable, so could be in any schema

DO $$

BEGIN

RAISE DEBUG 'initial search_path = %', current_setting('search_path');

PERFORM set_config('search_path', current_setting('search_path') || ', ' || extnamespace::regnamespace::text, true) -- true = local only

FROM pg_extension

WHERE extname = 'count_nulls'

;

RAISE DEBUG 'search_path changed to %', current_setting('search_path');

END

$$;

/*

DO $$

DECLARE

c_schema CONSTANT name := (SELECT extnamespace::regnamespace::text FROM pg_extension WHERE extname = 'cat_tools');

BEGIN

IF c_schema IS NULL THEN

RAISE 'extension cat_tools is not installed';

END IF;

IF c_schema <> 'cat_tools' THEN

RAISE 'having the cat_tools extension installed anywhere but the "cat_tools" schema is not currently supported'

USING DETAIL = format('current schema for cat_tools is %s', c_schema)

;

END IF;

END

$$;

*/

DO $$

BEGIN

CREATE ROLE object_reference__usage NOLOGIN;

EXCEPTION WHEN duplicate_object THEN

NULL;

END

$$;

DO $$

BEGIN

CREATE ROLE object_reference__dependency NOLOGIN;

EXCEPTION WHEN duplicate_object THEN

NULL;

END

$$;

/*

* 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 __object_reference;

CREATE FUNCTION __object_reference.exec(

sql text

) RETURNS void LANGUAGE plpgsql AS $body$

BEGIN

RAISE DEBUG 'sql = %', sql;

EXECUTE sql;

END

$body$;

CREATE FUNCTION __object_reference.safe_dump(

relation regclass

, filter text DEFAULT ''

) RETURNS void LANGUAGE plpgsql AS $body$

BEGIN

PERFORM pg_catalog.pg_extension_config_dump(relation, filter);

EXCEPTION WHEN feature_not_supported THEN

RAISE WARNING 'I promise you will be sorry if you try to use this as anything other than an extension!';

END

$body$;

CREATE FUNCTION __object_reference.create_function(

function_name text

, args text

, options text

, body text

, comment text

, grants text DEFAULT NULL

) RETURNS void LANGUAGE plpgsql AS $body$

DECLARE

c_clean_args 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 __object_reference.exec( format(

create_template

, function_name

, args

, options -- TODO: Force search_path if options ~* 'definer'

, body

) )

;

PERFORM __object_reference.exec( format(

revoke_template

, function_name

, c_clean_args

) )

;

IF grants IS NOT NULL THEN

PERFORM __object_reference.exec( format(

grant_template

, function_name

, c_clean_args

, grants

) )

;

END IF;

IF comment IS NOT NULL THEN

PERFORM __object_reference.exec( format(

comment_template

, function_name

, c_clean_args

, comment

) )

;

END IF;

END

$body$;

-- Schema already created via CREATE EXTENSION

GRANT USAGE ON SCHEMA object_reference TO object_reference__usage;

CREATE SCHEMA _object_reference;

GRANT USAGE ON SCHEMA _object_reference TO object_reference__dependency;

SELECT __object_reference.create_function(

'_object_reference.exec'

, 'sql text'

, 'void LANGUAGE plpgsql'

, $body$

BEGIN

RAISE DEBUG 'sql = %', sql;

EXECUTE sql;

END

$body$

, 'Execute arbitrary SQL with logging.'

);

CREATE TABLE _object_reference.object(

object_id serial PRIMARY KEY

, object_type cat_tools.object_type NOT NULL

-- , original_name text NOT NULL

, object_names text[] NOT NULL

, object_args text[] NOT NULL

, CONSTRAINT object__u_object_names__object_args UNIQUE( object_type, object_names, object_args )

/* TODO: this can't be a trigger because some objects won't exist when a dump is loaded

, CONSTRAINT object__address_sanity

-- pg_get_object_address will throw an error if anything is wrong, so the IS NOT NULL is mostly pointless

CHECK( pg_catalog.pg_get_object_address(object_type::text, object_names, object_args) IS NOT NULL )

*/

);

SELECT __object_reference.safe_dump('_object_reference.object');

SELECT __object_reference.safe_dump('_object_reference.object_object_id_seq');

GRANT REFERENCES ON _object_reference.object TO object_reference__dependency;

CREATE TABLE _object_reference._object_oid(

object_id int PRIMARY KEY REFERENCES _object_reference.object ON DELETE CASCADE ON UPDATE CASCADE

, classid regclass NOT NULL

/* TODO: needs to be a trigger

CONSTRAINT classid_must_match__object__address_classid

CHECK( classid IS NOT DISTINCT FROM cat_tools.object__address_classid(object_type) )

*/

, objid oid NOT NULL

, objsubid int NOT NULL

CONSTRAINT objid_must_match CHECK( -- _object_reference._sanity() depends on this!

objid IS NOT DISTINCT FROM coalesce(

regclass::oid -- Need to cast first item to generic OID

, regconfig

, regdictionary

, regnamespace -- SED: REQUIRES 9.5!

, regoperator

, regprocedure

, regtype

, object_oid

)

)

, CONSTRAINT object__u_classid__objid__objsubid UNIQUE( classid, objid, objsubid )

, regclass regclass

CONSTRAINT regclass_classid CHECK( regclass IS NULL OR classid = cat_tools.object__reg_type_catalog('regclass') )

, regconfig regconfig

CONSTRAINT regconfig_classid CHECK( regconfig IS NULL OR classid = cat_tools.object__reg_type_catalog('regconfig') )

, regdictionary regdictionary

CONSTRAINT regdictionary_classid CHECK( regdictionary IS NULL OR classid = cat_tools.object__reg_type_catalog('regdictionary') )

, regnamespace regnamespace -- SED: REQUIRES 9.5!

CONSTRAINT regnamespace_classid CHECK( regnamespace IS NULL OR classid = cat_tools.object__reg_type_catalog('regnamespace') ) -- SED: REQUIRES 9.5!

, regoperator regoperator

CONSTRAINT regoperator_classid CHECK( regoperator IS NULL OR classid = cat_tools.object__reg_type_catalog('regoperator') )

, regprocedure regprocedure

CONSTRAINT regprocedure_classid CHECK( regprocedure IS NULL OR classid = cat_tools.object__reg_type_catalog('regprocedure') )

-- I don't think we should ever have regrole since we can't create event triggers on it

-- , regrole regrole

, regtype regtype

CONSTRAINT regtype_classid CHECK( regtype IS NULL OR classid = cat_tools.object__reg_type_catalog('regtype') )

, object_oid oid

);

CREATE TRIGGER null_count

AFTER INSERT OR UPDATE

ON _object_reference._object_oid

FOR EACH ROW EXECUTE PROCEDURE not_null_count_trigger(

5 -- First 4 fields, + 1

, 'only one object reference field may be set'

)

;

CREATE UNIQUE INDEX _object_oid__u_regclass ON _object_reference._object_oid(regclass) WHERE regclass IS NOT NULL;

CREATE UNIQUE INDEX _object_oid__u_regconfig ON _object_reference._object_oid(regconfig) WHERE regconfig IS NOT NULL;

CREATE UNIQUE INDEX _object_oid__u_regdictionary ON _object_reference._object_oid(regdictionary) WHERE regdictionary IS NOT NULL;

CREATE UNIQUE INDEX _object_oid__u_regoperator ON _object_reference._object_oid(regoperator) WHERE regoperator IS NOT NULL;

CREATE UNIQUE INDEX _object_oid__u_regprocedure ON _object_reference._object_oid(regprocedure) WHERE regprocedure IS NOT NULL;

CREATE UNIQUE INDEX _object_oid__u_regtype ON _object_reference._object_oid(regtype) WHERE regtype IS NOT NULL;

SELECT __object_reference.create_function(

'_object_reference._sanity'

, $args$

obj _object_reference.object

, id _object_reference._object_oid

, OUT names_ok boolean

, OUT ids_ok boolean

, OUT ids_exist boolean

$args$

, 'RECORD LANGUAGE plpgsql STABLE'

, $body$

DECLARE

r record;

BEGIN

ASSERT NOT obj IS NULL, 'obj may not be null';

ASSERT id IS NULL OR obj.object_id = id.object_id, 'id must be null or object_ids must match';

ids_exist := NOT (id IS NULL); -- Remember this is NOT the same as id IS NOT NULL!

BEGIN

r := pg_catalog.pg_get_object_address(obj.object_type::text, obj.object_names, obj.object_args);

names_ok := true;

-- Assume that if get_object_address worked then the names are at least valid

ids_ok := r IS NOT DISTINCT FROM (id.classid::oid, id.objid, id.objsubid);

EXCEPTION

WHEN others THEN

IF

SQLSTATE IN(

'22023' -- invalid_parameter_value

, '3F000' -- invalid_schema_name

, '42703' -- undefined_column

, '42704' -- undefined_object

, '42883' -- undefined_function

)

OR SQLSTATE LIKE '42P%' -- Matches a bunch of codes, including undefined_* and invalid_*_definition

THEN

names_ok := false;

ids_ok := false; -- Should we see if pg_object_identity_as_address works??

ELSE

RAISE WARNING 'Unexpected error!!';

RAISE; -- Unexpected, so re-raise

END IF;

END;

END

$body$

, 'Check the sanity of object and _object_oid'

);

CREATE VIEW _object_reference._object_v AS

SELECT

o.object_id

, o.object_type

, o.object_names

, o.object_args

, i.classid

, i.objid

, i.objsubid

, i.regclass

, i.regconfig

, i.regdictionary

, i.regnamespace

, i.regoperator

, i.regprocedure

, i.regtype

, i.object_oid

, s.*

FROM _object_reference.object o

LEFT JOIN _object_reference._object_oid i USING(object_id)

, _object_reference._sanity(o, i) s

;

CREATE VIEW _object_reference._object_v__for_update AS

SELECT

o.object_id

, o.object_type

, o.object_names

, o.object_args

, i.classid

, i.objid

, i.objsubid

, i.regclass

, i.regconfig

, i.regdictionary

, i.regnamespace

, i.regoperator

, i.regprocedure

, i.regtype

, i.object_oid

, s.*

FROM _object_reference.object o

LEFT JOIN _object_reference._object_oid i USING(object_id)

, _object_reference._sanity(o, i) s

FOR UPDATE OF o

;

SELECT __object_reference.create_function(

'_object_reference._object_oid__add'

, $args$

object_id _object_reference._object_oid.object_id%TYPE

, object_type _object_reference.object.object_type%TYPE DEFAULT NULL

, classid _object_reference._object_oid.classid%TYPE DEFAULT NULL

, objid _object_reference._object_oid.objid%TYPE DEFAULT NULL

, objsubid _object_reference._object_oid.objsubid%TYPE DEFAULT NULL

$args$

, '_object_reference._object_v LANGUAGE plpgsql'

, $body$

DECLARE

r_object_v _object_reference._object_v;

BEGIN

IF object_type IS NULL THEN

-- Should definitely exist

SELECT INTO STRICT object_type, classid, objid, objsubid

o.object_type, a.classid, a.objid, a.objsubid

FROM _object_reference.object o

, pg_catalog.pg_get_object_address(o.object_type::text, o.object_names, o.object_args) a

WHERE o.object_id = _object_oid__add.object_id

;

END IF;

DECLARE

c_reg_type name := cat_tools.object__reg_type(object_type); -- Verifies regtype is supported, if there is one

c_oid_field CONSTANT name := coalesce(c_reg_type, 'object_oid');

c_oid_insert CONSTANT text := format(

--USING object_id, classid, objid, objsubid

$$INSERT INTO _object_reference._object_oid(object_id, classid, objid, objsubid, %I)

SELECT $1, $2, $3, $4, $3::%I$$

, c_oid_field

, coalesce(c_reg_type, 'oid')

)

;

BEGIN

RAISE DEBUG E'%\n USING %, %, %, %'

, c_oid_insert

, object_id, classid, objid, objsubid

;

EXECUTE c_oid_insert

USING object_id, classid, objid, objsubid

;

SELECT INTO STRICT r_object_v -- Record better exist!

*

FROM _object_reference._object_v__for_update o

WHERE o.object_id = _object_oid__add.object_id

;

END;

IF NOT r_object_v.ids_ok THEN

RAISE 'id mismatch for object_id %', object_id

USING

DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)

, HINT = 'this should not be possible'

;

END IF;

RETURN r_object_v;

END

$body$

, 'Check the sanity of object and _object_oid'

);

/*

* fix_refs / post_restore

*/

SELECT __object_reference.create_function(

'_object_reference.fix_refs'

, 'warning_only boolean'

, 'void LANGUAGE plpgsql'

, $body$

DECLARE

r_object_v _object_reference._object_v;

BEGIN

FOR r_object_v IN

SELECT * FROM _object_reference._object_v

LOOP

CASE

WHEN r_object_v.names_ok AND r_object_v.ids_ok THEN

NULL; -- All good!

WHEN NOT r_object_v.names_ok THEN

IF r_object_v.ids_exist THEN

-- Only happens if things are out of sync, so intentionally treat this as an error

RAISE 'names/args are out of sync on object_id %', r_object_v.object_id

USING

DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)

, HINT = CASE WHEN r_object_v.ids_ok THEN

E'The IDs are OK though. This should not happen, but may be fixable.\n'

|| 'Sanity-check the record and if OK then UPDATE _object_identity.object.'

ELSE

'There is also a record in _object_identity._object_oid with invalid IDs. This should never happen.'

END

;

ELSE

IF warning_only THEN

RAISE WARNING 'names not ok for object_id %', r_object_v.object_id

USING DETAIL = format(

'pg_catalog.pg_get_object_address(%L, %L, %L)'

, r_object_v.object_type

, r_object_v.object_names

, r_object_v.object_args

)

;

ELSE

RAISE 'names not ok for object_id %', r_object_v.object_id

USING DETAIL = format(

'pg_catalog.pg_get_object_address(%L, %L, %L)'

, r_object_v.object_type

, r_object_v.object_names

, r_object_v.object_args

)

;

END IF;

END IF;

-- at this point, names are OK but ids are not (or don't exist)

WHEN NOT r_object_v.ids_exist THEN

IF warning_only THEN

-- This is a normal condition during a restore, so just fix it

PERFORM _object_reference._object_oid__add(r_object_v.object_id);

ELSE

RAISE 'no record in _object_reference._object_oid for object_id %', r_object_v.object_id

USING

DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)

, HINT = 'It should be safe to fix this by calling _object_reference.fix_refs()'

;

END IF;

WHEN r_object_v.ids_exist THEN

IF warning_only THEN

RAISE WARNING 'extraneous ID information for object_id %', r_object.object_id

USING

DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)

, HINT = E'The names are OK though. This should not happen, but may be fixable.\n'

|| 'Sanity-check the record and if OK then UPDATE _object_identity._object_v.'

;

ELSE

RAISE 'extraneous ID information for object_id %', r_object.object_id

USING

DETAIL = '_object_reference._object_v = ' || pg_catalog.row_to_json(r_object_v)

, HINT = E'The names are OK though. This should not happen, but may be fixable.\n'

|| 'Sanity-check the record and if OK then UPDATE _object_identity._object_v.'

;

END IF;

END CASE;

END LOOP;

END

$body$

, 'Fixes records in _object_reference._object_oid after a restore.'

);

SELECT __object_reference.create_function(

'object_reference.post_restore'

, ''

, 'void SECURITY DEFINER LANGUAGE sql'

, 'SELECT _object_reference.fix_refs(false)'

, 'Ensures all object references are correct after a restore.'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'_object_reference._repair'

, ''

, 'bigint SECURITY DEFINER LANGUAGE sql'

, 'SELECT count(*) AS objects FROM _object_reference.object, _object_reference._object_oid__add(object_id)'

, 'Ensures all object references are correct after a restore.'

, 'object_reference__usage'

);

CREATE MATERIALIZED VIEW _object_reference._sentry_mv AS SELECT _object_reference._repair();

SELECT __object_reference.safe_dump('_object_reference._sentry_mv');

/*

* Unsupported object types

*/

SELECT __object_reference.create_function(

'object_reference.unsupported'

, ''

, 'cat_tools.object_type[] LANGUAGE sql IMMUTABLE'

, $body$

SELECT cat_tools.objects__shared()

|| cat_tools.objects__address_unsupported()

|| '{event trigger}'

$body$

, 'Returns array of object types that are not supported.'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.unsupported_srf'

, ''

, 'SETOF cat_tools.object_type LANGUAGE sql IMMUTABLE'

, $body$

SELECT * FROM unnest(object_reference.unsupported())

$body$

, 'Returns set of object types that are not supported.'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.unsupported'

, 'object_type cat_tools.object_type'

, 'boolean LANGUAGE sql IMMUTABLE'

, $body$

SELECT object_type = ANY(object_reference.unsupported())

$body$

, 'Is a object_type unsupported?'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.unsupported'

, 'object_type text'

, 'boolean LANGUAGE sql IMMUTABLE'

, $body$

SELECT object_reference.unsupported(object_type::cat_tools.object_type)

$body$

, 'Is a object_type unsupported?'

, 'object_reference__usage'

);

/*

* Untested object types

*/

SELECT __object_reference.create_function(

'object_reference.untested'

, ''

, 'cat_tools.object_type[] LANGUAGE sql IMMUTABLE'

, $body$

SELECT '{

foreign table, foreign table column, aggregate, collation, conversion, language,

large object, operator, operator class, operator family, operator of access method,

function of access method, rule, text search parser, text search dictionary,

text search template, text search configuration, foreign-data wrapper, server,

user mapping, default acl, transform, access method, extension, policy

}'::cat_tools.object_type[]

$body$

, 'Returns array of object types that have not been tested.'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.untested_srf'

, ''

, 'SETOF cat_tools.object_type LANGUAGE sql IMMUTABLE'

, $body$

SELECT * FROM unnest(object_reference.untested())

$body$

, 'Returns set of object types that have not been tested.'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.untested'

, 'object_type cat_tools.object_type'

, 'boolean LANGUAGE sql IMMUTABLE'

, $body$

SELECT object_type = ANY(object_reference.untested())

$body$

, 'Is a object_type untested?'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.untested'

, 'object_type text'

, 'boolean LANGUAGE sql IMMUTABLE'

, $body$

SELECT object_reference.untested(object_type::cat_tools.object_type)

$body$

, 'Is a object_type untested?'

, 'object_reference__usage'

);

/*

* OBJECT GROUP

*/

CREATE TABLE _object_reference.object_group(

object_group_id serial PRIMARY KEY

, object_group_name varchar(200) NOT NULL

);

CREATE UNIQUE INDEX object_group__u_object_group_name__lower ON _object_reference.object_group(lower(object_group_name));

SELECT __object_reference.safe_dump('_object_reference.object_group');

CREATE TABLE _object_reference.object_group__object(

object_group_id int NOT NULL REFERENCES _object_reference.object_group

, object_id int NOT NULL REFERENCES _object_reference.object

, CONSTRAINT object_group__object__u_object_group_id__object_id UNIQUE( object_group_id, object_id )

);

SELECT __object_reference.safe_dump('_object_reference.object_group__object');

-- __get

SELECT __object_reference.create_function(

'object_reference.object_group__get'

, $args$

object_group_name _object_reference.object_group.object_group_name%TYPE

$args$

, '_object_reference.object_group LANGUAGE plpgsql STABLE'

, $body$

DECLARE

r _object_reference.object_group;

BEGIN

SELECT INTO STRICT r

*

FROM _object_reference.object_group ogo

WHERE lower(ogo.object_group_name) = lower(object_group__get.object_group_name)

;

RETURN r;

EXCEPTION WHEN no_data_found THEN

RAISE 'object group "%" does not exist', object_group_name

USING ERRCODE = 'no_data_found'

;

END

$body$

, 'Get details about the specified object group'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.object_group__get'

, $args$

object_group_id _object_reference.object_group.object_group_id%TYPE

$args$

, '_object_reference.object_group LANGUAGE plpgsql STABLE'

, $body$

DECLARE

r _object_reference.object_group;

BEGIN

SELECT INTO STRICT r

*

FROM _object_reference.object_group ogo

WHERE (ogo.object_group_id) = (object_group__get.object_group_id)

;

RETURN r;

EXCEPTION WHEN no_data_found THEN

RAISE 'object group id % does not exist', object_group_id

USING ERRCODE = 'no_data_found'

;

END

$body$

, 'Get details about the specified object group'

, 'object_reference__usage'

);

-- __create

SELECT __object_reference.create_function(

'object_reference.object_group__create'

, $args$

object_group_name _object_reference.object_group.object_group_name%TYPE

$args$

, 'int LANGUAGE sql'

, $body$

INSERT INTO _object_reference.object_group(object_group_name) VALUES(object_group_name)

RETURNING object_group_id

$body$

, 'Create a new object group.'

, 'object_reference__usage'

);

-- __remove

SELECT __object_reference.create_function(

'object_reference.object_group__remove'

, $args$

object_group_id _object_reference.object_group.object_group_id%TYPE

, force boolean DEFAULT false

$args$

, 'void LANGUAGE plpgsql'

, $body$

DECLARE

-- This is to ensure group exists

c_object_group_id CONSTANT int := (object_reference.object_group__get($1)).object_group_id;

BEGIN

IF force IS TRUE THEN

DELETE FROM _object_reference.object_group__object

WHERE object_group__object.object_group_id = c_object_group_id

;

END IF;

DELETE FROM _object_reference.object_group

WHERE object_group.object_group_id = c_object_group_id

;

END

$body$

, 'Remove a object group. If force is true, remove group even if it still references objects.'

, 'object_reference__usage'

);

SELECT __object_reference.create_function(

'object_reference.object_group__remove'

, $args$

object_group_name _object_reference.object_group.object_group_name%TYPE

, force boolean DEFAULT false

$args$

, 'void LANGUAGE sql'

, $body$

SELECT object_reference.object_group__remove(

(object_reference.object_group__get($1)).object_group_id

, $2

);

$body$

, 'Remove a object group. If force is true, remove group even if it still references objects.'

, 'object_reference__usage'

);

-- __object__add

SELECT __object_reference.create_function(

'object_reference.object_group__object__add'

, $args$

object_group_id _object_reference.object_group__object.object_group_id%TYPE

, object_id _object_reference.object_group__object.object_id%TYPE

$args$

, 'void LANGUAGE sql'

, $body$

INSERT INTO _object_reference.object_group__object AS ogo(object_group_id, object_id)

VALUES($1, $2)

ON CONFLICT (object_group_id, object_id) DO NOTHING

$body$

, 'Add a object_id to a object group.'

, 'object_reference__usage'

);

-- __object__remove

SELECT __object_reference.create_function(

'object_reference.object_group__object__remove'

, $args$

object_group_id _object_reference.object_group__object.object_group_id%TYPE

, object_id _object_reference.object_group__object.object_id%TYPE

$args$

, 'void LANGUAGE plpgsql'

, $body$

BEGIN

DELETE FROM _object_reference.object_group__object AS ogo

WHERE

(

ogo.object_group_id

, ogo.object_id

) = (

-- This is to ensure group exists

(object_reference.object_group__get($1)).object_group_id

, object_group__object__remove.object_id

)

;

IF NOT FOUND THEN

-- We know group exists, so issue must be that object doesn't exist

RAISE 'object id % does not exist', object_id

USING ERRCODE = 'no_data_found'

;

END IF;

END

$body$

, 'Remove a object_id from a object group.'

, 'object_reference__usage'

);

/*

* REFERENCES

*/

SELECT __object_reference.create_function(

'object_reference.object_group__dependency__add'

, $args$

table_name text

, field_name name

$args$

, 'void LANGUAGE plpgsql'

, $body$

DECLARE

-- Do this to sanitize input

o_table CONSTANT regclass := table_name;

BEGIN

PERFORM _object_reference.exec( format( 'ALTER TABLE %s ADD FOREIGN KEY( %I ) REFERENCES _object_reference.object_group', table_name, field_name ) );

END

$body$

, 'Add a foreign key from <table_name>.<field_name> to the object_group table.'

, 'object_reference__dependency'

);

-- s/object_group/object/

SELECT __object_reference.create_function(

'object_reference.object__dependency__add'

, $args$

table_name text

, field_name name

$args$

, 'void LANGUAGE plpgsql'

, $body$

DECLARE

-- Do this to sanitize input

o_table CONSTANT regclass := table_name;

BEGIN

PERFORM _object_reference.exec( format( 'ALTER TABLE %s ADD FOREIGN KEY( %I ) REFERENCES _object_reference.object', table_name, field_name ) );

END

$body$

, 'Add a foreign key from <table_name>.<field_name> to the object table.'

, 'object_reference__dependency'

);

/*

* OBJECT GETSERT

*/

SELECT __object_reference.create_function(

'_object_reference._object_v__for_update'

, $args$

object_type _object_reference.object.object_type%TYPE

, objid _object_reference._object_oid.objid%TYPE

, objsubid _object_reference._object_oid.objsubid%TYPE

, object_group_id int DEFAULT NULL

, class_id regclass DEFAULT NULL

$args$

, '_object_reference._object_v LANGUAGE plpgsql'

, $body$

DECLARE

c_classid CONSTANT regclass := cat_tools.object__address_classid(object_type);

r_object_v _object_reference._object_v;

r_address record;

did_insert boolean := false;

i smallint;

sql text;

BEGIN

ASSERT class_id IS NULL OR class_id = c_classid, format(

'cat_tools.object__address_classid(object_type) %L <> class_id %L'

, c_classid

, class_id

);

IF object_reference.unsupported(object_type) THEN

RAISE 'object_type % is not supported', object_type;

END IF;

SELECT INTO r_address * FROM pg_catalog.pg_identify_object_as_address(c_classid, objid, objsubid);

IF r_address IS NULL THEN

RAISE 'unable to find object'

USING DETAIL = format(

'pg_identify_object_as_address(%s, %s, %s) returned NULL'

, c_classid

, objid

, objsubid

)

;

END IF;

-- Ensure the object record exists

SELECT INTO r_object_v

*

FROM _object_reference._object_v__for_update o

WHERE (o.object_type, o.object_names, o.object_args) = (_object_v__for_update.object_type, r_address.object_names, r_address.object_args)

;

IF NOT FOUND THEN

FOR i IN 1..10 LOOP

did_insert := true;

INSERT INTO _object_reference.object(object_type, object_names, object_args)

VALUES(_object_v__for_update.object_type, r_address.object_names, r_address.object_args)

ON CONFLICT ON CONSTRAINT object__u_object_names__object_args DO NOTHING

;

-- Still a small race condition here...

SELECT INTO r_object_v

*

FROM _object_reference._object_v__for_update o

WHERE (o.object_type, o.object_names, o.object_args) = (_object_v__for_update.object_type, r_address.object_names, r_address.object_args)

;

EXIT WHEN FOUND;

END LOOP;

IF NOT FOUND THEN

RAISE 'fell out of loop!' USING HINT = 'This should never happen.';

END IF;

END IF;

ASSERT r_object_v.names_ok, 'names do not match (should not be possible)' ;

IF object_group_id IS NOT NULL THEN

PERFORM object_reference.object_group__object__add(object_group_id, r_object_v.object_id);

END IF;

-- Handle _object_oid table

CASE

WHEN r_object_v.ids_ok THEN

RETURN r_object_v;

WHEN NOT r_object_v.ids_exist THEN

/*

* Just need to create IDs record.

*/

/*

* This shouldn't normally happen, but could occur if a restore didn't

* finish cleanly. We know it's safe to do this because names_ok is true.

*/

IF NOT did_insert THEN

RAISE WARNING 'missing record in _object_reference._object_oid for object_id %', r_object_v.object_id

USING HINT = 'This indicates a restore did not finish cleanly.'

;

END IF;

r_object_v := _object_reference._object_oid__add(r_object_v.object_id, object_type, c_classid, objid, objsubid);

WHEN r_object_v.ids_exist THEN

RAISE 'ids are out of sync for object_id %', r_object_v.object_id

USING DETAIL = format(

E'_object_reference._object_v = %L,\n arguments (%L, %s, %s, %s)'

, pg_catalog.row_to_json(r_object_v, true)

, object_type

, objid

, objsubid

, object_group_id

)

, HINT = 'this shoud not happen if event trigger "zzz_object_reference_end" is working'

;

ELSE

RAISE 'unknown condition';

END CASE;

RETURN r_object_v;

END

$body$

, 'Return details of a object record, creating a new record if one does not exist.'

);

SELECT __object_reference.create_function(

'object_reference.object__getsert_w_group_id'

, $args$

object_type cat_tools.object_type

, object_name text

, secondary text DEFAULT NULL

, object_group_id int DEFAULT NULL

, loose boolean DEFAULT false

$args$

, 'int LANGUAGE plpgsql'

, $body$

DECLARE

c_catalog CONSTANT regclass := cat_tools.object__catalog(object_type);

c_loose CONSTANT boolean := coalesce(loose, false);

v_objid oid;

v_subid int := 0;

BEGIN

RAISE DEBUG '% "%" (secondary %) uses catalog %', object_type, object_name, secondary, c_catalog;

-- Some catalogs need special handling

CASE c_catalog

-- Functions

WHEN 'pg_catalog.pg_proc'::regclass THEN

/*

* Need to handle functions specially to support all the extra options they

* can have that regprocedure doesn't support.

*/

-- TODO: allow this to parse object_name directly

BEGIN

v_objid := cat_tools.regprocedure(object_name, secondary);

EXCEPTION WHEN undefined_function THEN

IF c_loose THEN

RETURN NULL;

END IF;

RAISE;

END;

secondary = NULL;

-- Columns

WHEN 'pg_catalog.pg_attribute'::regclass THEN

v_objid := object_name::regclass;

BEGIN

-- Will throw error if column isn't valid

v_subid := (cat_tools.pg_attribute__get(v_objid, secondary)).attnum;