-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi everybody,
I have tables like
create table xx (
id int4,
col1 whatever,
col2 whatever,
col3 whatever,
valid_from date,
valid_thru date);
where the primary key is always (id, valid_from).
Now my software has a routine that checks on update if
there is a currently valid row (valid_from <= current_date < valid_thru) with
a valid_from matching the valid_from < today
If so, the currently valid row is updated with valid_thru = yesterday.
The new record then is inserted (instead of updated) with a valid_from of
today and a valid_thru of some date far in the future ('9999-01-01')
If there is a currently valid record with valid_from = today, then a normal
update is performed.
In the end this produces a record history with a granularity of one day.
Since I perform a LOT of these calls (sometimes about 1000 such "update
queries" at a time) I thought it might be a great idea to push this
checking/updating/inserting into the database, instead of performing that
outside from the application side.
Questions:
a) Which would be better (faster, less expensive): Trigger or Rule ?
b) If Trigger, how can I avoid that the trigger triggers itself (in the end it
performs an update to the same table)
c) How can I avoid to create explicit updates/inserts for this, i.e. I don't
want to to a "insert into xx (id,col1,col2,col3,valid_from,valid_thru) values
(new.id,new.col1,new.col2 .........", instead I'd like to create a function
that gets a tablename and rowset with the new data and creates the insert
statement itself. Then it could be applied to any table matching the above
schema.
My bet would be a trigger, since it calls a function directly, however then I
obviously can't control the parameters given to the function.
Any help is greatly appreciated.
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/odp2jqGXBvRToM4RAk9RAJ9qdjG+0h4EVjIDGyiNuFqKahZXqgCbBLJM
fs79gvQUU+jq+vZ9VJRF5M8=
=xfBy
-----END PGP SIGNATURE-----
