Örnekcreate_journal_table() metodu
şöyledir. Bu metod EVENT TRIGGER HANDLER içindir.
CREATE OR REPLACE FUNCTION create_journal_table()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
obj record;
schema_name text;
table_name text;
journal_table_name text;
BEGIN
FOR obj IN
SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
IF obj.command_tag = 'CREATE TABLE' AND obj.object_type = 'table' THEN
schema_name := split_part(obj.object_identity, '.', 1);
table_name := split_part(obj.object_identity, '.', 2);
schema_name := trim(both '"' from schema_name);
table_name := trim(both '"' from table_name);
-- Skip journal tables to avoid recursion
IF right(table_name, 8) = '_journal' THEN
CONTINUE;
END IF;
journal_table_name := format('%I.%I_journal', schema_name, table_name);
EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
id bigserial PRIMARY KEY,
action text NOT NULL,
changed_at timestamptz DEFAULT now(),
old_data jsonb,
new_data jsonb
)', journal_table_name);
EXECUTE format(
'CREATE TRIGGER %I_to_journal
AFTER INSERT OR UPDATE OR DELETE ON %I.%I
FOR EACH ROW
EXECUTE FUNCTION log_to_journal()',
table_name, schema_name, table_name
);
END IF;
END LOOP;
END;
$$;
Bu metodu register ederiz. Metod
şöyledir. Bu metod CREATE EVENT TRIGGER içindir.
CREATE EVENT TRIGGER create_journal_after_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION create_journal_table();log_to_journal() metodu
şöyledir. Bu metod row level logging yapar
CREATE OR REPLACE FUNCTION log_to_journal()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
journal_table text;
BEGIN
journal_table := format('%I.%I_journal', TG_TABLE_SCHEMA, TG_TABLE_NAME);
IF TG_OP = 'INSERT' THEN
EXECUTE format(
'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
journal_table
)
USING TG_OP, NULL, to_jsonb(NEW);
ELSIF TG_OP = 'UPDATE' THEN
EXECUTE format(
'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
journal_table
)
USING TG_OP, to_jsonb(OLD), to_jsonb(NEW);
ELSIF TG_OP = 'DELETE' THEN
EXECUTE format(
'INSERT INTO %s (action, old_data, new_data) VALUES ($1, $2::jsonb, $3::jsonb)',
journal_table
)
USING TG_OP, to_jsonb(OLD), NULL;
END IF;
RETURN NULL; -- AFTER trigger
END;
$$;
