I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables.
INSERT it seems easy enough to create a trigger on the metadata table that checks for a matching row in
pg_class, but I'm struggling to come up with a way to delete the row when the table is dropped.
I've looked into event triggers, but I can't find a way to determine the oid of the dropped table. Based on the information on the event trigger page and here https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER it doesn't seem like there's any data provided about the object that the event affects.
Is there any way of ensuring maintained integrity between my metadata table and the referenced rows in
edit: I found https://www.postgresql.org/docs/current/functions-event-triggers.html so it looks like I should be able to get the oid from an event trigger.
Here's what I ended up using
CREATE OR REPLACE FUNCTION delete_table_metadata() RETURNS event_trigger AS $$ DECLARE table_oid oid; schema_nm varchar; BEGIN FOR table_oid, schema_nm IN SELECT objid, schema_name FROM pg_event_trigger_dropped_objects() LOOP IF schema_nm = 'myschema' THEN DELETE FROM table_meta WHERE table_meta_id = table_oid; END IF; END LOOP; END $$ LANGUAGE plpgsql;
People seem to be curious why I want to do this. I have a need for dynamically generated tables and associated application data models. The metadata I need to store isn't actually about the tables per se, it's for the application models. Since the models aren't hardcoded I need a way to persistently store some information about them. There's an argument to be made that I should instead control the creation and dropping of this data from the application rather than the database, but this is more foolproof and I can foresee times where the tables might be manipulated directly from the database rather than the application during admin work.