pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfreezing 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>
2005-11-23 09:28 ` Re: freezing a particular field in a table hubert depesz lubaczewski <depesz@gmail.com>
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: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 ` Re: freezing a particular field in a table Michael Fuhr <mike@fuhr.org>
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-22 23:28 freezing a particular field in a table Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-11-23 09:28 ` Re: freezing a particular field in a table hubert depesz lubaczewski <depesz@gmail.com>
@ 2005-11-23 17:55 ` Michael Fuhr <mike@fuhr.org>
2005-11-24 07:38 ` Re: freezing a particular field in a table 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-22 23:28 freezing a particular field in a table Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-11-23 09:28 ` Re: freezing a particular field in a table hubert depesz lubaczewski <depesz@gmail.com>
2005-11-23 17:55 ` Re: freezing a particular field in a table Michael Fuhr <mike@fuhr.org>
@ 2005-11-24 07:38 ` hubert depesz lubaczewski <depesz@gmail.com>
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
* 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
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