Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA15587 for ; Wed, 16 Feb 2000 09:40:47 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by clio.trends.ca (8.9.3+Sun/8.9.3) with ESMTP id JAA13163 for ; Wed, 16 Feb 2000 09:37:06 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id JAA02555; Wed, 16 Feb 2000 09:36:56 -0500 (EST) To: Robin Keech cc: "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Auto increment field when updating? In-reply-to: <31262893C5E5D2119C4900105A30B3A112D593@roadrunner.dialogue.net> References: <31262893C5E5D2119C4900105A30B3A112D593@roadrunner.dialogue.net> Comments: In-reply-to Robin Keech message dated "Wed, 16 Feb 2000 11:08:28 +0000" Date: Wed, 16 Feb 2000 09:36:56 -0500 Message-ID: <2552.950711816@sss.pgh.pa.us> From: Tom Lane Robin Keech writes: > ... Only some fields are written to each time the row is updated. > There is one field called 'count' that ONLY needs to increment every > time that particular row AND the field 'count' are updated. > I know that I can select the information, increment it and update it > back again, but is there any way of setting up the database to do this > automatically? I am using PostgreSQL 6.5. Sure, use a trigger. Here's one I use for a table that wants to attach a globally new sequence number to each created or updated row: CREATE FUNCTION my_phistory_trigger() RETURNS OPAQUE AS ' BEGIN NEW.sequenceNo := nextval(''my_phistory_Seq''); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER my_phistory_trigger BEFORE INSERT OR UPDATE ON my_phistory FOR EACH ROW EXECUTE PROCEDURE my_phistory_trigger(); but if you just want to increment the number that's there, you could do NEW.sequenceNo := OLD.sequenceNo + 1; (bearing in mind that this'll only work as an UPDATE trigger, not INSERT). See the programmer's guide for doco on trigger functions. regards, tom lane