John Gibson schrieb:
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.
Id use a third table to just store the snapshot times and a sequence number:
CREATE SEQUENCE monitor_snapshots_id_seq;
CREATE TABLE monitor_snapshots (
monitor_time timestamp,
monitor_id int8 DEFAULT nextval('monitor_snapshots_id_seq'::text) NOT NULL
);
and then use the following code to take your snapshots:
INSERT INTO monitor_snapshots (monitor_time) VALUES (now());
INSERT INTO monitor_table
SELECT currval('monitor_snapshots_id_seq'::text) as monitor_id,
fruit_name,
fruit_qty
FROM fruit_table;Provided you modify your monitor_table to have monitor_id, fruit_name (perhaps fruit_id is better here), fruit_qty
If you got a table with fruit_id:fruit_name, you can always retrive your information via LEFT OUTER JOIN, otherwise you would only get the kind of fruits available in the given snapshot.
HTH Tino Wildenhain
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
