Skip to content

Instantly share code, notes, and snippets.

@ichux
Last active September 19, 2018 09:28
Show Gist options
  • Save ichux/0d82f2080be0723e54b60f5441839e4b to your computer and use it in GitHub Desktop.
Save ichux/0d82f2080be0723e54b60f5441839e4b to your computer and use it in GitHub Desktop.
get all the triggers declared on a postgres table
SELECT routine_name FROM information_schema.routines
WHERE routine_type='FUNCTION' AND specific_schema='public' ORDER BY routine_name
SELECT n.nspname AS schema_name
, p.proname AS function_name
, pg_get_function_arguments(p.oid) AS args
, pg_get_functiondef(p.oid) AS func_def
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE NOT p.proisagg
AND n.nspname NOT LIKE 'pg_%'
AND n.nspname <> 'information_schema'
-- AND pg_get_functiondef(p.oid) ~* '\mbig\M';
ORDER BY function_name;
SELECT event_object_table, TRIGGER_NAME, action_statement, action_timing, event_manipulation
FROM information_schema.triggers
WHERE event_object_table IN
(SELECT relname FROM pg_class WHERE pg_class.oid IN (SELECT tgrelid FROM pg_trigger))
ORDER BY event_object_table,
event_manipulation
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment