I have a table which is continually updated with the latest totals. I would like to take snapshots of some of the data in that table and store it in a second table to run statistics on it later. What might some ways of doing this be?
Illustrative (I hope) example using fruit-qty-on-hand at a grocery store:
Fruit_table {constantly updated by other processes}
CREATE TABLE "fruit_table" ( "fruit_name" varchar(20), "fruit_qty" int4 );
***TABLE DATA*** fruit name fruit_qty apple 5 orange 8 pear 3
monitor_table {stores snapshots of fruit table from time to time}
CREATE TABLE "monitor_table" ( "monitor_time" timestamp, "mon_apples_qty" int4, "mon_oranges_qty" int4, "mon_pears_qty" int4 );
I got the following to timestamp a single row from the fruit_table and put the results into the monitor_table:
insert into monitor_table(monitor_time, mon_apples_qty) select now(), fruit_table.fruit_qty where fruit_name = 'apple';
Unfortunately, I am stuck on how to get all three into the monitor table with the same timestamp. Since the times will be relatively long between snapshots some type of variables or functions could be used (I guess) to store the current time ( curr_time := now(); ) and then run the query three times with first an insert and then two updates using the variable time stamp on the updates to locate the record to update.
That doesn't sound very elegant to me. Please help if you have any ideas.
I am definately a newbie, so forgive me if this is trivial. Also, if another forum would be better for this, I would appreciate a nudge in that direction. :)
...john
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
