X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 679F5DAF52 for ; Wed, 23 Nov 2005 05:28:02 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90656-07 for ; Wed, 23 Nov 2005 09:28:03 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by svr1.postgresql.org (Postfix) with ESMTP id A9C35D9792 for ; Wed, 23 Nov 2005 05:27:59 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i28so791327wra for ; Wed, 23 Nov 2005 01:28:01 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=L2K8ZjThZuckW/N1v4ZSxBo4qZCe1dkGP6l3k4/eSa9D34twY+HT1oYBadRBdCM5aeGh+zqko2LQQNMdekhvJQjrlZ0hSAqkSyvhUgxxMnou8WH39QgWOo5mDmxqCAaRPIKOLd9tkEuujxy5BozpWMRGQl7Wvrzb5OT8TKLBmNw= Received: by 10.54.93.3 with SMTP id q3mr3049470wrb; Wed, 23 Nov 2005 01:28:01 -0800 (PST) Received: by 10.54.117.8 with HTTP; Wed, 23 Nov 2005 01:28:01 -0800 (PST) Message-ID: <9e4684ce0511230128y77557be3k2507d6eb1575c1e4@mail.gmail.com> Date: Wed, 23 Nov 2005 10:28:01 +0100 From: hubert depesz lubaczewski To: fmiddleton@verizon.net Subject: Re: freezing a particular field in a table Cc: pgsql-admin@postgresql.org In-Reply-To: <4383A9B7.7060008@verizon.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2877_31579148.1132738081786" References: <4383A9B7.7060008@verizon.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/304 X-Sequence-Number: 19677 ------=_Part_2877_31579148.1132738081786 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 11/23/05, Ferindo Middleton Jr 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 RO= W 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 :=3D OLD.some_field; return NEW; END; will work. depesz ------=_Part_2877_31579148.1132738081786 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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
w= here a particular field cannot be changed when saved initially. I have
<= /blockquote>

CREATE OR REPLACE FUNCTION impossible_to_change() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
        IF
            &nb= sp;   (NEW.some_field IS NULL AND OLD.some_field IS NOT NULL)
            &nb= sp;   OR
            &nb= sp;   (NEW.some_field IS NOT NULL AND OLD.some_field IS NULL)
            &nb= sp;   OR
            &nb= sp;   (NEW.some_field IS NOT NULL AND OLD.some_field IS NOT NULL AND NEW.some_field <> OLD.some_field)
        THEN
            &nb= sp;   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 RO= W 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 :=3D OLD.some_field;
return NEW;
END;

will work.

depesz
------=_Part_2877_31579148.1132738081786--