CODEWITHSUNDEEP
postgresqltriggerssql-insert
user23968581
user23968581

Reputation: 3

Postgres guard column function Insert statements

I am making columns immutable using BEFORE triggers and my current function works fine to protect columns from UPDATE, but I am having some trouble with INSERT.

Basically the problem is that my current implementation does not take into account default values for columns.

CREATE FUNCTION guard_columns()
  RETURNS TRIGGER
  AS $$
DECLARE
  _column TEXT;
  _old_value TEXT;
  _new_value TEXT;
BEGIN
  IF CURRENT_USER != 'postgres' THEN
    FOR i IN 0..TG_NARGS - 1 LOOP
      _column := TG_ARGV[i];
      
      EXECUTE FORMAT('SELECT ($1).%I::TEXT', _column)
      USING OLD INTO _old_value;
      EXECUTE FORMAT('SELECT ($1).%I::TEXT', _column)
      USING NEW INTO _new_value;

      IF TG_OP = 'UPDATE' AND _old_value IS DISTINCT FROM _new_value THEN
        RAISE invalid_parameter_value
        USING message = FORMAT('Attempt to modify immutable column: %I', _column);
      ELSIF TG_OP = 'INSERT' AND _old_value IS NOT NULL THEN
        RAISE invalid_parameter_value
        USING message = FORMAT('Attempt to set value for immutable column: %I', _column);
      END IF;
    END LOOP;
  END IF;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER _protect_column
  BEFORE INSERT OR UPDATE ON protected_table
  FOR EACH ROW
  EXECUTE FUNCTION guard_columns ('id', 'user_id', 'created_at', 'last_modified_at', 'last_modified_by');

In this case, even if no id is given for an INSERT operation, it still throws the error "Attempt to set value for immutable column: id" because it does not know that there is a default value (DEFAULT gen_random_uuid ()) for the column. And the same for the other columns.

How can I work around this problem?

I tried to query pg_attribute and a.attrelid, but without success. And maybe this is not even necessary.

Upvotes: 0

Views: 65

Answers (0)

Related Questions