Skip to content

Instantly share code, notes, and snippets.

@jmealo
Created May 7, 2021 17:50
Show Gist options
  • Save jmealo/0b925f9ab375e5cb0a2c204e6bc027c7 to your computer and use it in GitHub Desktop.
Save jmealo/0b925f9ab375e5cb0a2c204e6bc027c7 to your computer and use it in GitHub Desktop.
This trigger copies all the triggers attached to one table to another. If you use the tables name in the trigger, that'll get replaced as well.
CREATE OR REPLACE FUNCTION copy_table_triggers(p_source_tablename text, p_dest_tablename text) RETURNS boolean
LANGUAGE plpgsql
AS
$$
DECLARE
v_sql text;
v_trigger_list record;
BEGIN
-- Triggers
FOR v_trigger_list IN
SELECT tgrelid::regclass AS parent_table,
tgname AS trigger_name,
pg_get_triggerdef(oid) as trigger_def,
pg_get_functiondef(tgfoid) as trigger_function_def
FROM pg_trigger
WHERE tgrelid = p_source_tablename::regclass
LOOP
v_sql := replace((v_trigger_list.trigger_function_def || ';' || v_trigger_list.trigger_def), p_source_tablename::regclass::TEXT, p_dest_tablename::regclass::TEXT);
RAISE DEBUG 'Create Trigger: %', v_sql;
EXECUTE v_sql;
END LOOP;
RETURN true;
END
$$;
SELECT copy_table_triggers('public.backend_logs2', 'public.backend_logs3');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment