paste #s3V :
- Original query
- Prettified
| DECLARE +
| mut_row table_mutation; +
| excluded_cols text[] = ARRAY[]::text[]; +
| BEGIN +
| IF TG_WHEN <> 'AFTER' THEN +
| RAISE EXCEPTION 'handle_table_mutation() trigger may only run as an AFTER trigger'; +
| END IF; +
| +
| -- Construct the basis row we'll be inserting into the +
| -- mutation log table, populate it with inferred and +
| -- default values. If the GUC "myapp.administrator_id" is +
| -- set in the session, we'll also grab that. +
| mut_row = ROW( +
| nextval('table_mutation_id_seq'), +
| CURRENT_TIMESTAMP, -- tx_at +
| statement_timestamp(), -- action_at +
| txid_current(), -- transaction ID +
| substring(TG_OP, 1, 1), -- action +
| current_setting('myapp.administrator_id', true), -- administrator +
| session_user::text, -- user +
| current_setting('application_name'), -- client application +
| TG_TABLE_NAME::text, -- table name +
| NULL, -- populated below +
| NULL, -- populated below +
| TG_TABLE_SCHEMA::text -- schema name +
| ); +
| +
| IF TG_ARGV[1] IS NOT NULL THEN +
| excluded_cols = TG_ARGV[1]::text[]; +
| END IF; +
| +
| -- Only store changed columns with UPDATE +
| IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN +
| mut_row.mutations = hstore(NEW.*) - hstore(OLD.*) - excluded_cols; +
| IF mut_row.mutations = hstore('') THEN +
| -- No fields where changed, so skip this mutation +
| RETURN NULL; +
| END IF; +
| mut_row.entity_id = hstore(NEW.*)->COALESCE(TG_ARGV[0], 'id'); +
| ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN +
| mut_row.mutations = hstore(NEW.*) - excluded_cols; +
| mut_row.entity_id = hstore(NEW.*)->COALESCE(TG_ARGV[0], 'id'); +
| ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN +
| mut_row.mutations = hstore(OLD.*) - excluded_cols; +
| mut_row.entity_id = hstore(OLD.*)->COALESCE(TG_ARGV[0], 'id'); +
| ELSE +
| RAISE EXCEPTION '[handle_table_mutation()] - Trigger func added as trigger for unhandled case: %, %', TG_OP, TG_LEVEL;+
| RETURN NULL; +
| END IF; +
| +
| INSERT INTO table_mutation VALUES (mut_row.*); +
| RETURN NULL; +
| END; +
|
| DECLARE + | mut_row table_mutation;
+ | excluded_cols text[] = ARRAY[]::text[];
+ |
BEGIN
+ | IF TG_WHEN <> 'AFTER' THEN
+ | RAISE EXCEPTION 'handle_table_mutation() trigger may only run as an AFTER trigger';
+ |
END IF;
+ | + | -- Construct the basis row we'll be inserting into the +
| -- mutation log table, populate it with inferred and +
| -- default values. If the GUC "myapp.administrator_id" is +
| -- set in the session, we'll also grab that. +
| mut_row = ROW (+ | nextval('table_mutation_id_seq'),
+ | CURRENT_TIMESTAMP, -- tx_at +
| statement_timestamp(), -- action_at +
| txid_current(), -- transaction ID +
| substring(TG_OP, 1, 1), -- action +
| current_setting('myapp.administrator_id', TRUE), -- administrator +
| SESSION_USER::text, -- user +
| current_setting('application_name'), -- client application +
| TG_TABLE_NAME::text, -- table name +
| NULL, -- populated below +
| NULL, -- populated below +
| TG_TABLE_SCHEMA::text -- schema name +
|);
+ | + | IF TG_ARGV[1] IS NOT NULL THEN
+ | excluded_cols = TG_ARGV[1]::text[];
+ |
END IF;
+ | + | -- Only store changed columns with UPDATE +
| IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
+ | mut_row.mutations = hstore (NEW.*) - hstore (OLD.*) - excluded_cols;
+ | IF mut_row.mutations = hstore ('') THEN
+ | -- No fields where changed, so skip this mutation +
| RETURN NULL;
+ |
END IF;
+ | mut_row.entity_id = hstore (NEW.*) -> COALESCE(TG_ARGV[0], 'id');
+ |
ELSIF (TG_OP = 'INSERT'
AND TG_LEVEL = 'ROW') THEN
+ | mut_row.mutations = hstore (NEW.*) - excluded_cols;
+ | mut_row.entity_id = hstore (NEW.*) -> COALESCE(TG_ARGV[0], 'id');
+ |
ELSIF (TG_OP = 'DELETE'
AND TG_LEVEL = 'ROW') THEN
+ | mut_row.mutations = hstore (OLD.*) - excluded_cols;
+ | mut_row.entity_id = hstore (OLD.*) -> COALESCE(TG_ARGV[0], 'id');
+ |
ELSE
+ | RAISE EXCEPTION '[handle_table_mutation()] - Trigger func added as trigger for unhandled case: %, %', TG_OP, TG_LEVEL;
+ | RETURN NULL;
+ |
END IF;
+ | + | INSERT INTO table_mutation
VALUES (mut_row.*);
+ | RETURN NULL;
+ |
END;
+ |
-- Formatted by pgFormatter::Beautify