Thread: freezing a particular field in a table
Is there a way where you can implement a situation in a postgres table where a particular field cannot be changed when saved initially. I have table and there's one particular integer field that should never be changed in any record inserted... but I might need to allow the entire record to be deleted... I was just wondering if there's a way to implement something like this on a field (but not the entire row) at the database level? Ferindo Middleton
Perhaps a BEFORE UPDATE trigger, setting NEW.do_not_change_me =
OLD.do_not_change_me?
Jason Minion
IT Developer
Sigler Printing & Publishing
413 Northwestern Ave
Ames, IA 50010
515-232-6997
jason.minion@sigler.com
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ferindo Middleton
Jr
Sent: Tuesday, November 22, 2005 5:29 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] freezing a particular field in a table
Is there a way where you can implement a situation in a postgres table
where a particular field cannot be changed when saved initially. I have
table and there's one particular integer field that should never be
changed in any record inserted... but I might need to allow the entire
record to be deleted... I was just wondering if there's a way to
implement something like this on a field (but not the entire row) at the
database level?
Ferindo Middleton
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On 11/23/05, Ferindo Middleton Jr <fmiddleton@verizon.net> wrote:
CREATE OR REPLACE FUNCTION impossible_to_change() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF
(NEW.some_field IS NULL AND OLD.some_field IS NOT NULL)
OR
(NEW.some_field IS NOT NULL AND OLD.some_field IS NULL)
OR
(NEW.some_field IS NOT NULL AND OLD.some_field IS NOT NULL AND NEW.some_field <> OLD.some_field)
THEN
raise exception 'some_field cannod be changed!';
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER impossible_to_change BEFORE UPDATE ON some_table FOR EACH ROW EXECUTE PROCEDURE impossible_to_change();
in this way - whenever somebody will try to alter the value, it will raise exception.
in case you dont want exceptions, and just want old value to stick (i would vote against it), then just change body of the function to:
DECLARE
BEGIN
NEW.some_field := OLD.some_field;
return NEW;
END;
will work.
depesz
Is there a way where you can implement a situation in a postgres table
where a particular field cannot be changed when saved initially. I have
CREATE OR REPLACE FUNCTION impossible_to_change() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF
(NEW.some_field IS NULL AND OLD.some_field IS NOT NULL)
OR
(NEW.some_field IS NOT NULL AND OLD.some_field IS NULL)
OR
(NEW.some_field IS NOT NULL AND OLD.some_field IS NOT NULL AND NEW.some_field <> OLD.some_field)
THEN
raise exception 'some_field cannod be changed!';
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER impossible_to_change BEFORE UPDATE ON some_table FOR EACH ROW EXECUTE PROCEDURE impossible_to_change();
in this way - whenever somebody will try to alter the value, it will raise exception.
in case you dont want exceptions, and just want old value to stick (i would vote against it), then just change body of the function to:
DECLARE
BEGIN
NEW.some_field := OLD.some_field;
return NEW;
END;
will work.
depesz
On Wed, Nov 23, 2005 at 10:28:01AM +0100, hubert depesz lubaczewski wrote: > IF > (NEW.some_field IS NULL AND OLD.some_field IS NOT NULL) > OR > (NEW.some_field IS NOT NULL AND OLD.some_field IS NULL) > OR > (NEW.some_field IS NOT NULL AND OLD.some_field IS NOT NULL > AND NEW.some_field <> OLD.some_field) > THEN A more succinct expression would be: IF NEW.some_field IS DISTINCT FROM OLD.some_field THEN IS DISTINCT FROM is like <> except that it treats NULL as an ordinary value. > CREATE TRIGGER impossible_to_change BEFORE UPDATE ON some_table FOR EACH ROW > EXECUTE PROCEDURE impossible_to_change(); If the table has other triggers and you don't want them to modify the value either, then the check should probably go in an AFTER trigger. -- Michael Fuhr
On 11/23/05, Michael Fuhr <mike@fuhr.org> wrote:
thanks - i didn't know about this operator.
i would rather use before anyway.
the problem with after is that in aftrer - all you can do is to raise exception. while - if you would like to value to stick - you would ahve to change the code *and* change trigger declaration. putting it in before solves the problem.
of course there might be problem with another triggers, but that's another story.
depesz
IF NEW.some_field IS DISTINCT FROM OLD.some_field THEN
IS DISTINCT FROM is like <> except that it treats NULL as an ordinary
value.
thanks - i didn't know about this operator.
> CREATE TRIGGER impossible_to_change BEFORE UPDATE ON some_table FOR EACH ROW
> EXECUTE PROCEDURE impossible_to_change();
If the table has other triggers and you don't want them to modify
the value either, then the check should probably go in an AFTER
trigger.
i would rather use before anyway.
the problem with after is that in aftrer - all you can do is to raise exception. while - if you would like to value to stick - you would ahve to change the code *and* change trigger declaration. putting it in before solves the problem.
of course there might be problem with another triggers, but that's another story.
depesz