Consider a trigger that would like to prevent certain operations when executed by a web application, while allowing the operations when performed by a nightly batch job.
One way to solve this is to use a kind of session variables. The trigger lets the operation execute normally only if a session variable is set, which the batch job sets at the start of the execution. Below is an example illustrating one implementation for this in PostgreSQL.
First, we define a kind of namespace for our session variable (a customized option or variable class in postgres lingo) in postgresql.conf:
# comma-separated list of class names:
custom_variable_classes = 'myvariableclass'
Now, consider the following table:
CREATE TABLE my_table (ID INTEGER);
INSERT INTO my_table VALUES (1);
A trigger is to prevent DELETE:s from that table unless the session variable myvariableclass.idontcare is set:
CREATE OR REPLACE FUNCTION prevent_delete_unless_idontcare() RETURNS TRIGGER AS $$
DECLARE
idontcare TEXT;
BEGIN
SELECT INTO idontcare CURRENT_SETTING('myvariableclass.idontcare');
IF (idontcare != 'true') THEN
RAISE EXCEPTION 'Not allowed to delete unless myvariableclass.idontcare is true';
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER prevent_delete_unless_idontcare_trigger
AFTER DELETE ON my_table
FOR EACH ROW EXECUTE PROCEDURE prevent_delete_unless_idontcare();
Now let's see it in action:
fornwall=> DELETE FROM my_table;
ERROR: Not allowed to delete unless myvariableclass.idontcare is true
fornwall=> SELECT SET_CONFIG('myvariableclass.idontcare', 'true', FALSE);
set_config
------------
true
(1 row)
fornwall=> DELETE FROM my_table;
DELETE 1
See postgres documentation for the current_setting and set_config functions.
