head 1.14;
access;
symbols;
locks; strict;
comment @# @;
1.14
date 2004.02.17.09.18.42; author thl; state Exp;
branches;
next 1.13;
1.13
date 2003.06.08.15.09.38; author rse; state Exp;
branches;
next 1.12;
1.12
date 2003.06.08.15.08.54; author rse; state Exp;
branches;
next 1.11;
1.11
date 2003.06.08.15.07.47; author rse; state Exp;
branches;
next 1.10;
1.10
date 2003.06.08.14.50.02; author rse; state Exp;
branches;
next 1.9;
1.9
date 2003.06.06.13.40.46; author rse; state Exp;
branches;
next 1.8;
1.8
date 2003.06.06.13.33.43; author rse; state Exp;
branches;
next 1.7;
1.7
date 2003.06.06.10.09.17; author rse; state Exp;
branches;
next 1.6;
1.6
date 2003.06.06.09.46.27; author rse; state Exp;
branches;
next 1.5;
1.5
date 2003.06.05.12.45.55; author rse; state Exp;
branches;
next 1.4;
1.4
date 2003.06.05.12.43.21; author rse; state Exp;
branches;
next 1.3;
1.3
date 2003.06.05.12.38.37; author thl; state Exp;
branches;
next 1.2;
1.2
date 2003.06.05.10.58.37; author rse; state Exp;
branches;
next 1.1;
1.1
date 2003.05.29.10.11.15; author rse; state Exp;
branches;
next ;
desc
@@
1.14
log
@correct spelling: privileges, convenient;
@
text
@--
-- OSSP as -- Accounting System
-- Copyright (c) 2002-2003 Cable & Wireless Deutschland
-- Copyright (c) 2002-2003 Ralf S. Engelschall
--
-- This file is part of OSSP as, an accounting system which can be
-- found at http://www.ossp.org/pkg/tool/as/
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version
-- 2.0 of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
-- USA, or contact The OSSP Project .
--
-- bres-history.sql -- History Tracking Basic Research
--
-- TODO:
-- - delete entries from as_hs_ut for expiring history: does it still not correctly
-- and especially should only allow the oldest entry to be deleted or implicitly
-- delete also all older entries.
-- ## _____________________________________________________________________
-- ##
-- ## Database Schema
-- ## _____________________________________________________________________
-- ##
-- cascadingly drop existing schema
-- (just ignore errors on console)
DROP SEQUENCE as_hs_st_seq CASCADE;
DROP TABLE as_hs_st CASCADE;
DROP TABLE as_hs_ut CASCADE;
DROP TABLE as_hs_ut_no CASCADE;
DROP TABLE as_account CASCADE;
-- System Time Sequence
CREATE SEQUENCE as_hs_st_seq
INCREMENT 1 MINVALUE 1 START 1;
-- System Time Table
CREATE TABLE as_hs_st (
st_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid) FIXME
-- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
st_hid BIGINT
UNIQUE NOT NULL
DEFAULT nextval('as_hs_st_seq'),
-- history id associated with system time (discrete monoton increasing)
-- [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)
);
-- User Time Table
CREATE TABLE as_hs_ut (
ut_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid) FIXME
-- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
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
DEFAULT 'infinity',
-- user time end (exclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_hid BIGINT
NOT NULL
REFERENCES as_hs_st (st_hid)
MATCH FULL DEFERRABLE
-- user time history identifier
-- [42] (rw)
);
-- User Time Table (non-overlapping)
CREATE TABLE as_hs_ut_no (
ut_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid) FIXME
-- MATCH FULL DEFERRABLE, FIXME
-- object identifier
-- [1] (rw)
ut_beg TIMESTAMP (4) WITH TIME ZONE
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
DEFAULT 'infinity',
-- user time end (exclusive)
-- [2002-01-01 00:00:00.0000] (rw)
ut_hid BIGINT
NOT NULL
REFERENCES as_hs_st (st_hid)
MATCH FULL DEFERRABLE,
-- user time history identifier
-- [42] (rw)
ut_hid_max BIGINT
NOT NULL
-- REFERENCES as_hs_st (st_hid) FIXME
-- MATCH FULL DEFERRABLE FIXME
-- user time history identifier (maximum value until which this entry is valid)
-- [42] (rw)
);
-- Account Table
CREATE TABLE as_account(
ac_oid BIGINT
NOT NULL,
-- REFERENCES as_oid (id_oid)
-- MATCH FULL DEFERRABLE,
-- object identifier
-- [1] (rw)
ac_name TEXT
NOT NULL,
-- name of account
-- [foo] (rw)
ac_hid BIGINT
NOT NULL
REFERENCES as_hs_st (st_hid)
MATCH FULL DEFERRABLE
-- account history identifier
-- [42] (rw)
);
-- ## _____________________________________________________________________
-- ##
-- ## Automated Update of as_hs_ut_no Table
-- ## _____________________________________________________________________
-- ##
-- as_hs_ut update function
DROP FUNCTION as_hs_ut_update(BIGINT, BIGINT) CASCADE;
CREATE FUNCTION as_hs_ut_update(BIGINT, BIGINT)
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
oid BIGINT;
hid BIGINT;
pos_prev TIMESTAMP (4) WITH TIME ZONE;
pos_this TIMESTAMP (4) WITH TIME ZONE;
pos_next TIMESTAMP (4) WITH TIME ZONE;
alt_this BIGINT;
alt_prev BIGINT;
BEGIN
oid := $1;
hid := $2;
-- delete all old entries corresponding to OID
DELETE FROM as_hs_ut_no WHERE ut_oid = oid AND ut_hid_max = hid;
-- initialize iteration variables
pos_prev := ''-infinity'';
pos_this := ''-infinity'';
pos_next := NULL;
alt_this := NULL;
alt_prev := NULL;
LOOP
-- determine next position from existing begin/end positions
pos_next := (
SELECT min(v) FROM (
(SELECT min(ut_beg) FROM as_hs_ut WHERE ut_beg > pos_this AND ut_oid = oid AND ut_hid <= hid)
UNION (SELECT min(ut_end) FROM as_hs_ut WHERE ut_end > pos_this AND ut_oid = oid AND ut_hid <= hid)
) AS result (v)
);
IF pos_next IS NULL THEN
pos_next := ''infinity'';
END IF;
-- determine current altidude
alt_this := (
SELECT max(ut_hid) FROM as_hs_ut
WHERE ut_beg <= pos_this AND pos_this < ut_end AND ut_oid = oid AND ut_hid <= hid
);
IF alt_this IS NULL THEN
alt_this := -1;
END IF;
-- RAISE NOTICE ''pos_this = %, pos_next = %, alt_this = %'', pos_this, pos_next, alt_this;
IF alt_this = -1 THEN
-- RAISE NOTICE ''hit ground - pruning entry'';
ELSE
IF alt_this = alt_prev THEN
-- RAISE NOTICE ''keeping altitude - consolidating entry'';
UPDATE as_hs_ut_no SET ut_end = pos_next
WHERE ut_hid = alt_this AND ut_beg = pos_prev AND ut_end = pos_this AND ut_oid = oid AND ut_hid_max = hid;
ELSE
-- RAISE NOTICE ''changing altitude - new entry'';
INSERT INTO as_hs_ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_hid_max)
VALUES (alt_this, oid, pos_this, pos_next, hid);
pos_prev := pos_this;
END IF;
END IF;
-- stop if maximum is reached
IF pos_next >= ''infinity'' THEN
EXIT;
END IF;
-- move one step
pos_this := pos_next;
alt_prev := alt_this;
END LOOP;
RETURN;
END;
';
-- as_hs_ut trigger stub function
DROP FUNCTION ut_trigger() CASCADE;
CREATE FUNCTION ut_trigger()
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
BEGIN
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
PERFORM as_hs_ut_update(NEW.ut_oid, NEW.ut_hid);
ELSIF TG_OP = ''DELETE'' THEN
PERFORM as_hs_ut_update(OLD.ut_oid, OLD.ut_hid);
END IF;
RETURN NULL;
END;
';
-- as_hs_ut trigger
--DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
CREATE TRIGGER ut_trigger
AFTER INSERT OR UPDATE OR DELETE
ON as_hs_ut FOR EACH ROW
EXECUTE PROCEDURE ut_trigger ();
-- ## _____________________________________________________________________
-- ##
-- ## Convinience History Tracking Constructor/Destructor Functions
-- ## hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]]);
-- ## as_hs_delete(oid, st_hid);
-- ## _____________________________________________________________________
-- ##
-- hid := 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;
';
-- convenience function overloading
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;
';
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;
';
CREATE OR REPLACE FUNCTION as_hs_make(BIGINT)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make($1, NULL, NULL, NULL);
END;
';
CREATE OR REPLACE FUNCTION as_hs_make()
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
BEGIN
RETURN as_hs_make(NULL, NULL, NULL, NULL);
END;
';
-- as_hs_delete(oid, st_hid);
CREATE OR REPLACE FUNCTION as_hs_delete (BIGINT, BIGINT)
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
var_oid BIGINT;
var_hid BIGINT;
BEGIN
var_oid := $1;
var_hid := $2;
DELETE FROM as_hs_ut WHERE ut_oid = var_oid AND ut_hid = var_hid;
DELETE FROM as_hs_st WHERE st_oid = var_oid AND st_hid = var_hid;
RETURN;
END;
';
-- ## _____________________________________________________________________
-- ##
-- ## Parametrized History Table View
-- ## * := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
-- ## _____________________________________________________________________
-- ##
-- This finds (optionally only maximum) oid/hid combinations between
-- st_beg and st_end (both inclusive) system time, for which the
-- user time touches the time range between ut_beg and ut_end (both
-- inclusive).
-- parametrized view return type
DROP TYPE as_hs_t CASCADE;
CREATE TYPE as_hs_t AS (
hs_oid BIGINT,
hs_hid BIGINT
);
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, INTEGER)
RETURNS SETOF as_hs_t LANGUAGE 'plpgsql' STABLE AS '
DECLARE
arg_ut_beg TIMESTAMP (4) WITH TIME ZONE; -- user time begin (including)
arg_ut_end TIMESTAMP (4) WITH TIME ZONE; -- user time end (including)
arg_st_beg TIMESTAMP (4) WITH TIME ZONE; -- system time begin (including)
arg_st_end TIMESTAMP (4) WITH TIME ZONE; -- system time end (including)
arg_st_max INTEGER; -- whether to take only maximum system time into account
rec RECORD;
BEGIN
-- pre-process input arguments by providing reasonable defaults
arg_ut_beg := $1; IF arg_ut_beg IS NULL THEN arg_ut_beg := ''-infinity''; END IF;
arg_ut_end := $2; IF arg_ut_end IS NULL THEN arg_ut_end := ''infinity''; END IF;
arg_st_beg := $3; IF arg_st_beg IS NULL THEN arg_st_beg := ''-infinity''; END IF;
arg_st_end := $4; IF arg_st_end IS NULL THEN arg_st_end := ''infinity''; 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
-- variant 1: take only maximum system time for each user time and oid
FOR rec IN
SELECT ut_oid, ut_hid
FROM as_hs_ut_no, (
SELECT st_oid, max(st_hid) AS st_hid
FROM as_hs_st
WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
GROUP BY st_oid
) AS as_hs_st
WHERE
ut_oid = st_oid AND ut_hid_max = st_hid
AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end)
LOOP
RETURN NEXT rec;
END LOOP;
ELSE
-- variant 2: take all system times for each user time and oid
FOR rec IN
SELECT DISTINCT ut_oid, ut_hid
FROM as_hs_ut_no, (
SELECT st_oid, st_hid
FROM as_hs_st
WHERE (arg_st_beg <= st_beg AND st_beg <= arg_st_end)
) AS as_hs_st
WHERE
ut_oid = st_oid AND ut_hid_max = st_hid
AND (ut_beg <= arg_ut_end AND arg_ut_beg < ut_end)
LOOP
RETURN NEXT rec;
END LOOP;
END IF;
RETURN;
END;
';
-- convenience overloading functions
CREATE OR REPLACE FUNCTION as_hs
(TIMESTAMP (4) WITH TIME ZONE, 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, $4, NULL);
';
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);
';
CREATE OR REPLACE FUNCTION as_hs
(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, NULL, NULL, NULL);
';
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);
';
CREATE OR REPLACE FUNCTION as_hs ()
RETURNS SETOF as_hs_t LANGUAGE 'sql' STABLE AS '
SELECT * FROM as_hs(NULL, NULL, NULL, NULL, NULL);
';
-- ## _____________________________________________________________________
-- ##
-- ## Account Table Convinience Manipulation Triggers
-- ## _____________________________________________________________________
-- ##
-- as_account trigger function
CREATE OR REPLACE FUNCTION as_account_trigger()
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
DECLARE
hid INTEGER;
BEGIN
IF TG_WHEN = ''BEFORE'' AND TG_OP = ''INSERT'' THEN
IF new.ac_hid IS NULL THEN
-- auto-generate history id if not explicitly set by user
new.ac_hid := as_hs_make(new.ac_oid);
END IF;
RETURN new;
ELSIF TG_WHEN = ''BEFORE'' AND TG_OP = ''UPDATE'' THEN
-- clone old entry to allow old entry to be overwritten as it would be new entry
INSERT INTO as_account VALUES (old.ac_oid, old.ac_name, old.ac_hid);
-- give new entry a new history id if not explicity set by user
IF (new.ac_hid = old.ac_hid) THEN
new.ac_hid := as_hs_make(new.ac_oid);
END IF;
RETURN new;
ELSIF TG_WHEN = ''AFTER'' AND TG_OP = ''DELETE'' THEN
-- delete corresponding history entries
PERFORM as_hs_delete(old.ac_oid, old.ac_hid);
RETURN old;
END IF;
END;
';
-- as_account triggers
CREATE TRIGGER as_account_trigger_before
BEFORE INSERT OR UPDATE ON as_account FOR EACH ROW
EXECUTE PROCEDURE as_account_trigger();
CREATE TRIGGER as_account_trigger_after
AFTER DELETE ON as_account FOR EACH ROW
EXECUTE PROCEDURE as_account_trigger();
-- ## _____________________________________________________________________
-- ##
-- ## Parametrized Account Table View
-- ## * := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
-- ## _____________________________________________________________________
-- ##
CREATE OR REPLACE FUNCTION as_account ()
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs() WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4) WHERE ac_hid = hs_hid;
';
CREATE OR REPLACE FUNCTION as_account
(TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE,
TIMESTAMP (4) WITH TIME ZONE, TIMESTAMP (4) WITH TIME ZONE, INTEGER)
RETURNS SETOF as_account LANGUAGE 'sql' STABLE AS '
SELECT ac.* FROM as_account ac, as_hs($1, $2, $3, $4, $5) WHERE ac_hid = hs_hid;
';
-- ## _____________________________________________________________________
-- ##
-- ## Test Data
-- ## _____________________________________________________________________
-- ##
-- whole test data as view
-- # ^ ^
-- # | |
-- # as_hs_st hid | | | |
-- # 2007-01-01 #6 | [==20:bar3=[ |
-- # 2006-01-01 #5 | [==20:bar2===========[
-- # 2005-01-01 #4 [=============20:bar1===========[
-- # 2004-01-01 #3 [=============10:foo============[
-- # 2002-01-01 #2 | [==10:bar==[ |
-- # 2001-01-01 #1 [==10:foo==[ | |
-- # as_hs_ut--> 0100-01-01 0200-01-01 0300-01-01 infinity
-- 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 as_account VALUES (10, 'foo', as_hs_make(10, '0100-01-01', '0200-01-01', '2001-01-01'));
INSERT INTO as_account VALUES (10, 'bar', as_hs_make(10, '0200-01-01', '0300-01-01', '2002-01-01'));
INSERT INTO as_account VALUES (10, 'foo', as_hs_make(10, '0100-01-01', 'infinity', '2004-01-01'));
INSERT INTO as_account VALUES (20, 'bar1', as_hs_make(20, '0100-01-01', 'infinity', '2005-01-01'));
INSERT INTO as_account VALUES (20, 'bar2', as_hs_make(20, '0200-01-01', 'infinity', '2006-01-01'));
INSERT INTO as_account VALUES (20, 'bar3', as_hs_make(20, '0200-01-01', '0300-01-01', '2007-01-01'));
-- ## _____________________________________________________________________
-- ##
-- ## Test Data Dumping
-- ## _____________________________________________________________________
-- ##
select * from as_account;
-- # 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)
select * from as_hs_ut;
-- # ut_oid | ut_beg | ut_end | ut_hid
-- # --------+---------------------+---------------------+--------
-- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1
-- # 10 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 2
-- # 10 | 0100-01-01 00:00:00 | infinity | 3
-- # 20 | 0100-01-01 00:00:00 | infinity | 4
-- # 20 | 0200-01-01 00:00:00 | infinity | 5
-- # 20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 6
-- # (6 rows)
select * from as_hs_ut_no;
-- # ut_oid | ut_beg | ut_end | ut_hid | ut_hid_max
-- # --------+---------------------+---------------------+--------+------------
-- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1 | 1
-- # 10 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 1 | 2
-- # 10 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 2 | 2
-- # 10 | 0100-01-01 00:00:00 | infinity | 3 | 3
-- # 20 | 0100-01-01 00:00:00 | infinity | 4 | 4
-- # 20 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 4 | 5
-- # 20 | 0200-01-01 00:00:00 | infinity | 5 | 5
-- # 20 | 0100-01-01 00:00:00 | 0200-01-01 00:00:00 | 4 | 6
-- # 20 | 0200-01-01 00:00:00 | 0300-01-01 00:00:00 | 6 | 6
-- # 20 | 0300-01-01 00:00:00 | infinity | 5 | 6
-- # (10 rows)
select * from as_hs_st;
-- # st_oid | st_hid | st_beg
-- # --------+--------+------------------------
-- # 10 | 1 | 2001-01-01 00:00:00+01
-- # 10 | 2 | 2002-01-01 00:00:00+01
-- # 10 | 3 | 2004-01-01 00:00:00+01
-- # 20 | 4 | 2005-01-01 00:00:00+01
-- # 20 | 5 | 2006-01-01 00:00:00+01
-- # 20 | 6 | 2007-01-01 00:00:00+01
-- # (6 rows)
-- ##
-- ## Test Usage
-- ##
-- whole test data retrieved
SELECT * FROM as_account;
-- # 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('-infinity','infinity','-infinity','infinity',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('-infinity','infinity','-infinity','infinity',1); -- same as SELECT FROM as_hs();
-- # hs_oid | hs_hid
-- # --------+--------
-- # 10 | 3
-- # 20 | 4
-- # 20 | 6
-- # 20 | 5
-- # (4 rows)
-- same as previous but join with payload to retrieve more details (details from oids with hightes altitude)
SELECT * FROM as_account, 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 as_account, as_hs('0222-01-01','0222-01-01','-infinity','infinity',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 as_account, as_hs('0222-01-01','0222-01-01','-infinity','2006-01-01',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)
@
1.13
log
@get working after cleanup again
@
text
@d292 1
a292 1
-- convinience function overloading
d409 1
a409 1
-- convinience overloading functions
@
1.12
log
@simplify syntax
@
text
@d237 1
a237 1
PERFORM ut_update(NEW.ut_oid, NEW.ut_hid);
d239 1
a239 1
PERFORM ut_update(OLD.ut_oid, OLD.ut_hid);
@
1.11
log
@cleanup the syntax
@
text
@d455 1
a455 2
END IF;
IF TG_WHEN = ''BEFORE'' AND TG_OP = ''UPDATE'' THEN
d463 1
a463 2
END IF;
IF TG_WHEN = ''AFTER'' AND TG_OP = ''DELETE'' THEN
@
1.10
log
@add as_account trigger and dynamic view
@
text
@d1 36
a36 7
-- TODO: delete entries from as_hs_ut for expiring history: does it still not correctly
-- and especially should only allow the oldest entry to be deleted or implicitly
-- delete also all older entries.
-- ##
-- ## Create Database Schema
-- ##
d38 2
d54 2
a55 2
-- REFERENCES as_oid (id_oid)
-- MATCH FULL DEFERRABLE,
d74 2
a75 2
-- REFERENCES as_oid (id_oid)
-- MATCH FULL DEFERRABLE,
d100 2
a101 2
-- REFERENCES as_oid (id_oid)
-- MATCH FULL DEFERRABLE,
d122 2
a123 2
-- REFERENCES as_hs_st (st_hid)
-- MATCH FULL DEFERRABLE
d148 9
a156 7
-- ##
-- ## Update as_hs_ut_no table for as_hs_ut change on particular OID
-- ##
-- Update function
DROP FUNCTION ut_update(BIGINT, BIGINT) CASCADE;
CREATE FUNCTION ut_update(BIGINT, BIGINT)
d231 1
a231 1
-- Trigger Stub Function
d238 2
a239 4
ELSE
IF TG_OP = ''DELETE'' THEN
PERFORM ut_update(OLD.ut_oid, OLD.ut_hid);
END IF;
d245 2
a246 2
-- Global Trigger
-- DROP TRIGGER ut_trigger ON as_hs_ut CASCADE;
d252 7
a258 4
-- ##
-- ## Make History Tracking
-- ## hid := as_hs_make([oid[, ut_beg, ut_end[, st_beg]]])
-- ##
d260 1
a260 1
-- full featured implementation: as_hs_make(oid, ut_beg, ut_end, st_beg)
d292 1
a292 16
-- full featured implementation: as_hs_delete(oid, st_hid)
CREATE OR REPLACE FUNCTION as_hs_delete (BIGINT, BIGINT)
RETURNS VOID LANGUAGE 'plpgsql' STABLE AS '
DECLARE
var_oid BIGINT;
var_hid BIGINT;
BEGIN
var_oid := $1;
var_hid := $2;
DELETE FROM as_hs_ut WHERE ut_oid = var_oid AND ut_hid = var_hid;
DELETE FROM as_hs_st WHERE st_oid = var_oid AND st_hid = var_hid;
RETURN;
END;
';
-- overloading: as_hs_make(oid, ut_beg, ut_end)
a299 2
-- overloading: as_hs_make(oid, ut_beg)
a306 2
-- overloading: as_hs_make(oid)
a312 2
-- overloading: as_hs_make()
d320 26
a345 9
-- ##
-- ## Dynamic History Table
-- ## * := as_hs(ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]])
-- ##
-- This finds (optionally only maximum) oid/hid combinations between
-- st_beg and st_end (both inclusive) system time, for which the
-- user time touches the time range between ut_beg and ut_end (both
-- inclusive).
d347 1
d409 1
a409 1
-- convinience overloaded function: without "st_max"
a415 2
-- convinience overloaded function: without "st_end"
a421 2
-- convinience overloaded function: without "as_hs_st" at all
a426 2
-- convinience overloaded function: without "ut_end" at all
a431 2
-- convinience overloaded function: without "as_hs_ut" and "as_hs_st" at all
d437 5
a441 3
-- ##
-- ## Account Table Convinience Manipulation Triggers
-- ##
d473 1
a473 1
-- as_account triggers
d481 6
a486 4
-- ##
-- ## Account Table Dynamic View
-- ## * := as_account([ut_beg[, ut_end[, st_beg[, st_end[, st_max]]]]])
-- ##
d515 5
a519 4
-- ##
-- ## Test Data
-- ##
d557 5
a561 3
-- ##
-- ## Data Dump
-- ##
@
1.9
log
@greatly simplify creation of sample data by using the new as_hs_make() constructor
@
text
@d1 3
d91 2
a92 2
REFERENCES as_hs_st (st_hid)
MATCH FULL DEFERRABLE
d110 1
a110 1
NOT NULL
d258 15
d413 75
@
1.8
log
@add as_hs_make() constructor function
@
text
@d429 6
a434 23
INSERT INTO as_hs_st (st_oid,st_beg) VALUES (10, '2001-01-01');
INSERT INTO as_hs_ut VALUES (10, '0100-01-01', '0200-01-01', currval('as_hs_st_seq'));
INSERT INTO as_account VALUES (10, 'foo', currval('as_hs_st_seq'));
INSERT INTO as_hs_st (st_oid,st_beg) VALUES (10, '2002-01-01');
INSERT INTO as_hs_ut VALUES (10, '0200-01-01', '0300-01-01', currval('as_hs_st_seq'));
INSERT INTO as_account VALUES (10, 'bar', currval('as_hs_st_seq'));
INSERT INTO as_hs_st (st_oid,st_beg) VALUES (10, '2004-01-01');
INSERT INTO as_hs_ut VALUES (10, '0100-01-01', 'infinity', currval('as_hs_st_seq'));
INSERT INTO as_account VALUES (10, 'foo', currval('as_hs_st_seq'));
INSERT INTO as_hs_st (st_oid,st_beg) VALUES (20, '2005-01-01');
INSERT INTO as_hs_ut VALUES (20, '0100-01-01', 'infinity', currval('as_hs_st_seq'));
INSERT INTO as_account VALUES (20, 'bar1', currval('as_hs_st_seq'));
INSERT INTO as_hs_st (st_oid,st_beg) VALUES (20, '2006-01-01');
INSERT INTO as_hs_ut VALUES (20, '0200-01-01', 'infinity', currval('as_hs_st_seq'));
INSERT INTO as_account VALUES (20, 'bar2', currval('as_hs_st_seq'));
INSERT INTO as_hs_st (st_oid,st_beg) VALUES (20, '2007-01-01');
INSERT INTO as_hs_ut VALUES (20, '0200-01-01', '0300-01-01', currval('as_hs_st_seq'));
INSERT INTO as_account VALUES (20, 'bar3', currval('as_hs_st_seq'));
@
1.7
log
@use also the final table names for as_db.sql
@
text
@d31 1
d45 2
a46 1
NOT NULL,
d50 2
a51 1
NOT NULL,
d71 2
a72 1
NOT NULL,
d76 2
a77 1
NOT NULL,
d219 71
d291 1
a291 1
-- ## * := as_hs(ut_beg, ut_end[, st_beg, st_end[, st_max]])
d368 8
d381 7
@
1.6
log
@finally convert from INTEGER to TIMESTAMP and cleanup the schema definition to closely look like the version we want in as_db.sql
@
text
@d6 5
a10 5
DROP SEQUENCE st_seq CASCADE;
DROP TABLE st CASCADE;
DROP TABLE ut CASCADE;
DROP TABLE ut_no CASCADE;
DROP TABLE ac CASCADE;
d13 1
a13 1
CREATE SEQUENCE st_seq
d17 1
a17 1
CREATE TABLE st (
d26 1
a26 1
DEFAULT nextval('st_seq'),
d36 1
a36 1
CREATE TABLE ut (
d53 1
a53 1
REFERENCES st (st_hid)
d60 1
a60 1
CREATE TABLE ut_no (
d77 1
a77 1
REFERENCES st (st_hid)
d83 1
a83 1
REFERENCES st (st_hid)
d90 1
a90 1
CREATE TABLE ac(
d103 1
a103 1
REFERENCES st (st_hid)
d110 1
a110 1
-- ## Update ut_no table for ut change on particular OID
d130 1
a130 1
DELETE FROM ut_no WHERE ut_oid = oid AND ut_hid_max = hid;
d143 2
a144 2
(SELECT min(ut_beg) FROM ut WHERE ut_beg > pos_this AND ut_oid = oid AND ut_hid <= hid)
UNION (SELECT min(ut_end) FROM ut WHERE ut_end > pos_this AND ut_oid = oid AND ut_hid <= hid)
d153 1
a153 1
SELECT max(ut_hid) FROM ut
d167 1
a167 1
UPDATE ut_no SET ut_end = pos_next
d171 1
a171 1
INSERT INTO ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_hid_max)
d207 1
a207 1
-- DROP TRIGGER ut_trigger ON ut CASCADE;
d210 1
a210 1
ON ut FOR EACH ROW
d252 1
a252 1
FROM ut_no, (
d254 1
a254 1
FROM st
d257 1
a257 1
) AS st
d268 1
a268 1
FROM ut_no, (
d270 1
a270 1
FROM st
d272 1
a272 1
) AS st
d292 1
a292 1
-- convinience overloaded function: without "st" at all
d299 1
a299 1
-- convinience overloaded function: without "ut" and "st" at all
d312 1
a312 1
-- # st hid | | | |
d319 1
a319 1
-- # ut--> 0100-01-01 0200-01-01 0300-01-01 infinity
d338 23
a360 23
INSERT INTO st (st_oid,st_beg) VALUES (10, '2001-01-01');
INSERT INTO ut VALUES (10, '0100-01-01', '0200-01-01', currval('st_seq'));
INSERT INTO ac VALUES (10, 'foo', currval('st_seq'));
INSERT INTO st (st_oid,st_beg) VALUES (10, '2002-01-01');
INSERT INTO ut VALUES (10, '0200-01-01', '0300-01-01', currval('st_seq'));
INSERT INTO ac VALUES (10, 'bar', currval('st_seq'));
INSERT INTO st (st_oid,st_beg) VALUES (10, '2004-01-01');
INSERT INTO ut VALUES (10, '0100-01-01', 'infinity', currval('st_seq'));
INSERT INTO ac VALUES (10, 'foo', currval('st_seq'));
INSERT INTO st (st_oid,st_beg) VALUES (20, '2005-01-01');
INSERT INTO ut VALUES (20, '0100-01-01', 'infinity', currval('st_seq'));
INSERT INTO ac VALUES (20, 'bar1', currval('st_seq'));
INSERT INTO st (st_oid,st_beg) VALUES (20, '2006-01-01');
INSERT INTO ut VALUES (20, '0200-01-01', 'infinity', currval('st_seq'));
INSERT INTO ac VALUES (20, 'bar2', currval('st_seq'));
INSERT INTO st (st_oid,st_beg) VALUES (20, '2007-01-01');
INSERT INTO ut VALUES (20, '0200-01-01', '0300-01-01', currval('st_seq'));
INSERT INTO ac VALUES (20, 'bar3', currval('st_seq'));
d366 1
a366 1
select * from ac;
d377 1
a377 1
select * from ut;
d388 1
a388 1
select * from ut_no;
d403 1
a403 1
select * from st;
d419 1
a419 1
SELECT * FROM ac;
d453 1
a453 1
SELECT * FROM ac, as_hs() WHERE ac_hid = hs_hid;
d463 1
a463 1
SELECT * FROM ac, as_hs('0222-01-01','0222-01-01','-infinity','infinity',1) WHERE ac_hid = hs_hid;
d471 1
a471 1
SELECT * FROM ac, as_hs('0222-01-01','0222-01-01','-infinity','2006-01-01',1) WHERE ac_hid = hs_hid;
@
1.5
log
@remember one more issue
@
text
@d6 4
a10 5
CREATE TABLE ac(
ac_oid INTEGER,
ac_name TEXT,
ac_hid INTEGER
);
d12 1
a12 1
DROP SEQUENCE st_seq CASCADE;
d16 1
a16 1
DROP TABLE st CASCADE;
d18 15
a32 6
st_oid INTEGER,
-- redundant stored oid to avoid additional joins on query
st_hid INTEGER DEFAULT nextval('st_seq') NOT NULL,
-- discrete monton increasing value assigned to system time
st_beg INTEGER
-- system time when system change occurred
d35 1
a35 1
DROP TABLE ut CASCADE;
d37 20
a56 4
ut_oid INTEGER, -- oid
ut_beg INTEGER, -- user time begin (inclusive)
ut_end INTEGER, -- user time end (exclusive)
ut_hid INTEGER -- user time history id (system time when this entry was made)
d59 1
a59 1
DROP TABLE ut_no CASCADE;
d61 46
a106 5
ut_oid INTEGER, -- oid
ut_beg INTEGER, -- user time begin (inclusive)
ut_end INTEGER, -- user time end (exclusive)
ut_hid INTEGER, -- user time history id (system time when this entry was made)
ut_hid_max INTEGER -- user time history id (system time until which this entry is valid)
d114 2
a115 2
DROP FUNCTION ut_update(INTEGER, INTEGER) CASCADE;
CREATE FUNCTION ut_update(INTEGER, INTEGER)
d118 7
a124 7
oid INTEGER;
hid INTEGER;
pos_prev INTEGER;
pos_this INTEGER;
pos_next INTEGER;
alt_this INTEGER;
alt_prev INTEGER;
d133 2
a134 2
pos_prev := -1;
pos_this := -1;
d148 1
a148 1
pos_next := 9999;
d178 1
a178 1
IF pos_next >= 9999 THEN
d225 2
a226 2
hs_oid INTEGER,
hs_hid INTEGER
d228 3
a230 1
CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
d233 5
a237 5
arg_ut_beg INTEGER; -- user time begin (including)
arg_ut_end INTEGER; -- user time end (including)
arg_st_beg INTEGER; -- system time begin (including)
arg_st_end INTEGER; -- system time end (including)
arg_st_max INTEGER; -- whether to take only maximum system time into account
d241 5
a245 5
arg_ut_beg := $1; IF arg_ut_beg IS NULL THEN arg_ut_beg := 0; END IF;
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 := 1; END IF;
d285 3
a287 1
CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER, INTEGER, INTEGER)
d293 2
a294 1
CREATE OR REPLACE FUNCTION as_hs(INTEGER, INTEGER)
d300 1
a300 1
CREATE OR REPLACE FUNCTION as_hs()
d310 10
a319 10
-- # ^
-- # |
-- # st hid
-- # 2007 #6 | [==20:bar3=[ |
-- # 2006 #5 | [==20:bar2===========[
-- # 2005 #4 [=============20:bar1===========[
-- # 2004 #3 [=============10:foo============[
-- # 2002 #2 | [==10:bar==[ |
-- # 2001 #1 [==10:foo==[ | |
-- # ut--> 100 200 300 9999
d338 2
a339 2
INSERT INTO st (st_oid,st_beg) VALUES (10, 2001);
INSERT INTO ut VALUES (10, 100, 200, currval('st_seq'));
d342 2
a343 2
INSERT INTO st (st_oid,st_beg) VALUES (10, 2002);
INSERT INTO ut VALUES (10, 200, 300, currval('st_seq'));
d346 2
a347 2
INSERT INTO st (st_oid,st_beg) VALUES (10, 2004);
INSERT INTO ut VALUES (10, 100, 9999, currval('st_seq'));
d350 2
a351 2
INSERT INTO st (st_oid,st_beg) VALUES (20, 2005);
INSERT INTO ut VALUES (20, 100, 9999, currval('st_seq'));
d354 2
a355 2
INSERT INTO st (st_oid,st_beg) VALUES (20, 2006);
INSERT INTO ut VALUES (20, 200, 9999, currval('st_seq'));
d358 2
a359 2
INSERT INTO st (st_oid,st_beg) VALUES (20, 2007);
INSERT INTO ut VALUES (20, 200, 300, currval('st_seq'));
d363 1
a363 1
-- ## Test Usage
d367 10
d378 10
d389 14
d404 13
d431 1
a431 1
SELECT * FROM as_hs(0,9999,0,9999,0);
d443 1
a443 1
SELECT * FROM as_hs(0,9999,0,9999,1); -- same as SELECT FROM as_hs();
a461 1
d463 1
a463 1
SELECT * FROM ac, as_hs(222,222,0,9999,1) WHERE ac_hid = hs_hid;
d471 1
a471 1
SELECT * FROM ac, as_hs(222,222,0,2006,1) WHERE ac_hid = hs_hid;
d477 1
@
1.4
log
@remember that we use half-open [..[ intervals in data, but closed [...] intervals in query
@
text
@d20 1
@
1.3
log
@provide more docu
@
text
@d28 4
a31 4
ut_oid INTEGER, --
ut_beg INTEGER, --
ut_end INTEGER, --
ut_hid INTEGER --
d37 2
a38 2
ut_beg INTEGER, -- user time begin
ut_end INTEGER, -- user time end
@
1.2
log
@flush this stuff for now
@
text
@d177 1
a177 1
arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 0; END IF;
d238 1
d250 17
d300 1
a300 1
-- whole test data (complete history, i.e. data at all hids), PLAIN
d302 31
d334 1
a334 4
-- whole history data
SELECT * FROM as_hs();
-- whole test data (complete history, i.e. data at all hids), WITH HISTORY
d336 24
a359 1
@
1.1
log
@add our AS stand-alone SQL and PL/pgSQL basic research stuff to CVS, too
@
text
@d8 3
a10 3
ac_oid INTEGER,
ac_name TEXT,
ac_hid INTEGER
d19 5
a23 5
st_oid INTEGER,
st_hid INTEGER DEFAULT nextval('st_seq') NOT NULL,
-- discrete monton increasing value assigned to system time
st_beg INTEGER
-- system time when system change occurred
d28 4
a31 4
ut_oid INTEGER,
ut_beg INTEGER,
ut_end INTEGER,
ut_hid INTEGER
d36 5
a40 5
ut_oid INTEGER,
ut_beg INTEGER,
ut_end INTEGER,
ut_hid INTEGER,
ut_shid INTEGER
d64 1
a64 1
DELETE FROM ut_no WHERE ut_oid = oid AND ut_shid = hid;
d102 1
a102 1
WHERE ut_hid = alt_this AND ut_beg = pos_prev AND ut_end = pos_this AND ut_oid = oid AND ut_shid = hid;
d105 1
a105 1
INSERT INTO ut_no (ut_hid, ut_oid, ut_beg, ut_end, ut_shid)
d177 1
a177 1
arg_st_max := $5; IF arg_st_max IS NULL THEN arg_st_max := 1; END IF;
d191 1
a191 1
ut_oid = st_oid AND ut_shid = st_hid
d199 1
a199 1
SELECT ut_oid, ut_hid
d206 1
a206 1
ut_oid = st_oid AND ut_shid = st_hid
d238 10
a247 34
-- INSERT INTO ac VALUES (11, 'foo', 1);
-- INSERT INTO ac VALUES (10, 'foo2', 3);
-- INSERT INTO ac VALUES (10, 'foo3', 5);
-- INSERT INTO ac VALUES (10, 'foo', 7);
-- INSERT INTO ac VALUES (10, 'foo', 9);
--
-- INSERT INTO ac VALUES (20, 'bar', 2);
-- INSERT INTO ac VALUES (20, 'bar2', 4);
-- INSERT INTO ac VALUES (20, 'bar3', 6);
-- INSERT INTO ac VALUES (20, 'bar', 8);
-- INSERT INTO ac VALUES (20, 'bar', 10);
--
-- INSERT INTO st (st_oid,st_beg) VALUES (10, 100);
-- INSERT INTO st (st_oid,st_beg) VALUES (20, 110);
-- INSERT INTO st (st_oid,st_beg) VALUES (10, 120);
-- INSERT INTO st (st_oid,st_beg) VALUES (20, 130);
-- INSERT INTO st (st_oid,st_beg) VALUES (10, 140);
-- INSERT INTO st (st_oid,st_beg) VALUES (20, 150);
-- INSERT INTO st (st_oid,st_beg) VALUES (10, 160);
-- INSERT INTO st (st_oid,st_beg) VALUES (20, 170);
--
-- INSERT INTO ut VALUES (10, 100, 9999, 1);
-- INSERT INTO ut VALUES (10, 102, 9999, 3);
-- INSERT INTO ut VALUES (10, 104, 9999, 5);
-- INSERT INTO ut VALUES (10, 106, 9999, 7);
-- INSERT INTO ut VALUES (10, 100, 200, 9);
--
-- INSERT INTO ut VALUES (20, 101, 9999, 2);
-- INSERT INTO ut VALUES (20, 103, 9999, 4);
-- INSERT INTO ut VALUES (20, 105, 9999, 6);
-- INSERT INTO ut VALUES (20, 107, 9999, 8);
-- INSERT INTO ut VALUES (20, 100, 200, 10);
-- INSERT INTO ut VALUES (20, 300, 400, 10);
-- INSERT INTO ut VALUES (20, 500, 600, 10);
d272 18
@