pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule to fill in value on column on insert
3+ messages / 3 participants
[nested] [flat]
* Rule to fill in value on column on insert
@ 2002-06-10 08:25 Bradley Kieser <brad@kieser.net>
0 siblings, 2 replies; 3+ messages in thread
From: Bradley Kieser @ 2002-06-10 08:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rule to fill in value on column on insert
@ 2002-06-10 08:44 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Bradley Kieser <brad@kieser.net>
1 sibling, 0 replies; 3+ messages in thread
From: Christopher Kings-Lynne @ 2002-06-10 08:44 UTC (permalink / raw)
To: Bradley Kieser <brad@kieser.net>; pgsql-sql
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)
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rule to fill in value on column on insert
@ 2002-06-10 13:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bradley Kieser <brad@kieser.net>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-06-10 13:22 UTC (permalink / raw)
To: Bradley Kieser <brad@kieser.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-06-10 13:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-10 08:25 Rule to fill in value on column on insert Bradley Kieser <brad@kieser.net>
2002-06-10 08:44 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-06-10 13:22 ` Tom Lane <tgl@sss.pgh.pa.us>
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