OSSP CVS Repository

ossp-pkg/as/as-db/bres/bres-nonoverlap.sql

---

DROP TABLE ht;
CREATE TABLE ht (
    ht_hid    INTEGER,
    ht_oid    INTEGER,
    ht_vb     INTEGER,
    ht_ve     INTEGER
);

DROP TABLE htno;
CREATE TABLE htno (
    ht_hid    INTEGER,
    ht_oid    INTEGER,
    ht_vb     INTEGER,
    ht_ve     INTEGER
);

--  Update htno table for ht change on particular OID
DROP FUNCTION ht2htno(INTEGER);
CREATE FUNCTION ht2htno(INTEGER) 
RETURNS INTEGER LANGUAGE 'plpgsql' STABLE AS '
DECLARE
    oid INTEGER;
    pos_prev INTEGER;
    pos_this INTEGER;
    pos_next INTEGER;
    alt_this INTEGER;
    alt_prev INTEGER;
BEGIN
    oid := $1;

    --  delete all old entries corresponding to OID
    DELETE FROM htno WHERE ht_oid = oid;

    --  initialize iteration variables
    pos_prev := -1;
    pos_this := -1;
    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(ht_vb) FROM ht WHERE ht_vb > pos_this AND ht_oid = oid)
              UNION (SELECT min(ht_ve) FROM ht WHERE ht_ve > pos_this AND ht_oid = oid)
            ) AS result (v)
        );
        IF pos_next IS NULL THEN
            pos_next := 9999;
        END IF;

        --  determine current altidude
        alt_this := (SELECT max(ht_hid) FROM ht WHERE ht_vb <= pos_this AND pos_this < ht_ve AND ht_oid = oid);
        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 htno SET ht_ve = pos_next
                    WHERE ht_hid = alt_this AND ht_vb = pos_prev AND ht_ve = pos_this AND ht_oid = oid;
            ELSE
                -- RAISE NOTICE ''changing altitude - new entry'';
                INSERT INTO htno (ht_hid, ht_oid, ht_vb, ht_ve)
                    VALUES (alt_this, oid, pos_this, pos_next);
                pos_prev := pos_this;
            END IF;
        END IF;

        --  stop if maximum is reached
        IF pos_next >= 9999 THEN
           EXIT;
        END IF;

        --  move one step
        pos_this := pos_next;
        alt_prev := alt_this;
    END LOOP;
    RETURN 0;
END;
';

--  Trigger Stub Function
DROP FUNCTION ht_trigger();
CREATE FUNCTION ht_trigger() 
RETURNS TRIGGER LANGUAGE 'plpgsql' STABLE AS '
BEGIN
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        PERFORM ht2htno(NEW.ht_oid);
    ELSE
        IF TG_OP = ''DELETE'' THEN
            PERFORM ht2htno(OLD.ht_oid);
        END IF;
    END IF;
    RETURN NULL;
END;
';

--  Global Trigger
CREATE TRIGGER ht_trigger
    AFTER INSERT OR UPDATE OR DELETE
    ON ht FOR EACH ROW
    EXECUTE PROCEDURE ht_trigger ();

INSERT INTO ht VALUES (1, 1, 2, 4);
INSERT INTO ht VALUES (2, 1, 3, 6);
INSERT INTO ht VALUES (3, 1, 8, 9);
INSERT INTO ht VALUES (4, 1, 5, 6);
INSERT INTO ht VALUES (5, 1, 3, 5);
INSERT INTO ht VALUES (6, 1, 7, 8);
INSERT INTO ht VALUES (7, 1, 7, 9);

INSERT INTO ht VALUES (30, 2, 10, 14);
INSERT INTO ht VALUES (31, 2, 12, 17);
INSERT INTO ht VALUES (32, 2, 13, 15);
INSERT INTO ht VALUES (33, 2, 16, 19);
INSERT INTO ht VALUES (34, 2, 20, 22);

SELECT * FROM ht ORDER BY ht_hid;
SELECT * FROM htno ORDER BY ht_hid;

---