Thread: freezing a particular field in a table

freezing a particular field in a table

From
Ferindo Middleton Jr
Date:
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

Re: freezing a particular field in a table

From
"Jason Minion"
Date:
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


Re: freezing a particular field in a table

From
hubert depesz lubaczewski
Date:
On 11/23/05, Ferindo Middleton Jr <fmiddleton@verizon.net> wrote:
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

Re: freezing a particular field in a table

From
Michael Fuhr
Date:
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

Re: freezing a particular field in a table

From
hubert depesz lubaczewski
Date:
On 11/23/05, Michael Fuhr <mike@fuhr.org> wrote:
  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