*** a/doc/src/sgml/event-trigger.sgml --- b/doc/src/sgml/event-trigger.sgml *************** *** 27,35 **** An event trigger fires whenever the event with which it is associated occurs in the database in which it is defined. Currently, the only ! supported events are ddl_command_start ! and ddl_command_end. Support for additional events may be ! added in future releases. --- 27,35 ---- An event trigger fires whenever the event with which it is associated occurs in the database in which it is defined. Currently, the only ! supported events ! are ddl_command_start, ddl_command_end. Support ! for additional events may be added in future releases. *************** *** 46,51 **** --- 46,59 ---- + To list all objects that have been deleted as part of executing a + command, use the Set Returning + Function pg_event_trigger_dropped_objects() from + your ddl_command_end event trigger code. Note that happens + after the objects have been deleted, so no catalog lookup is possible. + + + Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated ddl_command_end triggers will not be executed. Conversely, *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** *** 15688,15696 **** FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); choose a trigger name that comes after the name of any other trigger you might have on the table. ! For more information about creating triggers, see . --- 15688,15742 ---- choose a trigger name that comes after the name of any other trigger you might have on the table. ! For more information about creating triggers, see . + + + Event Trigger Functions + + + pg_dropped_objects + + + + Currently PostgreSQL provides one built in event trigger + helper function, pg_event_trigger_dropped_objects, which + will list all object dropped by a DROP command. That + listing includes multiple targets of the command, as in DROP + TABLE a, b, c; and objects dropped because of + a CASCADE dependency. + + + + The pg_event_trigger_dropped_objects function can be used + in an event trigger like this: + + create function test_event_trigger_for_sql_drop() + returns event_trigger as $$ + DECLARE + obj record; + BEGIN + RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; + + FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() + LOOP + RAISE NOTICE '% dropped object: % % % %', + tg_tag, + obj.classId::regclass, + obj.classId, obj.objid, obj.objsubid; + END LOOP; + END + $$ language plpgsql; + + + + + For more information about event triggers, + see . + + + *** a/src/backend/access/transam/xact.c --- b/src/backend/access/transam/xact.c *************** *** 30,35 **** --- 30,36 ---- #include "catalog/namespace.h" #include "catalog/storage.h" #include "commands/async.h" + #include "commands/event_trigger.h" #include "commands/tablecmds.h" #include "commands/trigger.h" #include "executor/spi.h" *************** *** 1955,1960 **** CommitTransaction(void) --- 1956,1962 ---- AtEOXact_HashTables(true); AtEOXact_PgStat(true); AtEOXact_Snapshot(true); + AtEOXact_EventTrigger(true); pgstat_report_xact_timestamp(0); CurrentResourceOwner = NULL; *************** *** 2208,2213 **** PrepareTransaction(void) --- 2210,2216 ---- AtEOXact_HashTables(true); /* don't call AtEOXact_PgStat here */ AtEOXact_Snapshot(true); + AtEOXact_EventTrigger(true); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); *************** *** 2382,2387 **** CleanupTransaction(void) --- 2385,2391 ---- */ AtCleanup_Portals(); /* now safe to release portal memory */ AtEOXact_Snapshot(false); /* and release the transaction's snapshots */ + AtEOXact_EventTrigger(false); /* and reset Event Trigger internal state */ CurrentResourceOwner = NULL; /* and resource owner */ if (TopTransactionResourceOwner) *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *************** *** 81,117 **** #include "utils/tqual.h" - /* - * Deletion processing requires additional state for each ObjectAddress that - * it's planning to delete. For simplicity and code-sharing we make the - * ObjectAddresses code support arrays with or without this extra state. - */ - typedef struct - { - int flags; /* bitmask, see bit definitions below */ - ObjectAddress dependee; /* object whose deletion forced this one */ - } ObjectAddressExtra; - - /* ObjectAddressExtra flag bits */ - #define DEPFLAG_ORIGINAL 0x0001 /* an original deletion target */ - #define DEPFLAG_NORMAL 0x0002 /* reached via normal dependency */ - #define DEPFLAG_AUTO 0x0004 /* reached via auto dependency */ - #define DEPFLAG_INTERNAL 0x0008 /* reached via internal dependency */ - #define DEPFLAG_EXTENSION 0x0010 /* reached via extension dependency */ - #define DEPFLAG_REVERSE 0x0020 /* reverse internal/extension link */ - - - /* expansible list of ObjectAddresses */ - struct ObjectAddresses - { - ObjectAddress *refs; /* => palloc'd array */ - ObjectAddressExtra *extras; /* => palloc'd array, or NULL if not used */ - int numrefs; /* current number of references */ - int maxrefs; /* current size of palloc'd array(s) */ - }; - - /* typedef ObjectAddresses appears in dependency.h */ - /* threaded list of ObjectAddresses, for recursion detection */ typedef struct ObjectAddressStack { --- 81,86 ---- *************** *** 347,353 **** performMultipleDeletions(const ObjectAddresses *objects, */ for (i = 0; i < targetObjects->numrefs; i++) { ! ObjectAddress *thisobj = targetObjects->refs + i; deleteOneObject(thisobj, &depRel, flags); } --- 316,330 ---- */ for (i = 0; i < targetObjects->numrefs; i++) { ! ObjectAddress *thisobj; ! ! thisobj = targetObjects->refs + i; ! ! if (EventTriggerSQLDropInProgress && ! EventTriggerSupportsObjectType(getObjectClass(thisobj))) ! { ! add_exact_object_address(thisobj, EventTriggerSQLDropList); ! } deleteOneObject(thisobj, &depRel, flags); } *************** *** 2175,2180 **** record_object_address_dependencies(const ObjectAddress *depender, --- 2152,2169 ---- behavior); } + int + get_object_addresses_numelements(const ObjectAddresses *addresses) + { + return addresses->numrefs; + } + + ObjectAddress * + get_object_addresses_element(const ObjectAddresses *addresses, int i) + { + return addresses->refs + i; + } + /* * Clean up when done with an ObjectAddresses array. */ *** a/src/backend/commands/event_trigger.c --- b/src/backend/commands/event_trigger.c *************** *** 25,30 **** --- 25,31 ---- #include "commands/dbcommands.h" #include "commands/event_trigger.h" #include "commands/trigger.h" + #include "funcapi.h" #include "parser/parse_func.h" #include "pgstat.h" #include "miscadmin.h" *************** *** 39,44 **** --- 40,49 ---- #include "utils/syscache.h" #include "tcop/utility.h" + /* Globally visible state variables */ + bool EventTriggerSQLDropInProgress = false; + ObjectAddresses *EventTriggerSQLDropList = NULL; + typedef struct { const char *obtypename; *************** *** 150,157 **** CreateEventTrigger(CreateEventTrigStmt *stmt) } /* Validate tag list, if any. */ ! if (strcmp(stmt->eventname, "ddl_command_start") == 0 && tags != NULL) validate_ddl_tags("tag", tags); /* * Give user a nice error message if an event trigger of the same name --- 155,166 ---- } /* Validate tag list, if any. */ ! if ((strcmp(stmt->eventname, "ddl_command_start") == 0 || ! strcmp(stmt->eventname, "ddl_command_end") == 0) ! && tags != NULL) ! { validate_ddl_tags("tag", tags); + } /* * Give user a nice error message if an event trigger of the same name *************** *** 739,744 **** EventTriggerDDLCommandEnd(Node *parsetree) --- 748,761 ---- /* Cleanup. */ list_free(runlist); + + if (EventTriggerSQLDropInProgress) + { + free_object_addresses(EventTriggerSQLDropList); + + EventTriggerSQLDropInProgress = false; + EventTriggerSQLDropList = NULL; + } } /* *************** *** 825,827 **** EventTriggerSupportsObjectType(ObjectType obtype) --- 842,948 ---- } return true; } + + /* + * SQL DROP event support functions + */ + void + EventTriggerInitDropList(void) + { + EventTriggerSQLDropInProgress = true; + EventTriggerSQLDropList = new_object_addresses(); + } + + /* + * AtEOXact_EventTrigger + * Event Trigger's cleanup function for end of transaction + */ + void + AtEOXact_EventTrigger(bool isCommit) + { + /* even on success we want to reset EventTriggerSQLDropInProgress */ + EventTriggerSQLDropInProgress = false; + } + + /* + * pg_event_trigger_dropped_objects + * + * Make the list of dropped objects available to the user function run by the + * Event Trigger. + */ + Datum + pg_event_trigger_dropped_objects(PG_FUNCTION_ARGS) + { + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + TupleDesc tupdesc; + Tuplestorestate *tupstore; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + int i; + + /* + * This function is meant to be called from within an event trigger in + * order to get the list of objects dropped, if any. + */ + if (!EventTriggerSQLDropInProgress) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("pg_dropped_objects() can only be called from an event trigger function"))); + + /* check to see if caller supports us returning a tuplestore */ + if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("set-valued function called in context that cannot accept a set"))); + if (!(rsinfo->allowedModes & SFRM_Materialize)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialize mode required, but it is not allowed in this context"))); + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + /* Build tuplestore to hold the result rows */ + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldcontext = MemoryContextSwitchTo(per_query_ctx); + + tupstore = tuplestore_begin_heap(true, false, work_mem); + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = tupdesc; + + MemoryContextSwitchTo(oldcontext); + + for (i = 0; i < get_object_addresses_numelements(EventTriggerSQLDropList); i++) + { + ObjectAddress *object; + Datum values[3]; + bool nulls[3]; + + /* Emit result row */ + object = get_object_addresses_element(EventTriggerSQLDropList, i); + + MemSet(values, 0, sizeof(values)); + MemSet(nulls, 0, sizeof(nulls)); + + /* classid */ + values[0] = ObjectIdGetDatum(object->classId); + + /* objid */ + values[1] = ObjectIdGetDatum(object->objectId); + + /* objsubid */ + if (OidIsValid(object->objectSubId)) + values[2] = ObjectIdGetDatum(object->objectSubId); + else + nulls[2] = true; + + tuplestore_putvalues(tupstore, tupdesc, values, nulls); + } + + /* clean up and return the tuplestore */ + tuplestore_donestoring(tupstore); + + return (Datum) 0; + } *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 698,715 **** standard_ProcessUtility(Node *parsetree, { DropStmt *stmt = (DropStmt *) parsetree; ! if (isCompleteQuery ! && EventTriggerSupportsObjectType(stmt->removeType)) EventTriggerDDLCommandStart(parsetree); switch (stmt->removeType) { case OBJECT_INDEX: - if (stmt->concurrent) - PreventTransactionChain(isTopLevel, - "DROP INDEX CONCURRENTLY"); - /* fall through */ - case OBJECT_TABLE: case OBJECT_SEQUENCE: case OBJECT_VIEW: --- 698,730 ---- { DropStmt *stmt = (DropStmt *) parsetree; ! /* ! * don't run any event trigger when we require not to have open ! * a transaction ! */ ! if (stmt->removeType == OBJECT_INDEX && stmt->concurrent) ! PreventTransactionChain(isTopLevel, ! "DROP INDEX CONCURRENTLY"); ! ! if (isCompleteQuery && ! EventTriggerSupportsObjectType(stmt->removeType)) ! { EventTriggerDDLCommandStart(parsetree); + /* + * cater with multiple targets and cascading drops. + * + * Initialize that after having called the + * ddl_command_start triggers so that + * EventTriggerSQLDropInProgress is still false there, as + * that protects pg_dropped_objects() calls. + */ + EventTriggerInitDropList(); + } + switch (stmt->removeType) { case OBJECT_INDEX: case OBJECT_TABLE: case OBJECT_SEQUENCE: case OBJECT_VIEW: *************** *** 723,730 **** standard_ProcessUtility(Node *parsetree, if (isCompleteQuery && EventTriggerSupportsObjectType(stmt->removeType)) EventTriggerDDLCommandEnd(parsetree); ! break; } --- 738,746 ---- if (isCompleteQuery && EventTriggerSupportsObjectType(stmt->removeType)) + { EventTriggerDDLCommandEnd(parsetree); ! } break; } *** a/src/include/catalog/dependency.h --- b/src/include/catalog/dependency.h *************** *** 107,113 **** typedef enum SharedDependencyType SHARED_DEPENDENCY_INVALID = 0 } SharedDependencyType; ! /* expansible list of ObjectAddresses (private in dependency.c) */ typedef struct ObjectAddresses ObjectAddresses; /* --- 107,142 ---- SHARED_DEPENDENCY_INVALID = 0 } SharedDependencyType; ! /* ! * Deletion processing requires additional state for each ObjectAddress that ! * it's planning to delete. For simplicity and code-sharing we make the ! * ObjectAddresses code support arrays with or without this extra state. ! */ ! typedef struct ! { ! int flags; /* bitmask, see bit definitions below */ ! ObjectAddress dependee; /* object whose deletion forced this one */ ! } ObjectAddressExtra; ! ! /* ObjectAddressExtra flag bits */ ! #define DEPFLAG_ORIGINAL 0x0001 /* an original deletion target */ ! #define DEPFLAG_NORMAL 0x0002 /* reached via normal dependency */ ! #define DEPFLAG_AUTO 0x0004 /* reached via auto dependency */ ! #define DEPFLAG_INTERNAL 0x0008 /* reached via internal dependency */ ! #define DEPFLAG_EXTENSION 0x0010 /* reached via extension dependency */ ! #define DEPFLAG_REVERSE 0x0020 /* reverse internal/extension link */ ! ! ! /* expansible list of ObjectAddresses */ ! struct ObjectAddresses ! { ! ObjectAddress *refs; /* => palloc'd array */ ! ObjectAddressExtra *extras; /* => palloc'd array, or NULL if not used */ ! int numrefs; /* current number of references */ ! int maxrefs; /* current size of palloc'd array(s) */ ! }; ! ! /* ObjectAddresses is exported to be used in Event Triggers */ typedef struct ObjectAddresses ObjectAddresses; /* *************** *** 191,196 **** extern void record_object_address_dependencies(const ObjectAddress *depender, --- 220,230 ---- ObjectAddresses *referenced, DependencyType behavior); + extern int get_object_addresses_numelements(const ObjectAddresses *addresses); + + extern ObjectAddress *get_object_addresses_element(const ObjectAddresses *addresses, + int i); + extern void free_object_addresses(ObjectAddresses *addrs); /* in pg_depend.c */ *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** *** 4679,4685 **** DESCR("SP-GiST support for quad tree over range"); DATA(insert OID = 3473 ( spg_range_quad_leaf_consistent PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "2281 2281" _null_ _null_ _null_ _null_ spg_range_quad_leaf_consistent _null_ _null_ _null_ )); DESCR("SP-GiST support for quad tree over range"); ! /* * Symbolic values for provolatile column: these indicate whether the result * of a function is dependent *only* on the values of its explicit arguments, --- 4679,4687 ---- DATA(insert OID = 3473 ( spg_range_quad_leaf_consistent PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "2281 2281" _null_ _null_ _null_ _null_ spg_range_quad_leaf_consistent _null_ _null_ _null_ )); DESCR("SP-GiST support for quad tree over range"); ! /* event triggers */ ! DATA(insert OID = 3566 ( pg_event_trigger_dropped_objects PGNSP PGUID 12 10 100 0 0 f f f f t t s 0 0 2249 "" "{26,26,26}" "{o,o,o}" "{classid, objid, objsubid}" _null_ pg_event_trigger_dropped_objects _null_ _null_ _null_ )); ! DESCR("list an extension's version update paths"); /* * Symbolic values for provolatile column: these indicate whether the result * of a function is dependent *only* on the values of its explicit arguments, *** a/src/include/commands/event_trigger.h --- b/src/include/commands/event_trigger.h *************** *** 13,21 **** --- 13,35 ---- #ifndef EVENT_TRIGGER_H #define EVENT_TRIGGER_H + #include "catalog/dependency.h" + #include "catalog/objectaddress.h" #include "catalog/pg_event_trigger.h" #include "nodes/parsenodes.h" + /* + * Global objects that we need to keep track of for benefits of Event Triggers. + * + * The EventTriggerSQLDropList is a list of ObjectAddress filled in from + * dependency.c doDeletion() function. Only objects that are supported as in + * EventTriggerSupportsObjectType() get appended here. ProcessUtility is + * responsible for resetting this list to NIL at the beginning of any DROP + * operation. + */ + extern bool EventTriggerSQLDropInProgress; + extern ObjectAddresses *EventTriggerSQLDropList; + typedef struct EventTriggerData { NodeTag type; *************** *** 43,46 **** extern bool EventTriggerSupportsObjectType(ObjectType obtype); --- 57,67 ---- extern void EventTriggerDDLCommandStart(Node *parsetree); extern void EventTriggerDDLCommandEnd(Node *parsetree); + extern void EventTriggerInitDropList(void); + extern List *EventTriggerAppendToDropList(ObjectAddress *object); + extern void EventTriggerSQLDrop(Node *parsetree); + + extern void AtEOXact_EventTrigger(bool isCommit); + + #endif /* EVENT_TRIGGER_H */ *** a/src/include/utils/builtins.h --- b/src/include/utils/builtins.h *************** *** 1146,1151 **** extern Datum pg_describe_object(PG_FUNCTION_ARGS); --- 1146,1154 ---- /* commands/constraint.c */ extern Datum unique_key_recheck(PG_FUNCTION_ARGS); + /* commands/event_trigger.c */ + extern Datum pg_event_trigger_dropped_objects(PG_FUNCTION_ARGS); + /* commands/extension.c */ extern Datum pg_available_extensions(PG_FUNCTION_ARGS); extern Datum pg_available_extension_versions(PG_FUNCTION_ARGS); *** a/src/test/regress/expected/event_trigger.out --- b/src/test/regress/expected/event_trigger.out *************** *** 93,103 **** ERROR: event trigger "regress_event_trigger" does not exist drop role regression_bob; ERROR: role "regression_bob" cannot be dropped because some objects depend on it DETAIL: owner of event trigger regress_event_trigger3 ! -- these are all OK; the second one should emit a NOTICE drop event trigger if exists regress_event_trigger2; drop event trigger if exists regress_event_trigger2; NOTICE: event trigger "regress_event_trigger2" does not exist, skipping drop event trigger regress_event_trigger3; drop event trigger regress_event_trigger_end; ! drop function test_event_trigger(); drop role regression_bob; --- 93,166 ---- drop role regression_bob; ERROR: role "regression_bob" cannot be dropped because some objects depend on it DETAIL: owner of event trigger regress_event_trigger3 ! -- now test pg_event_trigger_dropped_objects() ! create function test_event_trigger_dropped_objects() returns event_trigger as $$ ! DECLARE ! obj record; ! BEGIN ! RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; ! ! FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() ! LOOP ! -- we can't output the full data that we have here because the OID ! -- would change each time we run the regression tests. ! -- ! -- obj.classId, obj.objid, obj.objsubid; ! RAISE NOTICE '% dropped object: %', tg_tag, obj.classId::regclass; ! END LOOP; ! END ! $$ language plpgsql; ! NOTICE: test_event_trigger: ddl_command_start CREATE FUNCTION ! NOTICE: test_event_trigger: ddl_command_end CREATE FUNCTION ! -- OK ! create event trigger regress_event_trigger_drop_objects on ddl_command_end ! when tag in ('drop table', 'drop function', 'drop view') ! execute procedure test_event_trigger_dropped_objects(); ! -- a simple enough test: cascade ! create table evt_a(id serial); ! NOTICE: test_event_trigger: ddl_command_start CREATE TABLE ! NOTICE: test_event_trigger: ddl_command_end CREATE TABLE ! create view evt_a_v as select id from evt_a; ! NOTICE: test_event_trigger: ddl_command_end CREATE VIEW ! drop table evt_a cascade; ! NOTICE: drop cascades to view evt_a_v ! NOTICE: test_event_trigger: ddl_command_end DROP TABLE ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_rewrite ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: test_event_trigger: ddl_command_end DROP TABLE ! -- another test with multiple targets ! create table evt_a(id serial); ! NOTICE: test_event_trigger: ddl_command_start CREATE TABLE ! NOTICE: test_event_trigger: ddl_command_end CREATE TABLE ! create table evt_b(id serial); ! NOTICE: test_event_trigger: ddl_command_start CREATE TABLE ! NOTICE: test_event_trigger: ddl_command_end CREATE TABLE ! drop table evt_a, evt_b; ! NOTICE: test_event_trigger: ddl_command_end DROP TABLE ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_type ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: DROP TABLE dropped object: pg_class ! NOTICE: test_event_trigger: ddl_command_end DROP TABLE ! -- these are all OK; the third one should emit a NOTICE ! drop event trigger if exists regress_event_trigger_drop_objects; drop event trigger if exists regress_event_trigger2; drop event trigger if exists regress_event_trigger2; NOTICE: event trigger "regress_event_trigger2" does not exist, skipping drop event trigger regress_event_trigger3; drop event trigger regress_event_trigger_end; ! drop function test_event_trigger_dropped_objects(); drop role regression_bob; *** a/src/test/regress/sql/event_trigger.sql --- b/src/test/regress/sql/event_trigger.sql *************** *** 97,106 **** drop event trigger regress_event_trigger; -- should fail, regression_bob owns regress_event_trigger2/3 drop role regression_bob; ! -- these are all OK; the second one should emit a NOTICE drop event trigger if exists regress_event_trigger2; drop event trigger if exists regress_event_trigger2; drop event trigger regress_event_trigger3; drop event trigger regress_event_trigger_end; ! drop function test_event_trigger(); drop role regression_bob; --- 97,141 ---- -- should fail, regression_bob owns regress_event_trigger2/3 drop role regression_bob; ! -- now test pg_event_trigger_dropped_objects() ! create function test_event_trigger_dropped_objects() returns event_trigger as $$ ! DECLARE ! obj record; ! BEGIN ! RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag; ! ! FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() ! LOOP ! -- we can't output the full data that we have here because the OID ! -- would change each time we run the regression tests. ! -- ! -- obj.classId, obj.objid, obj.objsubid; ! RAISE NOTICE '% dropped object: %', tg_tag, obj.classId::regclass; ! END LOOP; ! END ! $$ language plpgsql; ! ! -- OK ! create event trigger regress_event_trigger_drop_objects on ddl_command_end ! when tag in ('drop table', 'drop function', 'drop view') ! execute procedure test_event_trigger_dropped_objects(); ! ! -- a simple enough test: cascade ! create table evt_a(id serial); ! create view evt_a_v as select id from evt_a; ! drop table evt_a cascade; ! ! -- another test with multiple targets ! create table evt_a(id serial); ! create table evt_b(id serial); ! drop table evt_a, evt_b; ! ! -- these are all OK; the third one should emit a NOTICE ! drop event trigger if exists regress_event_trigger_drop_objects; drop event trigger if exists regress_event_trigger2; drop event trigger if exists regress_event_trigger2; drop event trigger regress_event_trigger3; drop event trigger regress_event_trigger_end; ! drop function test_event_trigger_dropped_objects(); ! drop role regression_bob;