b0VIM 8.0XZk iAdecibeldecina.local~decibel/git/object_reference/sql/object_reference.sqlutf-8 3210#"! Utp J)KWFj   ad$hJ~5rn$  `  ] R ( "  v U T (  / )  i R @ 8 / d1heJ.@HgHZ(0seco CHECK( pg_catalog.pg_get_object_address(object_type::text, object_names, object_args) IS NOT NULL ) -- pg_get_object_address will throw an error if anything is wrong, so the IS NOT NULL is mostly pointless , CONSTRAINT object__address_sanity , CONSTRAINT object__u_object_names__object_args UNIQUE( object_type, object_names, object_args ) , object_args text[] NOT NULL , object_names text[] NOT NULL , object_oid oid , regtype regtype-- , regrole regrole -- SED: REQUIRES 9.5! -- I don't think we should ever have regrole since we can't create event triggers on it , regprocedure regprocedure , regoperator regoperator-- , regnamespace regnamespace -- SED: REQUIRES 9.5! , regdictionary regdictionary , regconfig regconfig , regclass regclass-- , original_name text NOT NULL , CONSTRAINT object__u_classid__objid__objsubid UNIQUE( classid, objid, objsubid ) , objsubid int NOT NULL ) ) , object_oid oid , regtype regtype-- , regrole regrole -- SED: REQUIRES 9.5! -- I don't think we should ever have regrole since we can't create event triggers on it , regprocedure regprocedure , regoperator regoperator-- , regnamespace regnamespace -- SED: REQUIRES 9.5! , regdictionary regdictionary , regconfig regconfig , regclass regclass CHECK( classid IS NOT DISTINCT FROM cat_tools.object__address_classid(object_type) ) CONSTRAINT classid_must_match__object__address_classid , classid oid NOT NULL , object_type cat_tools.object_type NOT NULL object_id serial PRIMARY KEYCREATE TABLE _object_reference.object( $body$;END RETURN v_reg_type; END IF; RAISE 'object type "%" is not yet supported', object_type; THEN ) , 'regtype' , 'regprocedure' , 'regoperator' , 'regdictionary' , 'regconfig' , 'regclass' NULL -- This is OK IF v_reg_type NOT IN ( v_reg_type := cat_tools.object__reg_type(object_type);BEGIN v_reg_type regtype; -- Set if there is a reg* object identifier type availableDECLAREAS $body$SET search_path FROM CURRENT -- Ensure pg_catalog is in the search_path) RETURNS name LANGUAGE plpgsql IMMUTABLE object_type cat_tools.object_typeCREATE FUNCTION _object_reference.reg_type(CREATE SCHEMA _object_reference;GRANT USAGE ON SCHEMA object_reference TO object_reference__usage;-- Schema already created via CREATE EXTENSION$body$;END EXECUTE sql; RAISE DEBUG 'sql = %', sql;BEGIN) RETURNS void LANGUAGE plpgsql AS $body$ sql textCREATE FUNCTION __object_reference.exec(CREATE SCHEMA __object_reference; */ * "temporary" schema instead. * also drop the extension! Instead of risking problems, create our own * Otherwise the eventual DROP CASCADE of pg_temp when the session ends will * NOTE: All pg_temp objects must be dropped at the end of the script!/*$$;END NULL;EXCEPTION WHEN duplicate_object THEN CREATE ROLE object_reference__usage NOLOGIN;BEGINDO $$$$;END RAISE DEBUG 'search_path changed to %', current_setting('search_path'); ; WHERE extname = 'count_nulls' FROM pg_extension PERFORM set_config('search_path', current_setting('search_path') || ', ' || extnamespace::regnamespace::text, true) -- true = local only RAISE DEBUG 'initial search_path = %', current_setting('search_path');BEGINDO $$-- This BS is because count_nulls is relocatable, so could be in any schemaSET LOCAL client_min_messages = WARNING;ad^FJ! s _ ^ P D >  = +   * u _ L H @ ? Qd\@aQ rH2PO x@ UPDATE _object_reference.obj UPDATE _object_reference.object ; ) , r_address.object_names, r_addres ON (o.classid, o.objid, o.objsubid) = (d.classid, d.objid, d.objsubid) JOIN _object_reference.object o ON (d.refclassid, d.refobjid, d.refobjsubid) = (ddl.classi ON (o.classid, o.objid, o.objsubid) = (d.classid, d.objid, d.objsubid) JOIN _object_reference.object o ON (d.refclassid, d.refobjid, d.refobjsubid) = (ddl.classid, ddl.objid, ddl.objsubid) JOIN pg_catalog.pg_depend d FROM ddl SELECT level + 1, top_object_id, o.*, command_tag, in_extension UNION ALL JOIN _object_reference.object o USING( classid, objid, objsubid ) FROM pg_catalog.pg_event_trigger_ddl_commands() SELECT 1::int AS level, o.obj * items have changed and worry about only those. That won't work b * object_names array can depend on multiple names (ie: a column d * the name of it's table, as well as the name of the schema the tabl * You might think we could simply recurse through pg_depend to hand * but not every name dependency gets enumerated that way. For * columns are not marked as dependent on their ta * columns are not marked as dependent on their table. * but not every name dependency gets enumerated that way. For example, * You might think we could simply recurse through pg_depend to handle this, * the name of it's table, as well as the name of the schema the table is in. * object_names array can depend on multiple names (ie: a column depends on * items have changed and worry about only those. That won't work because an * It's tempting to use pg_event_trigger_ddl_commands() to find exactly what /*BEGIN r_address record; r_ddl record;DECLARE) RETURNS event_trigger LANGUAGE plpgsql AS $body$CREATE FUNCTION _object_reference._etg_fix_identity($body$;END RAISE 'fell out of loop!' USING HINT = 'This should never happen.'; END LOOP; END; NULL; EXCEPTION WHEN unique_violation THEN RETURN r_object; ; USING object_type, classid, objid, objsubid, r_address.object_names, r_address.object_args INTO r_object EXECUTE c_insert BEGIN END IF; RETURN r_object; IF NOT r_object IS NULL THEN r_object := _object_reference.object__get_loose(classid, objid, objsubid); -- TODO: create a smart update function that only updates if data has changed, and always returns relevant data. Necessary to deal with object_* fields possibly changing. FOR i IN 1..10 LOOP END IF; ; ) , objsubid , objid , classid 'pg_identify_object_as_address(%s, %s, %s) returned NULL' USING DETAIL = format( RAISE 'unable to find object' IF r_address IS NULL THEN SELECT INTO r_address * FROM pg_catalog.pg_identify_object_as_address(classid, objid, objsubid);BEGIN sql text; i smallint; r_address record; r_object _object_reference.object; ; ) , coalesce(c_reg_type, 'oid') , c_oid_field RETURNING *$$ SELECT $1, $2, $3, $4, $3::%I, $5, $6 $$INSERT INTO _object_reference.object(object_type, classid, objid, objsubid, %I, object_names, object_args) -- USING object_type, classid, objid, objsubid, object_names, object_args c_insert CONSTANT text := format( c_oid_field CONSTANT name := coalesce(c_reg_type, 'object_oid'); c_reg_type name := _object_reference.reg_type(object_type); -- Verifies regtype is supported, if there is oneDECLARE) RETURNS _object_reference.object LANGUAGE plpgsql AS $body$ad , sv  E D   l 4 , + ` ,   M    | ;   , , secondary int DE , second , se , , sec , , secondary int DEFAULT 0 , object_oid oid obje , secondary int DEFAULT 0 , object , secondary int D , secondary int DEFAULT 0 , secondary int DEFAULT 0 , object_oid oid object_type ca , secondary int DEFAULT 0 , object_oid o , secondary int DEFAULT 0 , object_oid oid o , , , objsubid , ob , objsubid i , objsubid i , objsubid int DE , objsubid _object_reference.object.objsubid%TYPE , objid _object_reference.object.objid%TYPE , classid _object_reference.object.classid%TYPE object_type _object_reference.object.object_type%TYPECREATE FUNCTION _object_reference.object__getsert($body$; WHERE (o.classid, o.objid, o.objsubid) = ($1, $2, $3) FROM _object_reference.object oSELECT *) RETURNS _object_reference.object LANGUAGE sql STABLE AS $body$ , objsubid int DEFAULT 0 , objid oid classid oidCREATE FUNCTION _object_reference.object__get_loose(CREATE UNIQUE INDEX object__u_regtype ON _object_reference.object(regtype) WHERE regtype IS NOT NULL;CREATE UNIQUE INDEX object__u_regprocedure ON _object_reference.object(regprocedure) WHERE regprocedure IS NOT NULL;CREATE UNIQUE INDEX object__u_regoperator ON _object_reference.object(regoperator) WHERE regoperator IS NOT NULL;CREATE UNIQUE INDEX object__u_regdictionary ON _object_reference.object(regdictionary) WHERE regdictionary IS NOT NULL;CREATE UNIQUE INDEX object__u_regconfig ON _object_reference.object(regconfig) WHERE regconfig IS NOT NULL;CREATE UNIQUE INDEX object__u_regclass ON _object_reference.object(regclass) WHERE regclass IS NOT NULL;; ) , 'only one object reference field may be set' 8 -- First 5 fields, identifier field, object_* fields (can't do actual addition here) FOR EACH ROW EXECUTE PROCEDURE not_null_count_trigger( ON _object_reference.object AFTER INSERT OR UPDATECREATE TRIGGER null_count);adN){A3! e J .  ) m u [ n v  */ C */ C */ C */ C */ C */ C */ C */ */ CHECK( pg_catalog.pg_get_object_address(object_type::text, object_names, object_args) IS NOT NULL ) -- pg_get_object_address will throw an error if anything is wrong, so the IS NOT NULL is mostly pointless , CONSTRAINT object__address_sanity , CONSTRAINT object__u_object_names__object_args UNIQUE( object_type, object_names, object_args ) , object_args text[] NOT NULL , object_names text[] NOT NULL , object_oid oid CONSTRAINT regtype_classid CHECK( regtype IS NULL OR classid = cat_tools.object__reg_type_catalog('regtype') ) , regtype regtype-- , regrole regrole -- I don't think we should ever have regrole since we can't create event triggers on it CONSTRAINT regprocedure_classid CHECK( regprocedure IS NULL OR classid = cat_tools.object__reg_type_catalog('regprocedure') ) , regprocedure regprocedure CONSTRAINT regoperator_classid CHECK( regoperator IS NULL OR classid = cat_tools.object__reg_type_catalog('regoperator') ) , regoperator regoperator-- CONSTRAINT regnamespace_classid CHECK( regnamespace IS NULL OR classid = cat_tools.object__reg_type_catalog('regnamespace') ) -- SED: REQUIRES 9.5!-- , regnamespace regnamespace -- SED: REQUIRES 9.5!-- TODO: support this CONSTRAINT regdictionary_classid CHECK( regdictionary IS NULL OR classid = cat_tools.object__reg_type_catalog('regdictionary') ) , regdictionary regdictionary CONSTRAINT regconfig_classid CHECK( regconfig IS NULL OR classid = cat_tools.object__reg_type_catalog('regconfig') ) , regconfig regconfig CONSTRAINT regclass_classid CHECK( regclass IS NULL OR classid = cat_tools.object__reg_type_catalog('regclass') ) , regclass regclass-- , original_name text NOT NULL , CONSTRAINT object__u_classid__objid__objsubid UNIQUE( classid, objid, objsubid ) , objsubid int NOT NULL ) ) , object_oid , regtype , regprocedure , regoperator-- , regnamespace -- SED: REQUIRES 9.5!-- TODO: support this , regdictionary , regconfig regclass::oid -- Need to cast first item to generic OID objid IS NOT DISTINCT FROM coalesce( CONSTRAINT objid_must_match CHECK( , objid oid NOT NULLadW f42rpolPL% { t o ? 3 -- vi: expandtab sw=2 ts=2DROP SCHEMA __obj-- vi: expandtab sw=2 ts=2DROP SCH-- v----------------- vi: expan-- vi: exp-- vi: expandtab s-- vi: -- ------ vi:-- vi-- vi: -- vi: expandtab sw=2 ts=2DROP SC-- vi: expandtab sw=2 ts=2DROP S-- vi: ex-- vi: expandt-- v-- vi: expandtab sw=2 ts=2DROP SCHEMA __object_reference;); sql textDROP FUNCTION __object_reference.exec( */ * Drop "temporary" objects/*; EXECUTE PROCEDURE _object_reference._etg_fix_identity() --WHEN tag IN ( 'ALTER TABLE', 'DROP TABLE' ) -- For debugging ON ddl_command_endCREATE EVENT TRIGGER zzz_object_reference_end; EXECUTE PROCEDURE _object_reference._etg_drop() --WHEN tag IN ( 'ALTER TABLE', 'DROP TABLE' ) -- For debugging ON sql_dropCREATE EVENT TRIGGER zzz__object_reference_drop$body$;END END LOOP;ad0p x=poA Y 5  E   t k K -   , r_address.obj , r_address.objec , r_address.object , r_address.obje , r_address.object_names, r_address.object_args , r_obje , r_address.object_names, r_address.object_args , r_object , r_address.object_names, r_addre , r_address.object_names, r_address.object_args , r_object.object_names, r_object.object_args , r_address.object_names, r_address.object_args , r_object , r_address.object_na , r_address.object_names , r_address.object_names, r_address.object_arg , r_address.object_names, r_address.object_args DELETE FROM _object_reference.object WHERE object_id = r_object.object_id; RAISE DEBUG 'deleting object %' DELETE FROM _object_reference.object WHERE DELETE FROM _object_reference.object WHERE object_id = r_object.object_id; RAISE DEBUG 'deleting object %', r_object; LOOP JOIN _object_reference.object USING( classid, objid, objsubid ) FROM pg_catalog.pg_event_trigger_dropped_objects() d SELECT object.* FOR r_object IN -- Could potentially be done with a writable CTE -- Multiple objects might have been affectedad  /]*" M   K D / b [ V H  u i END LOOP; RAISE END L END LOOP; RAISE DEBUG 'dropped_objects(): %', r_address; FOR r_address IN SE END LOOP; RAISE DEBUG 'dropped_objects(): %', r_address; FOR r_address IN SELECT classid, objid, objsu END LOOP; RAISE DEBUG 'dropped_objects(): %', r_address; FOR r_address IN SELECT classid, objid, objsubid, object_type, schema_name, object_identity FROM pg_catalog.pg_event_trigger_dropped_objects() LOOPBEGIN r_address record; r_object _object_reference.object;DECLARE) RETURNS event_trigger LANGUAGE plpgsql AS $body$CREATE FUNCTION _object_reference._etg_drop($body$;END ; (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)) WHERE (object_type::text, object_names, object_args) IS DISTINCT FROM , object_args = (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)).object_args , object_names = (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)).object_namesadBff\:Y.- d UPDATE _object_reference.object ; ) UPDATE _object_r U UPDATE _object_reference.objec UPDATE _object_reference.object ; ) , r_address.object_names, r_address.obj UPDATE _object_reference.object UPDATE _object_reference.object ; ) , r_addre UPDATE _object_reference.object ; ) , r_address.ob UPDATE _object_reference.object UPDATE _object_reference.object ; ) , r_address.object_names, r_address.object_args UPDATE _object_reference.object ; ) , r_address UPDATE _object_reference. UPDATE _object_reference.obj UPDA SET object_type = (pg_catalog.pg_identify_object_as_address(classid, objid, objsubid)).type::cat_tools.object_type UPDATE _object_reference.objectadf {. 6  l ON (o.class ON (o.classid, o.objid, o.objsubid) = (d.classid, d.objid, d.objsubid) JOIN _object_reference.object o ON (d.refclassid, d.ref ON (o.classid, o.objid, o.objsubid) = ON (o.classid, o.objid, o.objsubid) ON (o.classid, o.objid, o.objsubid) = ( ON ON (o.classid, o ON (o.classid, o.objid, o.objsubid) = (d.classid, d.objid, d.objsubid) ON (o.classid, o.objid, o.objsubid) = (d.cla ON (o.classid, o.objid, o.objsubid) = (d.classid, d.objid, d.obj ON ( ON (o.classid, o.o */ * safe and attempt the update to classid. If it actually */ * constraint on the table sh */ * constraint on the table should catch it. * safe and attempt the update to object_type. If it actually does change the * Presumably there's no way for an objects type/classid to change, but be /* */ * for any names that have changed. * Rather than trying to be cute about this, we just do a brute-force check *