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/
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
