--- bres-history.sql 2003/06/05 10:58:37 1.2
+++ bres-history.sql 2003/06/05 12:38:37 1.3
@@ -174,7 +174,7 @@
arg_ut_end := $2; IF arg_ut_end IS NULL THEN arg_ut_end := 9999; END IF;
arg_st_beg := $3; IF arg_st_beg IS NULL THEN arg_st_beg := 0; END IF;
arg_st_end := $4; IF arg_st_end IS NULL THEN arg_st_end := 9999; END IF;
- arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 0; END IF;
+ arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 1; END IF;
-- perform the query operation
IF arg_st_max = 1 THEN
@@ -235,6 +235,7 @@
-- ## Test Data
-- ##
+-- whole test data as view
-- # ^
-- # |
-- # st hid
@@ -246,6 +247,23 @@
-- # 2001 #1 [==10:foo==[ | |
-- # ut--> 100 200 300 9999
+-- whole test data as raw data
+-- # 1st (systime hid=1) create record for oid=10, name="foo", 100 <= usertime < 200
+-- # 2nd (systime hid=2) create record for oid=10, name="bar", 200 <= usertime < 300
+-- # 3rd (systime hid=3) create record for oid=10, name="foo", 100 <= usertime < oo
+-- # 4th (systime hid=4) create record for oid=20, name="bar1", 100 <= usertime < oo
+-- # 5th (systime hid=5) create record for oid=20, name="bar2", 200 <= usertime < oo
+-- # 6th (systime hid=6) create record for oid=20, name="bar3", 200 <= usertime < 300
+
+-- whole test data in prosa
+-- # 1st create a new record "foo" containing payload valid between usertime 100 upto not including 200
+-- # 2nd rename "foo" to "bar" and move usertime frame to 200 upto n. i. 300
+-- # 3rd rename "bar" back to "foo", extend usertime begin back to original 100 and extend usertime end to infinity
+-- # 4th create a new record "bar1" containing payload valid between usertime 100 and infinity
+-- # 5th give "bar1" a new name "bar2" beginning with usertime 200
+-- # 6th another new name "bar3" valid for usertime frame 200 upto n. i. 300
+
+-- whole test data injected
INSERT INTO st (st_oid,st_beg) VALUES (10, 2001);
INSERT INTO ut VALUES (10, 100, 200, currval('st_seq'));
INSERT INTO ac VALUES (10, 'foo', currval('st_seq'));
@@ -279,12 +297,63 @@
select * from ut_no;
select * from st;
--- whole test data (complete history, i.e. data at all hids), PLAIN
+-- whole test data retrieved
SELECT * FROM ac;
+-- # ac_oid | ac_name | ac_hid
+-- # --------+---------+--------
+-- # 10 | foo | 1
+-- # 10 | bar | 2
+-- # 10 | foo | 3
+-- # 20 | bar1 | 4
+-- # 20 | bar2 | 5
+-- # 20 | bar3 | 6
+-- # (6 rows)
+
+-- for any usertime and any systime, list any valid oid for every point in systime (all oids with full history)
+SELECT * FROM as_hs(0,9999,0,9999,0);
+-- # hs_oid | hs_hid
+-- # --------+--------
+-- # 10 | 1
+-- # 10 | 2
+-- # 10 | 3
+-- # 20 | 4
+-- # 20 | 5
+-- # 20 | 6
+-- # (6 rows)
+
+-- for any usertime and any systime, list max valid oid for every point in systime (oids with highest altitude)
+SELECT * FROM as_hs(0,9999,0,9999,1); -- same as SELECT FROM as_hs();
+-- # hs_oid | hs_hid
+-- # --------+--------
+-- # 10 | 3
+-- # 20 | 4
+-- # 20 | 6
+-- # 20 | 5
+-- # (4 rows)
--- whole history data
-SELECT * FROM as_hs();
-
--- whole test data (complete history, i.e. data at all hids), WITH HISTORY
+-- same as previous but join with payload to retrieve more details (details from oids with hightes altitude)
SELECT * FROM ac, as_hs() WHERE ac_hid = hs_hid;
-
+-- # ac_oid | ac_name | ac_hid | hs_oid | hs_hid
+-- # --------+---------+--------+--------+--------
+-- # 10 | foo | 3 | 10 | 3
+-- # 20 | bar1 | 4 | 20 | 4
+-- # 20 | bar2 | 5 | 20 | 5
+-- # 20 | bar3 | 6 | 20 | 6
+-- # (4 rows)
+
+
+-- same as previous but we are only interested in oids valid for usertime point 222
+SELECT * FROM ac, as_hs(222,222,0,9999,1) WHERE ac_hid = hs_hid;
+-- # ac_oid | ac_name | ac_hid | hs_oid | hs_hid
+-- # --------+---------+--------+--------+--------
+-- # 10 | foo | 3 | 10 | 3
+-- # 20 | bar3 | 6 | 20 | 6
+-- # (2 rows)
+
+-- same as previous but we look back in systime 2006
+SELECT * FROM ac, as_hs(222,222,0,2006,1) WHERE ac_hid = hs_hid;
+-- # ac_oid | ac_name | ac_hid | hs_oid | hs_hid
+-- # --------+---------+--------+--------+--------
+-- # 10 | foo | 3 | 10 | 3
+-- # 20 | bar2 | 5 | 20 | 5
+-- # (2 rows)
|
