By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

Trigger or Rule ?

P: n/a
-----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+0h4EVjIDGyiNuFqKahZXqg CbBLJM
fs79gvQUU+jq+vZ9VJRF5M8=
=xfBy
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.