Skip to content

Instantly share code, notes, and snippets.

@ringerc
Last active August 21, 2018 02:24
Show Gist options
  • Save ringerc/c8d8e31a1dcb55e19f56269954eabdf0 to your computer and use it in GitHub Desktop.
Save ringerc/c8d8e31a1dcb55e19f56269954eabdf0 to your computer and use it in GitHub Desktop.
draft
BEGIN;
SET LOCAL bdr.skip_ddl_locking=on;
SET LOCAL bdr.skip_ddl_replication=on;
CREATE OR REPLACE FUNCTION public.simple_log_row()
RETURNS trigger LANGUAGE plpgsql
SET search_path = 'pg_catalog'
AS $$
BEGIN
IF tg_op = 'INSERT' THEN
RAISE NOTICE 'AUDIT: INSERT on % (%) in s_r_r %', TG_TABLE_NAME, NEW::text, current_setting('session_replication_role');
RETURN NEW;
ELSIF tg_op = 'UPDATE' THEN
RAISE NOTICE 'AUDIT: UPDATE on % (%) => (%) in s_r_r %', TG_TABLE_NAME, OLD::text, NEW::text, current_setting('session_replication_role');
RETURN NEW;
ELSIF tg_op = 'DELETE' THEN
RAISE NOTICE 'AUDIT: DELETE on % (%) in s_r_r %', TG_TABLE_NAME, OLD::text, current_setting('session_replication_role');
RETURN OLD;
END IF;
END;
$$;
CREATE TRIGGER log_v_devices_replication BEFORE INSERT OR UPDATE OR DELETE ON public.v_devices FOR EACH ROW EXECUTE PROCEDURE public.simple_log_row();
ALTER TABLE public.v_devices ENABLE ALWAYS TRIGGER log_v_devices_replication;
CREATE TRIGGER log_v_device_lines_replication BEFORE INSERT OR UPDATE OR DELETE ON public.v_device_lines FOR EACH ROW EXECUTE PROCEDURE public.simple_log_row();
ALTER TABLE public.v_device_lines ENABLE ALWAYS TRIGGER log_v_device_lines_replication;
CREATE OR REPLACE FUNCTION public.simple_statement_log()
RETURNS trigger LANGUAGE plpgsql
SET search_path = 'pg_catalog'
AS
$$
BEGIN
RAISE NOTICE 'AUDIT: % % statement % affects table %', tg_when, tg_op, tg_table_name, current_query();
RETURN NULL;
END;
$$;
CREATE TRIGGER log_v_devices_statement_before BEFORE INSERT OR UPDATE OR DELETE ON public.v_devices FOR EACH STATEMENT EXECUTE PROCEDURE public.simple_statement_log();
CREATE TRIGGER log_v_devices_statement_after AFTER INSERT OR UPDATE OR DELETE ON public.v_devices FOR EACH STATEMENT EXECUTE PROCEDURE public.simple_statement_log();
CREATE TRIGGER log_v_device_lines_statement_before BEFORE INSERT OR UPDATE OR DELETE ON public.v_device_lines FOR EACH STATEMENT EXECUTE PROCEDURE public.simple_statement_log();
CREATE TRIGGER log_v_device_lines_statement_after AFTER INSERT OR UPDATE OR DELETE ON public.v_device_lines FOR EACH STATEMENT EXECUTE PROCEDURE public.simple_statement_log();
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment