28 lines
1.0 KiB
PL/PgSQL
28 lines
1.0 KiB
PL/PgSQL
CREATE TABLE generic_log (
|
||
mtime timestamptz not null default now(),
|
||
action char not null check (action in ('I', 'U', 'D')),
|
||
username text not null,
|
||
table_name text not null,
|
||
row_data jsonb not null
|
||
);
|
||
|
||
CREATE INDEX ON generic_log USING brin (mtime);
|
||
CREATE INDEX ON generic_log ((row_data->>'my_pk’)) WHERE row_data->>'my_pk' IS NOT NULL; // note the cast to text as JSONB can’t be indexed with B-tree
|
||
CREATE EXTENSION IF NOT EXISTS btree_gin;
|
||
CREATE INDEX ON generic_log USING gin (table_name); // GiN is better for lots of repeating values
|
||
|
||
|
||
CREATE OR REPLACE FUNCTION public.generic_log_trigger()
|
||
RETURNS trigger LANGUAGE plpgsql
|
||
AS $function$
|
||
BEGIN
|
||
IF TG_OP = 'DELETE' THEN
|
||
INSERT INTO generic_log VALUES (now(), 'D', session_user, TG_TABLE_NAME, to_json(OLD));
|
||
ELSE
|
||
INSERT INTO generic_log VALUES (now(), TG_OP::char , session_user, TG_TABLE_NAME, to_json(NEW));
|
||
END IF;
|
||
RETURN NULL;
|
||
END;
|
||
$function$;
|
||
|
||
CREATE TRIGGER log_generic AFTER INSERT OR UPDATE OR DELETE ON some_table FOR EACH ROW EXECUTE FUNCTION generic_log_trigger(); |