46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
# File 'lib/pg_audit_log/function.rb', line 46
def install
execute <<-SQL
CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION #{name}() RETURNS trigger
LANGUAGE plpgsql
AS $_$
DECLARE
col information_schema.columns %ROWTYPE;
new_value text;
old_value text;
primary_key_column varchar;
primary_key_value varchar;
user_identifier integer;
unique_name varchar;
column_name varchar;
BEGIN
user_identifier := #{pg_audit_log_old_style_user_id ? %q(current_setting('audit.user_id')) : 'pg_temp.pg_audit_log_user_identifier()'};
IF user_identifier = #{DISABLED_USER} THEN
RETURN NULL;
END IF;
unique_name := #{pg_audit_log_old_style_user_id ? %q(current_setting('audit.user_unique_name')) : 'pg_temp.pg_audit_log_user_unique_name()'};
primary_key_column := NULL;
EXECUTE 'SELECT pg_attribute.attname
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = $1::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary'
INTO primary_key_column USING TG_RELNAME;
primary_key_value := NULL;
FOR col IN SELECT * FROM information_schema.columns WHERE table_name = TG_RELNAME LOOP
new_value := NULL;
old_value := NULL;
column_name := col.column_name;
IF TG_RELNAME = '#{users_table_name}' AND column_name = '#{users_access_column}' THEN
NULL;
ELSE
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
EXECUTE 'SELECT CAST($1 . '|| column_name ||' AS TEXT)' INTO new_value USING NEW;
IF primary_key_value IS NULL AND primary_key_column IS NOT NULL THEN
EXECUTE 'SELECT CAST($1 . '|| primary_key_column ||' AS VARCHAR)' INTO primary_key_value USING NEW;
END IF;
END IF;
IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
EXECUTE 'SELECT CAST($1 . '|| column_name ||' AS TEXT)' INTO old_value USING OLD;
IF primary_key_value IS NULL AND primary_key_column IS NOT NULL THEN
EXECUTE 'SELECT CAST($1 . '|| primary_key_column ||' AS VARCHAR)' INTO primary_key_value USING OLD;
END IF;
END IF;
IF TG_OP != 'UPDATE' OR new_value != old_value OR (TG_OP = 'UPDATE' AND ( (new_value IS NULL AND old_value IS NOT NULL) OR (new_value IS NOT NULL AND old_value IS NULL))) THEN
INSERT INTO audit_log("operation",
"table_name",
"primary_key",
"field_name",
"field_value_old",
"field_value_new",
"user_id",
"user_unique_name",
"occurred_at"
)
VALUES(TG_OP,
TG_RELNAME,
primary_key_value,
column_name,
old_value,
new_value,
user_identifier,
unique_name,
current_timestamp);
END IF;
END IF;
END LOOP;
RETURN NULL;
END
$_$;
SQL
end
|