Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 4B1AB476BBC for ; Mon, 10 Jun 2002 09:32:15 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 63200475C77 for ; Mon, 10 Jun 2002 09:23:35 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g5ADMTb05411; Mon, 10 Jun 2002 09:22:29 -0400 (EDT) To: Bradley Kieser Cc: pgsql-sql@postgresql.org Subject: Re: Rule to fill in value on column on insert In-reply-to: <3D046290.2060906@kieser.net> References: <3D046290.2060906@kieser.net> Comments: In-reply-to Bradley Kieser message dated "Mon, 10 Jun 2002 09:25:52 +0100" Date: Mon, 10 Jun 2002 09:22:28 -0400 Message-ID: <5408.1023715348@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200206/109 X-Sequence-Number: 8074 Bradley Kieser writes: > How do I create a rule that will set a column to a particular value upon > insert? It's for use within an audit trail and we want to prevent any > possibility of some rogue code setting it to an incorrect value (it's a > time stamp) so we don't want to use default values. Use a trigger function, not a rule. A "BEFORE INSERT" trigger can do this trivially, eg: NEW.insertiontime := now(); return NEW; You'll probably want a BEFORE UPDATE trigger as well, to prevent later changes: NEW.insertiontime := OLD.insertiontime; return NEW; or if you want to update the column during updates, you could actually share the first trigger for both purposes. regards, tom lane