Hi, all.
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
