--- bres-history.sql 2003/06/06 10:09:17 1.7
+++ bres-history.sql 2003/06/06 13:33:43 1.8
@@ -28,6 +28,7 @@
-- [742] (rw)
st_beg TIMESTAMP (4) WITH TIME ZONE
NOT NULL
+ DEFAULT 'now'
-- system time of point in history (not monoton increasing, could backstep)
-- [2002-01-01 00:00:00.0000] (rw)
);
@@ -41,11 +42,13 @@
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
- NOT NULL,
+ NOT NULL
+ DEFAULT 'now',
-- user time begin (inclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_end TIMESTAMP (4) WITH TIME ZONE
- NOT NULL,
+ NOT NULL
+ DEFAULT 'infinity',
-- user time end (exclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_hid BIGINT
@@ -65,11 +68,13 @@
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
- NOT NULL,
+ NOT NULL
+ DEFAULT 'now',
-- user time begin (inclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_end TIMESTAMP (4) WITH TIME ZONE
- NOT NULL,
+ NOT NULL
+ DEFAULT 'infinity',
-- user time end (exclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_hid BIGINT
@@ -211,8 +216,79 @@
EXECUTE PROCEDURE ut_trigger ();
-- ##
+-- ## Make History Tracking
+-- ## hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]])
+-- ##
+
+-- full featured implementation: as_hs_make(oid, ut_beg, ut_end, st_beg)
+CREATE OR REPLACE FUNCTION as_hs_make
+(BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
+RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
+DECLARE
+ var_oid BIGINT;
+ var_ut_beg TIMESTAMP (4) WITH TIME ZONE;
+ var_ut_end TIMESTAMP (4) WITH TIME ZONE;
+ var_st_beg TIMESTAMP (4) WITH TIME ZONE;
+ var_hid BIGINT;
+BEGIN
+ var_oid := $1;
+ var_ut_beg := $2; IF var_ut_beg IS NULL THEN var_ut_beg := now(); END IF;
+ var_ut_end := $3; IF var_ut_end IS NULL THEN var_ut_end := ''infinity''; END IF;
+ var_st_beg := $4; IF var_st_beg IS NULL THEN var_st_beg := now(); END IF;
+ var_hid := NULL;
+ IF var_oid IS NOT NULL THEN
+ -- create new system time entry
+ INSERT INTO as_hs_st (st_oid, st_beg)
+ VALUES (var_oid, var_st_beg);
+ var_hid := currval(''as_hs_st_seq'');
+ -- create new user time entry
+ INSERT INTO as_hs_ut (ut_oid, ut_beg, ut_end, ut_hid)
+ VALUES (var_oid, var_ut_beg, var_ut_end, var_hid);
+ ELSE
+ -- return just the current/maximum history id
+ var_hid := currval(''as_hs_st_seq'');
+ END IF;
+ RETURN var_hid;
+END;
+';
+
+-- overloading: as_hs_make(oid, ut_beg, ut_end)
+CREATE OR REPLACE FUNCTION as_hs_make
+(BIGINT, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
+RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
+BEGIN
+ RETURN as_hs_make($1, $2, $3, NULL);
+END;
+';
+
+-- overloading: as_hs_make(oid, ut_beg)
+CREATE OR REPLACE FUNCTION as_hs_make
+(BIGINT, TIMESTAMP (4) WITH TIME ZONE)
+RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
+BEGIN
+ RETURN as_hs_make($1, $2, NULL, NULL);
+END;
+';
+
+-- overloading: as_hs_make(oid)
+CREATE OR REPLACE FUNCTION as_hs_make(BIGINT)
+RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
+BEGIN
+ RETURN as_hs_make($1, NULL, NULL, NULL);
+END;
+';
+
+-- overloading: as_hs_make()
+CREATE OR REPLACE FUNCTION as_hs_make()
+RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
+BEGIN
+ RETURN as_hs_make(NULL, NULL, NULL, NULL);
+END;
+';
+
+-- ##
-- ## Dynamic History Table
--- ## <oid, hid-max>* := as_hs(ut_beg, ut_end[, st_beg, st_end[, st_max]])
+-- ## <oid, hid-max>* := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
-- ##
-- This finds (optionally only maximum) oid/hid combinations between
@@ -289,6 +365,14 @@
SELECT * FROM as_hs($1, $2, $3, $4, NULL);
';
+-- convinience overloaded function: without "st_end"
+CREATE OR REPLACE FUNCTION as_hs
+(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
+ TIMESTAMP (4) WITH TIME ZONE)
+RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
+ SELECT * FROM as_hs($1, $2, $3, NULL, NULL);
+';
+
-- convinience overloaded function: without "as_hs_st" at all
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
@@ -296,6 +380,13 @@
SELECT * FROM as_hs($1, $2, NULL, NULL, NULL);
';
+-- convinience overloaded function: without "ut_end" at all
+CREATE OR REPLACE FUNCTION as_hs
+(TIMESTAMP (4) WITH TIME ZONE)
+RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
+ SELECT * FROM as_hs($1, NULL, NULL, NULL, NULL);
+';
+
-- convinience overloaded function: without "as_hs_ut" and "as_hs_st" at all
CREATE OR REPLACE FUNCTION as_hs ()
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
|
