Mercurial > lada > lada-server
annotate db_schema/audit.sql @ 1315:84bb7e2aecb1
Do not redefine existing operator.
The removed SQL failed on PostgreSQL 9.5 because the operator is
provided and needed by the system. Further, it does not seem to be
used here.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 17 Mar 2017 13:25:57 +0100 |
parents | 1a3e86f4d768 |
children | d3113d594013 |
rev | line source |
---|---|
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
1 -- Based on https://github.com/xdimedrolx/audit-trigger/ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
2 -- |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
3 -- which is licensed by "The PostgreSQL License", effectively equivalent to the BSD |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
4 -- license. |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
5 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
6 SET search_path TO land; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
7 CREATE TABLE audit_trail( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
8 id bigserial primary key, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
9 table_name varchar(50) not null, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
10 tstamp TIMESTAMP WITHOUT TIME ZONE NOT NULL, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
11 action varchar(1) NOT NULL CHECK (action IN ('I','D','U', 'T')), |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
12 object_id integer not null, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
13 row_data JSONB, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
14 changed_fields JSONB |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
15 ); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
16 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
17 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b jsonb) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
18 RETURNS jsonb AS |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
19 $BODY$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
20 SELECT COALESCE( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
21 ( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
22 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
23 FROM jsonb_each(a) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
24 WHERE NOT ('{' || to_json(key) || ':' || value || '}')::jsonb <@ b |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
25 ) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
26 , '{}')::jsonb; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
27 $BODY$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
28 LANGUAGE sql IMMUTABLE STRICT; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
29 COMMENT ON FUNCTION jsonb_delete_left(jsonb, jsonb) IS 'delete matching pairs in second argument from first argument'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
30 DROP OPERATOR IF EXISTS - (jsonb, jsonb); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
31 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = jsonb); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
32 COMMENT ON OPERATOR - (jsonb, jsonb) IS 'delete matching pairs from left operand'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
33 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
34 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
35 CREATE OR REPLACE FUNCTION jsonb_delete_left(a jsonb, b text[]) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
36 RETURNS jsonb AS |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
37 $BODY$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
38 SELECT COALESCE( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
39 ( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
40 SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
41 FROM jsonb_each(a) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
42 WHERE key <> ALL(b) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
43 ) |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
44 , '{}')::jsonb; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
45 $BODY$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
46 LANGUAGE sql IMMUTABLE STRICT; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
47 COMMENT ON FUNCTION jsonb_delete_left(jsonb, text[]) IS 'delete keys in second argument from first argument'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
48 DROP OPERATOR IF EXISTS - (jsonb, text[]); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
49 CREATE OPERATOR - ( PROCEDURE = jsonb_delete_left, LEFTARG = jsonb, RIGHTARG = text[]); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
50 COMMENT ON OPERATOR - (jsonb, text[]) IS 'delete keys from left operand'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
51 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
52 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
53 CREATE OR REPLACE FUNCTION if_modified_func() RETURNS TRIGGER AS $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
54 DECLARE |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
55 audit_row land.audit_trail; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
56 include_values boolean; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
57 log_diffs boolean; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
58 h_old jsonb; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
59 h_new jsonb; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
60 excluded_cols text[] = ARRAY[]::text[]; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
61 BEGIN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
62 IF TG_WHEN <> 'AFTER' THEN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
63 RAISE EXCEPTION 'land.if_modified_func() may only run as an AFTER trigger'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
64 END IF; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
65 |
1307
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
66 -- Do nothing on delete. |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
67 IF (TG_OP = 'DELETE') THEN |
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
68 RETURN NULL; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
69 END IF; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
70 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
71 audit_row = ROW( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
72 nextval('land.audit_trail_id_seq'), -- id |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
73 TG_TABLE_NAME::varchar, -- table_name |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
74 current_timestamp, -- tstamp |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
75 substring(TG_OP,1,1), -- action |
1307
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
76 NEW.id, -- object_id |
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
77 NULL, NULL -- row_data, changed_fields |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
78 ); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
79 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
80 IF TG_ARGV[1] IS NOT NULL THEN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
81 excluded_cols = TG_ARGV[1]::text[]; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
82 END IF; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
83 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
84 IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
85 audit_row.row_data = row_to_json(OLD)::JSONB; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
86 audit_row.changed_fields = row_to_json(NEW)::JSONB - audit_row.row_data - excluded_cols; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
87 IF audit_row.changed_fields = '{}'::jsonb THEN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
88 -- All changed fields are ignored. Skip this update. |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
89 RETURN NULL; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
90 END IF; |
1307
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
91 ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
92 audit_row.row_data = row_to_json(NEW)::JSONB - excluded_cols; |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
93 audit_row.changed_fields = jsonb_strip_nulls(row_to_json(NEW)::JSONB - excluded_cols); |
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
94 ELSE |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
95 RAISE EXCEPTION '[land.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
96 RETURN NULL; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
97 END IF; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
98 INSERT INTO land.audit_trail VALUES (audit_row.*); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
99 RETURN NULL; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
100 END; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
101 $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
102 LANGUAGE plpgsql |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
103 SECURITY DEFINER |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
104 SET search_path = land, public; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
105 |
1314
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
106 CREATE OR REPLACE FUNCTION audit_table( |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
107 target_table regclass, |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
108 audit_rows boolean, |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
109 audit_query_text boolean, |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
110 ignored_cols text[] |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
111 ) RETURNS void AS |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
112 $body$ |
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
113 DECLARE |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
114 stm_targets text = 'INSERT OR UPDATE OR DELETE OR TRUNCATE'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
115 _q_txt text; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
116 _ignored_cols_snip text = ''; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
117 BEGIN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
118 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || quote_ident(target_table::TEXT); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
119 EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || quote_ident(target_table::TEXT); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
120 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
121 IF audit_rows THEN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
122 IF array_length(ignored_cols,1) > 0 THEN |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
123 _ignored_cols_snip = ', ' || quote_literal(ignored_cols); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
124 END IF; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
125 _q_txt = 'CREATE TRIGGER audit_trigger_row AFTER INSERT OR UPDATE OR DELETE ON ' || |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
126 quote_ident(target_table::TEXT) || |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
127 ' FOR EACH ROW EXECUTE PROCEDURE land.if_modified_func(' || |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
128 quote_literal(audit_query_text) || _ignored_cols_snip || ');'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
129 RAISE NOTICE '%',_q_txt; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
130 EXECUTE _q_txt; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
131 stm_targets = 'TRUNCATE'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
132 ELSE |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
133 END IF; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
134 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
135 _q_txt = 'CREATE TRIGGER audit_trigger_stm AFTER ' || stm_targets || ' ON ' || |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
136 target_table || |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
137 ' FOR EACH STATEMENT EXECUTE PROCEDURE land.if_modified_func('|| |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
138 quote_literal(audit_query_text) || ');'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
139 RAISE NOTICE '%',_q_txt; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
140 EXECUTE _q_txt; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
141 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
142 END; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
143 $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
144 language 'plpgsql'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
145 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
146 COMMENT ON FUNCTION audit_table(regclass, boolean, boolean, text[]) IS $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
147 Add auditing support to a table. |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
148 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
149 Arguments: |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
150 target_table: Table name, schema qualified if not on search_path |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
151 audit_rows: Record each row change, or only audit at a statement level |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
152 audit_query_text: Record the text of the client query that triggered the audit event? |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
153 ignored_cols: Columns to exclude from update diffs, ignore updates that change only ignored cols. |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
154 $body$; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
155 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
156 -- Pg doesn't allow variadic calls with 0 params, so provide a wrapper |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
157 CREATE OR REPLACE FUNCTION audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean) RETURNS void AS $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
158 SELECT audit_table($1, $2, $3, ARRAY[]::text[]); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
159 $body$ LANGUAGE SQL; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
160 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
161 -- And provide a convenience call wrapper for the simplest case |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
162 -- of row-level logging with no excluded cols and query logging enabled. |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
163 -- |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
164 CREATE OR REPLACE FUNCTION audit_table(target_table regclass) RETURNS void AS $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
165 SELECT audit_table($1, BOOLEAN 't', BOOLEAN 't'); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
166 $body$ LANGUAGE 'sql'; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
167 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
168 COMMENT ON FUNCTION audit_table(regclass) IS $body$ |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
169 Add auditing support to the given table. Row-level changes will be logged with full client query text. No cols are ignored. |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
170 $body$; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
171 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
172 CREATE INDEX probe_id_ndx ON audit_trail(cast("row_data"->>'probe_id' AS int)); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
173 CREATE INDEX messung_id_ndx ON audit_trail(cast("row_data"->>'messung_id' AS int)); |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
174 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
175 -- View for probe audit trail |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
176 CREATE OR REPLACE VIEW audit_trail_probe AS |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
177 SELECT |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
178 id, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
179 table_name, |
1307
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
180 action, |
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
181 object_id, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
182 tstamp, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
183 cast(row_data ->> 'messungs_id' AS integer) AS messungs_id, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
184 coalesce(cast(row_data ->> 'probe_id' AS integer), |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
185 (SELECT probe_id FROM messung WHERE id = cast( |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
186 row_data ->> 'messungs_id' AS integer))) AS probe_id, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
187 row_data, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
188 changed_fields |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
189 FROM audit_trail; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
190 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
191 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
192 -- View for messung audit trail |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
193 CREATE OR REPLACE VIEW audit_trail_messung AS |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
194 SELECT audit_trail.id, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
195 audit_trail.table_name, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
196 audit_trail.tstamp, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
197 audit_trail.action, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
198 audit_trail.object_id, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
199 audit_trail.row_data, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
200 audit_trail.changed_fields, |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
201 cast(row_data ->> 'messungs_id' AS int) AS messungs_id |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
202 FROM audit_trail; |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
203 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
204 |
1307
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
205 SELECT audit_table('probe', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); |
1314
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
206 SELECT audit_table('messung', true, false, |
1a3e86f4d768
Fix broken array and improve readability of function definition.
Tom Gottfried <tom@intevation.de>
parents:
1307
diff
changeset
|
207 '{id, tree_modified, letzte_aenderung, status}'::text[]); |
1307
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
208 SELECT audit_table('messwert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
209 SELECT audit_table('kommentar_p', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
210 SELECT audit_table('kommentar_m', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
211 SELECT audit_table('zusatz_wert', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); |
c2df9c848e9d
Record inserts in audit table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1298
diff
changeset
|
212 SELECT audit_table('ortszuordnung', true, false, '{id, tree_modified, letzte_aenderung}'::text[]); |
1298
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
213 |
ba47994c1665
Add audit-trail table/trigger/views to database.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
diff
changeset
|
214 SET search_path TO public; |