Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rule to fill in value on column on insert

Rule to fill in value on column on insert

From
Bradley Kieser
Date:
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.

In general, though, it would be handy to know how to do this as there 
are many uses for it!

Thanks,

Brad

Re: Rule to fill in value on column on insert

From
"Christopher Kings-Lynne"
Date:
Hi Bradley,

If it's a simple value, then set the column's defaul (ALTER TABLE tab ALTER
col SET DEFAULT blah).  Otherwise, look up CREATE TRIGGER and CREATE RULE in
the docs.

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Bradley Kieser
> Sent: Monday, 10 June 2002 4:26 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Rule to fill in value on column on insert
>
>
> 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.
>
> In general, though, it would be handy to know how to do this as there
> are many uses for it!
>
> Thanks,
>
> Brad
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: Rule to fill in value on column on insert

From
Tom Lane
Date:
Bradley Kieser <brad@kieser.net> 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