raimund@1298: -- Based on https://github.com/xdimedrolx/audit-trigger/
raimund@1298: --
raimund@1298: -- which is licensed by "The PostgreSQL License", effectively equivalent to the BSD
raimund@1298: -- license.
raimund@1298: 
raimund@1298: SET search_path TO land;
raimund@1298: CREATE TABLE audit_trail(
raimund@1298:       id bigserial primary key,
raimund@1298:       table_name varchar(50) not null,
raimund@1298:       tstamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
raimund@1298:       action varchar(1) NOT NULL CHECK (action IN ('I','D','U', 'T')),
raimund@1298:       object_id integer not null,
raimund@1298:       row_data JSONB,
raimund@1298:       changed_fields JSONB
raimund@1298: );
raimund@1298: 
raimund@1298: CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb)
raimund@1298:   RETURNS jsonb AS
raimund@1298:   $BODY$
raimund@1298:        SELECT COALESCE(
raimund@1298:               (
raimund@1298:               SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
raimund@1298:               FROM jsonb_each(a)
raimund@1298:               WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b
raimund@1298:               )
raimund@1298:        , '{}')::jsonb;
raimund@1298:        $BODY$
raimund@1298: LANGUAGE sql IMMUTABLE STRICT;
raimund@1298: COMMENT ON FUNCTION jsonb_delete_left(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument';
raimund@1298: DROP OPERATOR IF EXISTS - (jsonb, jsonb);
raimund@1298: CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = jsonb);
raimund@1298: COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand';
raimund@1298: 
raimund@1298: 
raimund@1298: CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[])
raimund@1298:   RETURNS jsonb AS
raimund@1298:   $BODY$
raimund@1298:        SELECT COALESCE(
raimund@1298:               (
raimund@1298:               SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
raimund@1298:               FROM jsonb_each(a)
raimund@1298:               WHERE key <> ALL(b)
raimund@1298:               )
raimund@1298:        , '{}')::jsonb;
raimund@1298:        $BODY$
raimund@1298: LANGUAGE sql IMMUTABLE STRICT;
raimund@1298: COMMENT ON FUNCTION jsonb_delete_left(jsonb, text[]) IS 'delete keys in second argument from first argument';
raimund@1298: DROP OPERATOR IF EXISTS - (jsonb, text[]);
raimund@1298: CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text[]);
raimund@1298: COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand';
raimund@1298: 
raimund@1298: 
raimund@1298: CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text)
raimund@1298:   RETURNS jsonb AS
raimund@1298:   $BODY$
raimund@1298:        SELECT COALESCE(
raimund@1298:               (
raimund@1298:               SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
raimund@1298:               FROM jsonb_each(a)
raimund@1298:               WHERE key <> b
raimund@1298:               )
raimund@1298:        , '{}')::jsonb;
raimund@1298:        $BODY$
raimund@1298: LANGUAGE sql IMMUTABLE STRICT;
raimund@1298: COMMENT ON FUNCTION jsonb_delete_left(jsonb, text) IS 'delete key in second argument from first argument';
raimund@1298: DROP OPERATOR IF EXISTS - (jsonb, text);
raimund@1298: CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text);
raimund@1298: COMMENT ON OPERATOR - (jsonb, text) IS 'delete key from left operand';
raimund@1298: 
raimund@1298: 
raimund@1298: CREATE OR REPLACE FUNCTION if_modified_func() RETURNS TRIGGER AS $body$
raimund@1298: DECLARE
raimund@1298:     audit_row land.audit_trail;
raimund@1298:     include_values boolean;
raimund@1298:     log_diffs boolean;
raimund@1298:     h_old jsonb;
raimund@1298:     h_new jsonb;
raimund@1298:     excluded_cols text[] = ARRAY[]::text[];
raimund@1298: BEGIN
raimund@1298:     IF TG_WHEN <> 'AFTER' THEN
raimund@1298:         RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger';
raimund@1298:     END IF;
raimund@1298: 
raimund@1307:     -- Do nothing on delete.
raimund@1307:     IF (TG_OP = 'DELETE') THEN
raimund@1298:         RETURN NULL;
raimund@1298:     END IF;
raimund@1298: 
raimund@1298:     audit_row = ROW(
raimund@1298:         nextval('land.audit_trail_id_seq'), -- id
raimund@1298:         TG_TABLE_NAME::varchar,             -- table_name
raimund@1298:         current_timestamp,                  -- tstamp
raimund@1298:         substring(TG_OP,1,1),               -- action
raimund@1307:         NEW.id,                             -- object_id
raimund@1298:         NULL, NULL                          -- row_data, changed_fields
raimund@1298:         );
raimund@1298: 
raimund@1298:     IF TG_ARGV[1] IS NOT NULL THEN
raimund@1298:         excluded_cols = TG_ARGV[1]::text[];
raimund@1298:     END IF;
raimund@1298: 
raimund@1298:     IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
raimund@1298:         audit_row.row_data = row_to_json(OLD)::JSONB;
raimund@1298:         audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols;
raimund@1298:         IF audit_row.changed_fields = '{}'::jsonb THEN
raimund@1298:             -- All changed fields are ignored. Skip this update.
raimund@1298:             RETURN NULL;
raimund@1298:         END IF;
raimund@1307:     ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
raimund@1307:         audit_row.row_data = row_to_json(NEW)::JSONB - excluded_cols;
raimund@1307:         audit_row.changed_fields = jsonb_strip_nulls(row_to_json(NEW)::JSONB - excluded_cols);
raimund@1298:     ELSE
raimund@1298:         RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
raimund@1298:         RETURN NULL;
raimund@1298:     END IF;
raimund@1298:     INSERT INTO land.audit_trail VALUES (audit_row.*);
raimund@1298:     RETURN NULL;
raimund@1298: END;
raimund@1298: $body$
raimund@1298: LANGUAGE plpgsql
raimund@1298: SECURITY DEFINER
raimund@1298: SET search_path = land, public;
raimund@1298: 
raimund@1298: CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, ignored_cols text[]) RETURNS void AS $body$
raimund@1298: DECLARE
raimund@1298:   stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE';
raimund@1298:   _q_txt text;
raimund@1298:   _ignored_cols_snip text = '';
raimund@1298: BEGIN
raimund@1298:     EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::TEXT);
raimund@1298:     EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::TEXT);
raimund@1298: 
raimund@1298:     IF audit_rows THEN
raimund@1298:         IF array_length(ignored_cols,1) > 0 THEN
raimund@1298:             _ignored_cols_snip = ', ' || quote_literal(ignored_cols);
raimund@1298:         END IF;
raimund@1298:         _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' ||
raimund@1298:                  quote_ident(target_table::TEXT) ||
raimund@1298:                  ' FOR EACH ROW EXECUTE PROCEDURE land.if_modified_func(' ||
raimund@1298:                  quote_literal(audit_query_text) || _ignored_cols_snip || ');';
raimund@1298:         RAISE NOTICE '%',_q_txt;
raimund@1298:         EXECUTE _q_txt;
raimund@1298:         stm_targets = 'TRUNCATE';
raimund@1298:     ELSE
raimund@1298:     END IF;
raimund@1298: 
raimund@1298:     _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' ||
raimund@1298:              target_table ||
raimund@1298:              ' FOR EACH STATEMENT EXECUTE PROCEDURE land.if_modified_func('||
raimund@1298:              quote_literal(audit_query_text) || ');';
raimund@1298:     RAISE NOTICE '%',_q_txt;
raimund@1298:     EXECUTE _q_txt;
raimund@1298: 
raimund@1298: END;
raimund@1298: $body$
raimund@1298: language 'plpgsql';
raimund@1298: 
raimund@1298: COMMENT ON FUNCTION audit_table(regclass, boolean, boolean, text[]) IS $body$
raimund@1298: Add auditing support to a table.
raimund@1298: 
raimund@1298: Arguments:
raimund@1298:    target_table:     Table name, schema qualified if not on search_path
raimund@1298:    audit_rows:       Record each row change, or only audit at a statement level
raimund@1298:    audit_query_text: Record the text of the client query that triggered the audit event?
raimund@1298:    ignored_cols:     Columns to exclude from update diffs, ignore updates that change only ignored cols.
raimund@1298: $body$;
raimund@1298: 
raimund@1298: -- Pg doesn't allow variadic calls with 0 params, so provide a wrapper
raimund@1298: CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $body$
raimund@1298: SELECT audit_table($1, $2, $3, ARRAY[]::text[]);
raimund@1298: $body$ LANGUAGE SQL;
raimund@1298: 
raimund@1298: -- And provide a convenience call wrapper for the simplest case
raimund@1298: -- of row-level logging with no excluded cols and query logging enabled.
raimund@1298: --
raimund@1298: CREATE OR REPLACE FUNCTION audit_table(target_table regclass) RETURNS void AS $body$
raimund@1298: SELECT audit_table($1, BOOLEAN 't', BOOLEAN 't');
raimund@1298: $body$ LANGUAGE 'sql';
raimund@1298: 
raimund@1298: COMMENT ON FUNCTION audit_table(regclass) IS $body$
raimund@1298: Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored.
raimund@1298: $body$;
raimund@1298: 
raimund@1298: CREATE INDEX probe_id_ndx ON audit_trail(cast("row_data"->>'probe_id' AS int));
raimund@1298: CREATE INDEX messung_id_ndx ON audit_trail(cast("row_data"->>'messung_id' AS int));
raimund@1298: 
raimund@1298: -- View for probe audit trail
raimund@1298: CREATE OR REPLACE VIEW audit_trail_probe AS
raimund@1298: SELECT
raimund@1298:     id,
raimund@1298:     table_name,
raimund@1307:     action,
raimund@1298:     object_id,
raimund@1298:     tstamp,
raimund@1298:     cast(row_data ->> 'messungs_id' AS integer) AS messungs_id,
raimund@1298:     coalesce(cast(row_data ->> 'probe_id' AS integer),
raimund@1298:         (SELECT probe_id FROM messung WHERE id = cast(
raimund@1298:             row_data ->> 'messungs_id' AS integer))) AS probe_id,
raimund@1298:     row_data,
raimund@1298:     changed_fields
raimund@1298: FROM audit_trail;
raimund@1298: 
raimund@1298: 
raimund@1298: -- View for messung audit trail
raimund@1298: CREATE OR REPLACE VIEW audit_trail_messung AS
raimund@1298: SELECT audit_trail.id,
raimund@1298:     audit_trail.table_name,
raimund@1298:     audit_trail.tstamp,
raimund@1298:     audit_trail.action,
raimund@1298:     audit_trail.object_id,
raimund@1298:     audit_trail.row_data,
raimund@1298:     audit_trail.changed_fields,
raimund@1298:     cast(row_data ->> 'messungs_id' AS int) AS messungs_id
raimund@1298: FROM audit_trail;
raimund@1298: 
raimund@1298: 
raimund@1307: SELECT audit_table('probe', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
raimund@1307: SELECT audit_table('messung', true, false, '{id,, tree_modified, letzte_aenderung, status}'::text[]);
raimund@1307: SELECT audit_table('messwert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
raimund@1307: SELECT audit_table('kommentar_p', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
raimund@1307: SELECT audit_table('kommentar_m', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
raimund@1307: SELECT audit_table('zusatz_wert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
raimund@1307: SELECT audit_table('ortszuordnung', true, false, '{id, tree_modified, letzte_aenderung}'::text[]);
raimund@1298: 
raimund@1298: SET search_path TO public;