1 \echo Use "ALTER EXTENSION table_log_pl UPDATE TO '0.2'" to load this file. \quit
3 CREATE OR REPLACE FUNCTION table_log_pl() RETURNS TRIGGER AS
7 v_loguser boolean := false;
11 v_col_trig text := '';
12 v_val_trig text := '';
16 v_enable_cache boolean := true;
17 v_enable_prepare boolean := false;
27 -- - The trigger_id comes off sequence, this function is oblivious
28 -- - 3 columns means don't log trigger_user or trigger_id
29 -- - 4 columns means don't log trigger_user
30 -- - 5 columns means log both
31 -- - To use the column data caching on server versions prior to
32 -- 9.6 add custom var "table_log.column_cache = ''" to postgresql.conf
34 IF (TG_NARGS > 2) THEN
35 v_nspname := TG_ARGV[2];
37 v_nspname := TG_TABLE_SCHEMA;
40 IF (TG_NARGS > 1 AND TG_ARGV[1]::int = 1) THEN
44 IF (TG_NARGS > 0) THEN
45 v_tabname := TG_ARGV[0];
47 v_tabname := TG_TABLE_NAME || '_log';
50 -- Retrieve custom variable used as a poor mans cache for multirow statements
51 IF (v_enable_cache) THEN
52 IF (current_setting('server_version_num')::int >= 90600) THEN
53 v_col_cache := current_setting('table_log.column_cache', true);
55 v_col_cache := current_setting('table_log.column_cache');
59 -- If column caching is enabled and previous call in this transaction
60 -- was for the same relation we can retrieve column detail.
61 IF (v_enable_cache AND left(v_col_cache, length(TG_RELID::text)+1) = (TG_RELID::text || ':')) THEN
62 v_cols := right(v_col_cache, (length(TG_RELID::text)+1)*-1);
63 v_cols_nam := ('{' || right(v_col_cache, (length(TG_RELID::text)+1)*-1) || '}')::text[];
64 ELSE -- Otherwise fetch the column detail
65 IF (TG_WHEN != 'AFTER') THEN
66 RAISE EXCEPTION 'table_log: must be fired after event';
68 IF (TG_LEVEL = 'STATEMENT') THEN
69 RAISE EXCEPTION 'table_log: can''t process STATEMENT events';
72 SELECT count(*), string_agg(quote_ident(attname),','), string_agg(format_type(atttypid, atttypmod),','), array_agg(quote_ident(attname))
73 INTO STRICT v_num_col, v_cols, v_cols_typ, v_cols_nam
74 FROM pg_catalog.pg_attribute
75 WHERE attrelid = TG_RELID
79 IF (v_num_col < 1) THEN
80 RAISE EXCEPTION 'table_log: number of columns in table is < 1, can this happen?';
83 SELECT count(*) INTO STRICT v_num_col_log
84 FROM pg_catalog.pg_attribute
85 WHERE attrelid = (v_nspname || '.' || v_tabname)::regclass
89 IF (v_num_col_log < 1) THEN
90 RAISE EXCEPTION 'could not get number columns in relation %.%', v_nspname, v_tabname;
93 -- This is the way the original checks column count regardless of trigger_id is presence
94 IF (v_num_col_log != (v_num_col + 3 + v_loguser::int)) AND (v_num_col_log != (v_num_col + 4 + v_loguser::int)) THEN
95 RAISE EXCEPTION 'number colums in relation %.%(%) does not match columns in %.%(%)', TG_TABLE_SCHEMA, TG_TABLE_NAME, v_num_col, v_nspname, v_tabname, v_num_col_log;
98 -- Set custom variable for use as a poor mans cache for multirow statements
99 IF (v_enable_cache) THEN
100 v_col_cache := (TG_RELID::text || ':' || v_cols);
101 PERFORM set_config('table_log.column_cache', v_col_cache, true);
104 -- Create a prepared statement for the current table, deallocating
105 -- any old statements we may have prepared.
106 IF (v_enable_prepare) THEN
107 FOR v_tmp IN (SELECT name FROM pg_catalog.pg_prepared_statements WHERE name ~ '^table_log_pl_') LOOP
108 EXECUTE format('DEALLOCATE %I', v_tmp);
111 SELECT '$' || string_agg(a::text, ', $') INTO v_col_trig FROM generate_series(1,v_num_col+3+v_loguser::int) a;
114 v_sql := format('PREPARE table_log_pl_%s(%s, text, text, timestamptz, text) AS INSERT INTO %I.%I (%s, "trigger_user", "trigger_mode", "trigger_changed", "trigger_tuple") VALUES (%s)', TG_RELID, v_cols_typ, v_nspname, v_tabname, v_cols, v_col_trig);
116 v_sql := format('PREPARE table_log_pl_%s(%s, text, timestamptz, text) AS INSERT INTO %I.%I (%s, "trigger_mode", "trigger_changed", "trigger_tuple") VALUES (%s)', TG_RELID, v_cols_typ, v_nspname, v_tabname, v_cols, v_col_trig);
122 -- If prepared statement method is enabled, construct strings for
123 -- variable parameters and execute.
124 IF (v_enable_prepare) THEN
125 FOR v_i IN 1..array_upper(v_cols_nam, 1) LOOP
126 IF (TG_OP != 'INSERT') THEN
127 EXECUTE 'SELECT ($1).' || v_cols_nam[v_i] || '::text' INTO v_tmp USING OLD;
128 v_vals_old := v_vals_old || quote_nullable(v_tmp) || ',';
130 IF (TG_OP != 'DELETE') THEN
131 EXECUTE 'SELECT ($1).' || v_cols_nam[v_i] || '::text' INTO v_tmp USING NEW;
132 v_vals_new := v_vals_new || quote_nullable(v_tmp) || ',';
137 v_vals_new := v_vals_new || quote_literal(session_user) || ',';
138 v_vals_old := v_vals_old || quote_literal(session_user) || ',';
141 IF (TG_OP != 'INSERT') THEN
142 v_sql := format('EXECUTE table_log_pl_%s(%s%L, %L, %L)', TG_RELID, v_vals_old, TG_OP, current_timestamp, 'old');
145 IF (TG_OP != 'DELETE') THEN
146 v_sql := format('EXECUTE table_log_pl_%s(%s%L, %L, %L)', TG_RELID, v_vals_new, TG_OP, current_timestamp, 'new');
152 ELSE -- Otherwise we can do the inserts dynamically.
154 v_col_trig := v_col_trig || ', "trigger_user"';
155 v_val_trig := format('%L, ', session_user);
157 v_col_trig := v_col_trig || ', "trigger_mode", "trigger_changed", "trigger_tuple"';
158 v_val_trig := format('%s%L, %L', v_val_trig, TG_OP, current_timestamp);
160 IF (TG_OP != 'INSERT') THEN
161 v_sql := format('INSERT INTO %I.%I (%s%s) SELECT %s, %s, ''old'' FROM (SELECT ($1::text::%I).*) t', v_nspname, v_tabname, v_cols, v_col_trig, v_cols, v_val_trig, TG_RELID::regclass);
162 EXECUTE v_sql USING OLD;
164 IF (TG_OP != 'DELETE') THEN
165 v_sql := format('INSERT INTO %I.%I (%s%s) SELECT %s, %s, ''new'' FROM (SELECT ($1::text::%I).*) t', v_nspname, v_tabname, v_cols, v_col_trig, v_cols, v_val_trig, TG_RELID::regclass);
166 EXECUTE v_sql USING NEW;
175 LANGUAGE plpgsql VOLATILE;