On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
IS there such a thing? I can be the first to consider this.
What I am aiming for is a solution with a couple coupled tables, one of which represents state through time and the other represents transactions or deltas on the state. With one field (a floating point number) in the state table (or should I say a number for each id field), it obviously has a time interval for which it is valid: a start time and an end time. What I am after is a situation where the moment a record is inserted in the deltas table, a trigger function first looks to see if the id provided presently has a state in the state table. If not, then it creates one. Then, the end date for that state record gets set to the current time and a new record is inserted with the new state (computed by applying the delta to the value in the previous record for the state), the current date as the start date and null for the end date. This seems like an obvious thing to try, but I am floundering a little and am therefore wondering if anyone has seen an SQL design pattern that talks about this, and an url where I can see such a discussion and, better, an example. The first concern is to ensure that every record inserted into the deltas table is immediately reflected in the state table, and the second is that the history of state can be reconstructed from a suitable query on the state table. I can do this easily in client code, but isn't this the sort of thing best suited to living within the database itself?
What you want to do here for handling the update v. insert is called an "UPSERT". Basically, what you do is run the update as if the row exists and catch the exception that is thrown if it doesn't at which point you insert the record with the end date = now(). After that you can proceed normally with creating the new record with start date = now() and end date = NULL.
Erik Jones Software Developer | Emma® erik@xxxxxxxxxx 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
