Re: Immutable attributes? - Mailing list pgsql-general

On Sun, Apr 24, 2005 at 09:26:49PM +0200, Stephane Bortzmeyer wrote:
>
> To protect the database from programming errors (there is a team
> working on the project and some beginners may produce bugs), I would
> like to flag some attributes as immutable, meaning non modifiable in
> an UPDATE. (Typical examples are ID or creation time.)
>
> Currently, I use triggers:
...
> It is quite painful, since I need a function (with the list of
> immutable attributes) and a trigger per table. If I INHERITS from a
> table, triggers on the parent table are not called if I update the
> child table.

This wouldn't solve all your problems, but you could write a generic
trigger function in a language like PL/Tcl or PL/Python (or PL/Perl
in 8.0 and later) and pass the column name(s) as arguments.  Here's
a minimally-tested example -- if it doesn't work exactly the way
you want then at least it should serve as inspiration:

CREATE FUNCTION check_immutable() RETURNS trigger AS '
for col in TD["args"]:
    if TD["new"][col] != TD["old"][col]:
        plpy.error(''attribute "%s" is immutable'' % col)
return "OK"
' LANGUAGE plpythonu;

CREATE TABLE foo (
    id       serial PRIMARY KEY,
    created  date NOT NULL DEFAULT current_date,
    name     text NOT NULL
);

CREATE TRIGGER check_immutable BEFORE UPDATE ON foo
    FOR EACH ROW EXECUTE PROCEDURE check_immutable('id', 'created');

INSERT INTO foo (name) VALUES ('name 1');
SELECT * FROM foo;
 id |  created   |  name
----+------------+--------
  1 | 2005-04-24 | name 1
(1 row)

UPDATE foo SET created = '2005-05-01';
ERROR:  plpython: function "check_immutable" failed
DETAIL:  plpy.Error: ('attribute "created" is immutable',)

UPDATE foo SET id = 2;
ERROR:  plpython: function "check_immutable" failed
DETAIL:  plpy.Error: ('attribute "id" is immutable',)

UPDATE foo SET name = 'name 2', created = current_date;
SELECT * FROM foo;
 id |  created   |  name
----+------------+--------
  1 | 2005-04-24 | name 2
(1 row)

I was going to suggest using contrib/noupdate, but it doesn't appear
to work, at least not the way I was expecting:

CREATE TABLE foo (
    id    serial PRIMARY KEY,
    name  text NOT NULL
);

CREATE TRIGGER check_immutable BEFORE UPDATE ON foo
  FOR EACH ROW EXECUTE PROCEDURE noup('id');

INSERT INTO foo (name) VALUES ('name 1');

UPDATE foo SET id = 2;
WARNING:  id: update not allowed
UPDATE 0

UPDATE foo SET name = 'name 2';
WARNING:  id: update not allowed
UPDATE 0

I expected the second update to succeed since we're not changing
the value of id.  I'm wondering if that's a bug in the module.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/