pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
freezing a particular field in a table
5+ messages / 4 participants
[nested] [flat]

* freezing a particular field in a table
@ 2005-11-22 23:28  Ferindo Middleton Jr <fmiddleton@verizon.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Ferindo Middleton Jr @ 2005-11-22 23:28 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: freezing a particular field in a table
@ 2005-11-22 23:38  Jason Minion <jason.minion@sigler.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Jason Minion @ 2005-11-22 23:38 UTC (permalink / raw)
  To: fmiddleton@verizon.net; pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: freezing a particular field in a table
@ 2005-11-23 09:28  hubert depesz lubaczewski <depesz@gmail.com>
  parent: Ferindo Middleton Jr <fmiddleton@verizon.net>
  0 siblings, 1 reply; 5+ messages in thread

From: hubert depesz lubaczewski @ 2005-11-23 09:28 UTC (permalink / raw)
  To: fmiddleton@verizon.net; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: freezing a particular field in a table
@ 2005-11-23 17:55  Michael Fuhr <mike@fuhr.org>
  parent: hubert depesz lubaczewski <depesz@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Michael Fuhr @ 2005-11-23 17:55 UTC (permalink / raw)
  To: hubert depesz lubaczewski <depesz@gmail.com>; +Cc: fmiddleton@verizon.net; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: freezing a particular field in a table
@ 2005-11-24 07:38  hubert depesz lubaczewski <depesz@gmail.com>
  parent: Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 5+ messages in thread

From: hubert depesz lubaczewski @ 2005-11-24 07:38 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: fmiddleton@verizon.net; pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-11-24 07:38 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-22 23:28 freezing a particular field in a table Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-11-23 09:28 ` hubert depesz lubaczewski <depesz@gmail.com>
2005-11-23 17:55   ` Michael Fuhr <mike@fuhr.org>
2005-11-24 07:38     ` hubert depesz lubaczewski <depesz@gmail.com>
2005-11-22 23:38 Re: freezing a particular field in a table Jason Minion <jason.minion@sigler.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox