This module contains several function implementing rules that restrict certain subset of update commands on a table. These functions are designed to be set as on update per-row triggers on a table. This work is sponsored by EnovaFinancial (enovafinancial.com) Installation: cd contrib/check_updates make install To disable the embedded assertion checks in allow_on_condition use 'noassert' target: make noassert install Optionally you can run a regression test: make installcheck. Run psql to install any of function (deny_updates, allow_on_condition, check_updates) into a database: psql -f deny_updates.sql $databasename Make sure your PostgreSQL installation contains pl/perlu support and that pl/perlu is installed into the database. Usage: Create a new 'on update' trigger on the target table. CREATE TRIGGER deny_test_updates BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE deny_updates('false', password); 1. deny_updates Purpose: block updates to individual table columns. Syntax: deny_updates(options, [fieldname1, fieldname2, ..., fieldnameN]) Where: options - options for the trigger fieldname1, fieldname2, .. - names of the target fields Options: The following options are allowed (case insensitive). If multiple options are used, they must be comma delimited. ALLOW_LIST - list of fields is a list of fields that should allow updates (instead of the normal case of disallowing updates to listed fields) ONLY_FROM_NULL - fields can only be updated if they were originally NULL Examples: Suppose we have a table called test, defined as: CREATE TABLE test(id INTEGER, name VARCHAR, password VARCHAR); To disallow updates for the password field a trigger should be added using the following command: CREATE TRIGGER deny_test_updates BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE deny_updates('', password); If the goal is to allow updates on id field only then this command can be implemented: CREATE TRIGGER deny_test_updates BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE deny_updates('ALLOW_LIST', id); or as an alternative: CREATE TRIGGER deny_test_updates BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE deny_updates('', name, password); A trigger can be defined with an empty set of arguments. It will block all updates to the table. 2. allow_on_condition Purpose: allow updates to the table only if the user supplied condition is satisfied. Syntax: allow_on_condition(condition_string, ['attribute1', 'attribute2', ..., 'attributeN']); Where: condition_string - a string with SQL condition, with %s placeholders instead of column names. attribute1, .. attributeN - list of tuple attribute names prefixed with either NEW. or OLD. Examples: Let's take the table test2 defined as: CREATE TABLE test2(a INTEGER, b INTEGER, c INTEGER, d INTEGER); To make a trigger allowing updates only when c becomes equal to 5: CREATE TRIGGER c_should_be_5 BEFORE UPDATE ON test2 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('%s = 5', NEW.c); To disallow updates to columns b and c: CREATE TRIGGER deny_b_c BEFORE UPDATE ON test2 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('%s = %s AND %s = %s', 'OLD.b', 'NEW.b', 'OLD.c', 'NEW.c'); Note: you can also deny_updates for the same effect: CREATE TRIGGER deny_b_c BEFORE UPDATE ON test2 FOR EACH ROW EXECUTE PROCEDURE deny_updates('', b, c); To allow updates to column d only if column a is NOT NULL: CREATE TRIGGER allow_d_if_a BEFORE UPDATE ON test2 FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('%s IS NOT NULL OR (%s = %s)', 'NEW.a', 'OLD.d', 'NEW.d'); 3. check_updates Purpose: combine deny_updates and allow_on_condition in one function. The logic is to block updates if they change a set of 'deny_columns', unless the 'allow_condition' is satisfied. Syntax: check_updates(options, [column1, column2, ...,columnN], condition_string, ['attribute1', 'attribute2',..,'attributeN']), where: options - list of options: ALLOW_LIST - list of fields is a list of fields that should allow updates (instead of the normal case of disallowing updates to listed fields) ONLY_FROM_NULL - fields can only be updated if they were originally NULL ASSERT_MESSAGE - if this option is specified, then the next 2 arguments are an assertion name, and a message which would be shown if the assertion would be triggered. [column1, ... columnN] - list of 'deny_columns' (see deny_updates) condition_string - a string representing the condition to check (see allow_on_condition) ['attribute1', 'attribute2', 'attributeN'] - list of attributes for the condition (see alow_on_condition). Examples: Assume we have a table test: CREATE TABLE test(a INTEGER, b INTEGER, c INTEGER); To block updates to columns a and b unless value of column b + c is greater than 10 use this trigger: CREATE TRIGGER check_updates_on_test BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE check_updates('f', 'a', 'b', '%s + %s > 10', 'NEW.b', 'NEW.c');