如何在postgresql中实现"type is <typeName> table of rowid index by“概念，将表数据与新插入的数据进行比对

Question

我正在尝试将下面这段代码从oracle转换为postgres(基本上目标是根据某个日期在表中插入一些记录而不会重叠，如果存在日期范围重叠，则应引发触发器)。但是经过这么多努力，还是没有办法弄明白该怎么做？

DECLARE
    V_check PLS_INTEGER;
BEGIN
    SELECT COUNT(*) INTO V_check
    FROM   user_objects
    WHERE  object_name = 'STORE_ROWID'
    AND    object_type = 'PACKAGE';
    IF V_check=0 THEN
        EXECUTE IMMEDIATE 'CREATE PACKAGE store_rowid AS'
        ||CHR(10)||'  TYPE T_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;'
        ||CHR(10)||'  T_newones    T_rowid;'
        ||CHR(10)||'  T_empty      T_rowid;'
        ||CHR(10)||'END store_rowid;';
    END IF;
END;

CREATE OR REPLACE TRIGGER biu_base_host
    BEFORE INSERT OR UPDATE ON BASE_HOST
    BEGIN
        STORE_ROWID.T_newones := STORE_ROWID.T_empty;
END biu_base_host;

CREATE OR REPLACE TRIGGER aiufr_base_host
    AFTER INSERT OR UPDATE ON BASE_HOST
    FOR EACH ROW
    BEGIN
        STORE_ROWID.T_newones(STORE_ROWID.T_newones.COUNT+1) := :NEW.ROWID;
END aiufr_base_host;

CREATE OR REPLACE TRIGGER aiu_base_host
    AFTER INSERT OR UPDATE ON BASE_HOST
    DECLARE
        r           BASE_HOST%ROWTYPE;
        V_count     INTEGER;
        V_status    PLS_INTEGER;
        C_lockid    CONSTANT PLS_INTEGER := 1001;

    BEGIN
    V_status := DBMS_LOCK.request(C_lockid,DBMS_LOCK.x_mode,release_on_commit=>TRUE);
    IF V_status NOT IN (0,4)
    THEN
        RAISE_APPLICATION_ERROR(-20102,'DBMS_LOCK.request ERROR, rc='||V_status);
    END IF;

    FOR i IN 1..STORE_ROWID.T_newones.COUNT
    LOOP
        SELECT  * INTO r
        FROM    BASE_HOST
        WHERE   ROWID = STORE_ROWID.T_newones(i);

        SELECT  COUNT(*) INTO V_count
        FROM    BASE_HOST
        WHERE   ROWID <> STORE_ROWID.T_newones(i)
        AND     ZONE_ID = r.ZONE_ID
        AND     SITE_ID = r.SITE_ID
        AND     ORG_ID = r.ORG_ID
        AND     (start_date BETWEEN r.start_date AND r.end_date
                OR end_date BETWEEN r.start_date AND r.end_date
                OR (start_date < r.start_date AND end_date > r.end_date));

        IF V_count <> 0 THEN
            RAISE_APPLICATION_ERROR(-20001,
                'Range from '||
                TO_CHAR(r.start_date,'YYYYMMDD-HH24MISS') ||
                ' to '||
                TO_CHAR(r.end_date,'YYYYMMDD-HH24MISS')||
                ' overlaps existing records');
        END IF;
    END LOOP;
END aiu_base_host;

Answer 1

排除约束我认为是实现这一点的完美方法，我已经编写了以下代码来实现相同的功能：

CREATE EXTENSION btree_gist；create table base_host ( zone_id numeric(3)，site_id numeric(3)，org_id VARCHAR(16)，start_date date，end_date date，reserved_capacity numeric(5,2)，last_update date，user_id VARCHAR(32)，EXCLUDE USING GIST ( zone_id WITH =，site_id WITH =，org_id WITH =，daterange("start_date"，"end_date") WITH &&))；