DECLARE
v_tabname text;
v_loguser boolean := false;
- v_nspname text;
+ v_nspname text;
v_num_col int;
v_num_col_log int;
v_col_trig text := '';
- v_val_trig text := '';
+ v_val_trig text := '';
v_cols text := '';
v_sql text;
v_col_cache text;
- v_max_cache int;
v_enable_cache boolean := true;
+ v_enable_prepare boolean := false;
+ v_tmp text;
+ v_i int;
+
+ v_cols_typ text;
+ v_cols_nam text[];
+ v_vals_old text = '';
+ v_vals_new text = '';
BEGIN
-- Notes:
-- - The trigger_id comes off sequence, this function is oblivious
END IF;
END IF;
- IF (v_enable_cache AND left(v_col_cache, length(TG_RELID::text)+1) = (TG_RELID::text || ':')) THEN
+ -- If column caching is enabled and previous call in this transaction
+ -- was for the same relation we can retrieve column detail.
+ IF (v_enable_cache AND left(v_col_cache, length(TG_RELID::text)+1) = (TG_RELID::text || ':')) THEN
v_cols := right(v_col_cache, (length(TG_RELID::text)+1)*-1);
- ELSE
+ v_cols_nam := ('{' || right(v_col_cache, (length(TG_RELID::text)+1)*-1) || '}')::text[];
+ ELSE -- Otherwise fetch the column detail
IF (TG_WHEN != 'AFTER') THEN
RAISE EXCEPTION 'table_log: must be fired after event';
END IF;
IF (TG_LEVEL = 'STATEMENT') THEN
RAISE EXCEPTION 'table_log: can''t process STATEMENT events';
- END IF;
+ END IF;
- SELECT count(*), string_agg(quote_ident(attname),',') INTO STRICT v_num_col, v_cols
+ SELECT count(*), string_agg(quote_ident(attname),','), string_agg(format_type(atttypid, atttypmod),','), array_agg(quote_ident(attname))
+ INTO STRICT v_num_col, v_cols, v_cols_typ, v_cols_nam
FROM pg_catalog.pg_attribute
WHERE attrelid = TG_RELID
AND attnum > 0
- AND NOT attisdropped;
+ AND NOT attisdropped;
IF (v_num_col < 1) THEN
RAISE EXCEPTION 'table_log: number of columns in table is < 1, can this happen?';
- END IF;
+ END IF;
SELECT count(*) INTO STRICT v_num_col_log
FROM pg_catalog.pg_attribute
WHERE attrelid = (v_nspname || '.' || v_tabname)::regclass
AND attnum > 0
- AND NOT attisdropped;
+ AND NOT attisdropped;
IF (v_num_col_log < 1) THEN
RAISE EXCEPTION 'could not get number columns in relation %.%', v_nspname, v_tabname;
v_col_cache := (TG_RELID::text || ':' || v_cols);
PERFORM set_config('table_log.column_cache', v_col_cache, true);
END IF;
+
+ -- Create a prepared statement for the current table, deallocating
+ -- any old statements we may have prepared.
+ IF (v_enable_prepare) THEN
+ FOR v_tmp IN (SELECT name FROM pg_catalog.pg_prepared_statements WHERE name ~ '^table_log_pl_') LOOP
+ EXECUTE format('DEALLOCATE %I', v_tmp);
+ END LOOP;
+
+ SELECT '$' || string_agg(a::text, ', $') INTO v_col_trig FROM generate_series(1,v_num_col+3+v_loguser::int) a;
+
+ IF (v_loguser) THEN
+ 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);
+ ELSE
+ 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);
+ END IF;
+ EXECUTE v_sql;
+ END IF;
END IF;
+
+ -- If prepared statement method is enabled, construct strings for
+ -- variable parameters and execute.
+ IF (v_enable_prepare) THEN
+ FOR v_i IN 1..array_upper(v_cols_nam, 1) LOOP
+ IF (TG_OP != 'INSERT') THEN
+ EXECUTE 'SELECT ($1).' || v_cols_nam[v_i] || '::text' INTO v_tmp USING OLD;
+ v_vals_old := v_vals_old || quote_nullable(v_tmp) || ',';
+ END IF;
+ IF (TG_OP != 'DELETE') THEN
+ EXECUTE 'SELECT ($1).' || v_cols_nam[v_i] || '::text' INTO v_tmp USING NEW;
+ v_vals_new := v_vals_new || quote_nullable(v_tmp) || ',';
+ END IF;
+ END LOOP;
+
+ IF (v_loguser) THEN
+ v_vals_new := v_vals_new || quote_literal(session_user) || ',';
+ v_vals_old := v_vals_old || quote_literal(session_user) || ',';
+ END IF;
- IF (v_loguser) THEN
- v_col_trig := v_col_trig || ', "trigger_user"';
- v_val_trig := format('%L, ', session_user);
- END IF;
- v_col_trig := v_col_trig || ', "trigger_mode", "trigger_changed", "trigger_tuple"';
- v_val_trig := format('%s%L, %L', v_val_trig, TG_OP, current_timestamp);
-
- IF (TG_OP != 'INSERT') THEN
- 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);
- EXECUTE v_sql USING OLD;
- END IF;
- IF (TG_OP != 'DELETE') THEN
- 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);
- EXECUTE v_sql USING NEW;
- RETURN NEW;
- ELSE
- RETURN OLD;
+ IF (TG_OP != 'INSERT') THEN
+ v_sql := format('EXECUTE table_log_pl_%s(%s%L, %L, %L)', TG_RELID, v_vals_old, TG_OP, current_timestamp, 'old');
+ EXECUTE v_sql;
+ END IF;
+ IF (TG_OP != 'DELETE') THEN
+ v_sql := format('EXECUTE table_log_pl_%s(%s%L, %L, %L)', TG_RELID, v_vals_new, TG_OP, current_timestamp, 'new');
+ EXECUTE v_sql;
+ RETURN NEW;
+ ELSE
+ RETURN OLD;
+ END IF;
+ ELSE -- Otherwise we can do the inserts dynamically.
+ IF (v_loguser) THEN
+ v_col_trig := v_col_trig || ', "trigger_user"';
+ v_val_trig := format('%L, ', session_user);
+ END IF;
+ v_col_trig := v_col_trig || ', "trigger_mode", "trigger_changed", "trigger_tuple"';
+ v_val_trig := format('%s%L, %L', v_val_trig, TG_OP, current_timestamp);
+
+ IF (TG_OP != 'INSERT') THEN
+ 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);
+ EXECUTE v_sql USING OLD;
+ END IF;
+ IF (TG_OP != 'DELETE') THEN
+ 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);
+ EXECUTE v_sql USING NEW;
+ RETURN NEW;
+ ELSE
+ RETURN OLD;
+ END IF;
END IF;
END;
projects / postgresql / table_log_pl / blobdiff