pasupulaphani · October 16, 2025 09:03
diff --git a/closure example b/closure example

 CREATE EXTENSION IF NOT EXISTS btree_gist;
 CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- optional, if you use SHA-256 hashes

 CREATE TABLE instrument_node (
                                 instrument_id      SERIAL PRIMARY KEY,
                                 name               TEXT NOT NULL,
                                 type               TEXT,
                                 source             TEXT,
                                 legal_entity       TEXT,
                                 effective_range    TSRANGE NOT NULL DEFAULT tsrange(NOW()::timestamp, null::timestamp, '[)'),
                                 asof_range         TSRANGE NOT NULL DEFAULT tsrange(NOW()::timestamp, null::timestamp, '[)'),
                                 _hash_256          TEXT,

    -- prevent overlapping temporal versions for the same instrument
                                 CONSTRAINT instrument_node_no_overlap
                                     EXCLUDE USING gist (
                                     instrument_id WITH =,
                                     effective_range WITH &&,
                                     asof_range WITH &&
                                     )
 );

 CREATE TABLE position_edge (
                               position_id          SERIAL PRIMARY KEY,
                               parent_instrument_id INTEGER NOT NULL REFERENCES instrument_node(instrument_id) ON DELETE CASCADE,
                               child_instrument_id  INTEGER NOT NULL REFERENCES instrument_node(instrument_id) ON DELETE CASCADE,
                               source               TEXT,
                               effective_range    TSRANGE NOT NULL DEFAULT tsrange(NOW()::timestamp, null::timestamp, '[)'),
                               asof_range         TSRANGE NOT NULL DEFAULT tsrange(NOW()::timestamp, null::timestamp, '[)'),
                               _hash_256            TEXT,

    -- prevent duplicate or overlapping edges for the same parent/child within the same time
                               CONSTRAINT position_edge_no_overlap
                                   EXCLUDE USING gist (
                                   parent_instrument_id WITH =,
                                   child_instrument_id  WITH =,
                                   effective_range      WITH &&,
                                   asof_range           WITH &&
                                   )
 );

 DROP TABLE IF EXISTS instrument_closure;
 CREATE TABLE instrument_closure (
                                    ancestor_id INTEGER NOT NULL REFERENCES instrument_node(instrument_id),
                                    descendant_id INTEGER NOT NULL REFERENCES instrument_node(instrument_id),
                                    path_length INTEGER NOT NULL, -- Distance between ancestor and descendant (0 for self-referencing)
                                    effective_range TSRANGE NOT NULL, -- The time period when this relationship was effective in the real world
                                    asof_range TSRANGE NOT NULL,     -- The time period when this relationship was known to the system
                                    PRIMARY KEY (ancestor_id, descendant_id, effective_range, asof_range) -- Composite primary key
 );

 CREATE INDEX idx_instrument_closure_descendant ON instrument_closure (descendant_id);
 CREATE INDEX idx_instrument_closure_ancestor ON instrument_closure (ancestor_id);
 CREATE INDEX idx_instrument_closure_effective_range ON instrument_closure USING GIST (effective_range);
 CREATE INDEX idx_instrument_closure_asof_range ON instrument_closure USING GIST (asof_range);


 -- Clear tables first (for repeatability)
 TRUNCATE position_edge, instrument_node RESTART IDENTITY;

 -- Insert instruments
 INSERT INTO instrument_node (name, type, source, legal_entity, effective_range, asof_range)
 VALUES
    ('Global_Portfolio', 'Portfolio', 'SystemX', 'Global_Entity', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('Fund_A', 'Fund', 'SystemX', 'Entity_A', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('Fund_B', 'Fund', 'SystemX', 'Entity_B', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('SPV_1', 'SPV', 'SystemX', 'Entity_C', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('Bond_1', 'Bond', 'SystemX', 'Entity_D', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('Bond_2', 'Bond', 'SystemX', 'Entity_E', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('Stock_1', 'Equity', 'SystemX', 'Entity_F', '[2020-01-01, infinity)', '[2020-01-01, infinity)'),
    ('Derivative_1', 'Derivative', 'SystemX', 'Entity_G', '[2020-01-01, infinity)', '[2020-01-01, infinity)');

 -- Global_Portfolio → Fund_A, Fund_B, SPV_1
 INSERT INTO position_edge (parent_instrument_id, child_instrument_id, source, effective_range, asof_range)
 SELECT p.instrument_id, c.instrument_id, 'SystemX', '[2020-01-01, infinity)', '[2020-01-01, infinity)'
 FROM instrument_node p
         JOIN instrument_node c ON c.name IN ('Fund_A', 'Fund_B', 'SPV_1')
 WHERE p.name = 'Global_Portfolio';

 -- Fund_A → Bond_1, Stock_1, Derivative_1
 INSERT INTO position_edge (parent_instrument_id, child_instrument_id, source, effective_range, asof_range)
 SELECT p.instrument_id, c.instrument_id, 'SystemX', '[2020-01-01, infinity)', '[2020-01-01, infinity)'
 FROM instrument_node p
         JOIN instrument_node c ON c.name IN ('Bond_1', 'Stock_1', 'Derivative_1')
 WHERE p.name = 'Fund_A';

 -- Fund_B → Bond_2, Stock_1 (Stock_1 has multiple parents)
 INSERT INTO position_edge (parent_instrument_id, child_instrument_id, source, effective_range, asof_range)
 SELECT p.instrument_id, c.instrument_id, 'SystemX', '[2020-01-01, infinity)', '[2020-01-01, infinity)'
 FROM instrument_node p
         JOIN instrument_node c ON c.name IN ('Bond_2', 'Stock_1')
 WHERE p.name = 'Fund_B';

 -- SPV_1 → Bond_1 (Bond_1 has multiple parents)
 INSERT INTO position_edge (parent_instrument_id, child_instrument_id, source, effective_range, asof_range)
 SELECT p.instrument_id, c.instrument_id, 'SystemX', '[2020-01-01, infinity)', '[2020-01-01, infinity)'
 FROM instrument_node p
         JOIN instrument_node c ON c.name = 'Bond_1'
 WHERE p.name = 'SPV_1';



 -- Suppose Fund_B stopped holding Stock_1 after 2023-12-31
 UPDATE position_edge
 SET effective_range = tsrange('[2020-01-01, 2023-12-31)')
 WHERE parent_instrument_id = (
    SELECT instrument_id FROM instrument_node WHERE name = 'Fund_B'
 )
  AND child_instrument_id = (
    SELECT instrument_id FROM instrument_node WHERE name = 'Stock_1'
 );


 -- Step 1: Clear existing closure table data (if re-populating)
 TRUNCATE TABLE instrument_closure;

 -- Step 2: Insert self-referencing entries for all instrument_nodes
 INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
 SELECT
    n.instrument_id,
    n.instrument_id,
    0,
    -- Assuming instrument_node has its own effective and asof ranges for its existence
    -- For simplicity, let's assume a node exists as long as it's in the table for now
    -- In a real bitemporal system, instrument_node would also have effective/asof ranges.
    -- For this example, we'll use a wide range or derive from related edges.
    -- Let's use a placeholder for now, assuming nodes are always valid for their edges.
    -- A more robust solution would involve instrument_node having its own temporal validity.
    TSRANGE('-infinity'::timestamp, 'infinity'::timestamp, '[]'), -- Placeholder: Node always exists effectively
    TSRANGE('-infinity'::timestamp, 'infinity'::timestamp, '[]')  -- Placeholder: Node always known as-of
 FROM
    instrument_node n;

 -- Step 3: Recursively build ancestor-descendant paths
 WITH RECURSIVE closure_cte AS (
    -- Base case: Direct relationships from position_edge
    SELECT
        pe.parent_instrument_id AS ancestor_id,
        pe.child_instrument_id AS descendant_id,
        1 AS path_length,
        pe.effective_range,
        pe.asof_range
    FROM
        position_edge pe

    UNION ALL

    -- Recursive step: Join with existing paths to extend them
    SELECT
        cte.ancestor_id,
        pe.child_instrument_id AS descendant_id,
        cte.path_length + 1 AS path_length,
        -- Intersect effective ranges
        cte.effective_range * pe.effective_range AS effective_range,
        -- Intersect asof ranges
        cte.asof_range * pe.asof_range AS asof_range
    FROM
        closure_cte cte
            JOIN
        position_edge pe ON cte.descendant_id = pe.parent_instrument_id
    WHERE
      -- Ensure ranges are still valid after intersection
        NOT ISEMPTY(cte.effective_range * pe.effective_range) AND
        NOT ISEMPTY(cte.asof_range * pe.asof_range)
 )
 INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
 SELECT DISTINCT
    ancestor_id,
    descendant_id,
    path_length,
    effective_range,
    asof_range
 FROM
    closure_cte
 ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING; -- Handle potential duplicates from different paths


 select * from instrument_closure;


 SELECT
    ic.descendant_id,
    n.name AS descendant_name,
    ic.path_length,
    ic.effective_range,
    ic.asof_range
 FROM
    instrument_closure ic
        JOIN
    instrument_node n ON ic.descendant_id = n.instrument_id
 WHERE
    ic.ancestor_id = 1 AND
    ic.path_length > 0 AND -- Exclude self-referencing entry
    ic.effective_range @> '2023-01-15 10:00:00'::timestamp AND
    ic.asof_range @> '2023-01-20 12:00:00'::timestamp
 ORDER BY
    ic.path_length, ic.descendant_id;


 -- ROOT (Global Portfolio)
 --      ├── Fund_A
 -- │    ├── Bond_1
 -- │    ├── Stock_1
 -- │    └── Derivative_1
 -- ├── Fund_B
 -- │    ├── Bond_2
 -- │    └── Stock_1  ← (shared with Fund_A → multiple parents!)
 -- └── SPV_1
 --      └── Bond_1   ← (also shared → multiple parents!)


 SELECT
    ic.descendant_id,
    n.name AS descendant_name,
    n.type AS descendant_type,
    n.source AS descendant_source,
    ic.path_length
 FROM
    instrument_closure ic
        JOIN
    instrument_node n ON ic.descendant_id = n.instrument_id
 WHERE
    ic.ancestor_id = (SELECT instrument_id FROM instrument_node WHERE name = 'Fund_A' AND source = 'SystemX' LIMIT 1)
  AND ic.path_length > 0 -- Exclude the node itself
  AND
    ic.effective_range @> '2023-01-15 10:00:00'::timestamp
  AND
    ic.asof_range @> '2023-01-20 12:00:00'::timestamp
 ORDER BY
    ic.path_length,
    ic.descendant_id;





 --------



 -- Trigger functions and triggers for instrument_node changes

 -- Function to handle INSERT on instrument_node
 CREATE OR REPLACE FUNCTION trg_instrument_node_insert()
 RETURNS TRIGGER AS $$
 BEGIN
    -- Insert self-referencing entry for the new node
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    VALUES (
        NEW.instrument_id,
        NEW.instrument_id,
        0,
        -- Assuming instrument_node has its own effective and asof ranges for its existence.
        -- If not, use a wide range like TSRANGE(\"-infinity\", \"infinity\", \"[]\")
        -- For this example, we\'ll use the ranges from the instrument_node itself.
        NEW.effective_range,
        NEW.asof_range
    )
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger for INSERT on instrument_node
 CREATE TRIGGER after_instrument_node_insert
 AFTER INSERT ON instrument_node
 FOR EACH ROW
 EXECUTE FUNCTION trg_instrument_node_insert();


 -- Function to handle DELETE on instrument_node
 CREATE OR REPLACE FUNCTION trg_instrument_node_delete()
 RETURNS TRIGGER AS $$
 BEGIN
    -- Delete all entries in instrument_closure related to the deleted node
    DELETE FROM instrument_closure
    WHERE ancestor_id = OLD.instrument_id OR descendant_id = OLD.instrument_id;

    RETURN OLD;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger for DELETE on instrument_node
 CREATE TRIGGER after_instrument_node_delete
 AFTER DELETE ON instrument_node
 FOR EACH ROW
 EXECUTE FUNCTION trg_instrument_node_delete();


 -- Function to handle UPDATE on instrument_node
 CREATE OR REPLACE FUNCTION trg_instrument_node_update()
 RETURNS TRIGGER AS $$
 BEGIN
    -- If the effective_range or asof_range of the instrument_node changes,
    -- update its self-referencing entry in instrument_closure.
    -- Other fields (Name, Type, Source, Legal_entity) do not affect the closure table structure.
    IF OLD.effective_range IS DISTINCT FROM NEW.effective_range OR
       OLD.asof_range IS DISTINCT FROM NEW.asof_range
    THEN
        -- First, invalidate the old self-referencing entry (if its ranges changed)
        DELETE FROM instrument_closure
        WHERE ancestor_id = OLD.instrument_id
          AND descendant_id = OLD.instrument_id
          AND path_length = 0
          AND effective_range = OLD.effective_range
          AND asof_range = OLD.asof_range;

        -- Then, insert the new self-referencing entry with updated ranges
        INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
        VALUES (
            NEW.instrument_id,
            NEW.instrument_id,
            0,
            NEW.effective_range,
            NEW.asof_range
        )
        ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;
    END IF;

    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger for UPDATE on instrument_node
 CREATE TRIGGER after_instrument_node_update
 AFTER UPDATE ON instrument_node
 FOR EACH ROW
 EXECUTE FUNCTION trg_instrument_node_update();






 -- Trigger functions and triggers for position_edge changes

 -- Function to handle INSERT on position_edge
 CREATE OR REPLACE FUNCTION trg_position_edge_insert()
 RETURNS TRIGGER AS $$
 BEGIN
    -- 1. Insert the new direct edge
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    VALUES (NEW.parent_instrument_id, NEW.child_instrument_id, 1, NEW.effective_range, NEW.asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    -- 2. New paths from ancestors of parent_instrument_id to child_instrument_id
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    SELECT
        ic.ancestor_id,
        NEW.child_instrument_id,
        ic.path_length + 1,
        ic.effective_range * NEW.effective_range,
        ic.asof_range * NEW.asof_range
    FROM
        instrument_closure ic
    WHERE
        ic.descendant_id = NEW.parent_instrument_id AND ic.path_length >= 0 AND
        NOT ISEMPTY(ic.effective_range * NEW.effective_range) AND
        NOT ISEMPTY(ic.asof_range * NEW.asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    -- 3. New paths from parent_instrument_id to descendants of child_instrument_id
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    SELECT
        NEW.parent_instrument_id,
        ic.descendant_id,
        1 + ic.path_length,
        NEW.effective_range * ic.effective_range,
        NEW.asof_range * ic.asof_range
    FROM
        instrument_closure ic
    WHERE
        ic.ancestor_id = NEW.child_instrument_id AND ic.path_length >= 0 AND
        NOT ISEMPTY(NEW.effective_range * ic.effective_range) AND
        NOT ISEMPTY(NEW.asof_range * ic.asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    -- 4. New paths from ancestors of parent_instrument_id through new edge to descendants of child_instrument_id
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    SELECT
        ica.ancestor_id,
        icd.descendant_id,
        ica.path_length + 1 + icd.path_length,
        ica.effective_range * NEW.effective_range * icd.effective_range,
        ica.asof_range * NEW.asof_range * icd.asof_range
    FROM
        instrument_closure ica
    JOIN
        instrument_closure icd ON icd.ancestor_id = NEW.child_instrument_id
    WHERE
        ica.descendant_id = NEW.parent_instrument_id AND ica.path_length >= 0 AND
        icd.path_length >= 0 AND
        NOT ISEMPTY(ica.effective_range * NEW.effective_range * icd.effective_range) AND
        NOT ISEMPTY(ica.asof_range * NEW.asof_range * icd.asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger for INSERT on position_edge
 CREATE TRIGGER after_position_edge_insert
 AFTER INSERT ON position_edge
 FOR EACH ROW
 EXECUTE FUNCTION trg_position_edge_insert();


 -- Function to handle UPDATE on position_edge
 -- WARNING: This trigger is highly complex and may have performance implications.
 -- Consider batch processing or application-level logic for robust bitemporal updates.
 CREATE OR REPLACE FUNCTION trg_position_edge_update()
 RETURNS TRIGGER AS $$
 DECLARE
    old_parent_id INTEGER := OLD.parent_instrument_id;
    old_child_id INTEGER := OLD.child_instrument_id;
    old_eff_range TSRANGE := OLD.effective_range;
    old_asof_range TSRANGE := OLD.asof_range;

    new_parent_id INTEGER := NEW.parent_instrument_id;
    new_child_id INTEGER := NEW.child_instrument_id;
    new_eff_range TSRANGE := NEW.effective_range;
    new_asof_range TSRANGE := NEW.asof_range;
 BEGIN
    -- Step 1: Invalidate all paths that were dependent on the OLD edge
    -- This is a simplified invalidation. A full bitemporal invalidation might involve
    -- splitting ranges or setting upper bounds for asof_range.
    -- For simplicity here, we\\'ll delete paths that *exactly* matched the old edge\\'s contribution.
    -- In a real system, this would need to be more sophisticated to handle partial overlaps.

    -- Delete direct old edge
    DELETE FROM instrument_closure
    WHERE ancestor_id = old_parent_id
      AND descendant_id = old_child_id
      AND path_length = 1
      AND effective_range = old_eff_range
      AND asof_range = old_asof_range;

    -- Delete paths where OLD edge was the direct link (A -> OLD_EDGE -> D)
    DELETE FROM instrument_closure ic
    WHERE EXISTS (
        SELECT 1 FROM instrument_closure ica
        WHERE ica.descendant_id = old_parent_id
          AND ica.ancestor_id = ic.ancestor_id
          AND ica.effective_range * old_eff_range * ic.effective_range = ic.effective_range
          AND ica.asof_range * old_asof_range * ic.asof_range = ic.asof_range
    ) AND EXISTS (
        SELECT 1 FROM instrument_closure icd
        WHERE icd.ancestor_id = old_child_id
          AND icd.descendant_id = ic.descendant_id
          AND icd.effective_range * old_eff_range * ic.effective_range = ic.effective_range
          AND icd.asof_range * old_asof_range * ic.asof_range = ic.asof_range
    ) AND ic.path_length = ica.path_length + 1 + icd.path_length;

    -- Step 2: Re-insert all paths based on the NEW edge (similar to INSERT logic)
    -- This will re-establish direct and transitive relationships.

    -- 1. Insert the new direct edge
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    VALUES (new_parent_id, new_child_id, 1, new_eff_range, new_asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    -- 2. New paths from ancestors of new_parent_id to new_child_id
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    SELECT
        ic.ancestor_id,
        new_child_id,
        ic.path_length + 1,
        ic.effective_range * new_eff_range,
        ic.asof_range * new_asof_range
    FROM
        instrument_closure ic
    WHERE
        ic.descendant_id = new_parent_id AND ic.path_length >= 0 AND
        NOT ISEMPTY(ic.effective_range * new_eff_range) AND
        NOT ISEMPTY(ic.asof_range * new_asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    -- 3. New paths from new_parent_id to descendants of new_child_id
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    SELECT
        new_parent_id,
        ic.descendant_id,
        1 + ic.path_length,
        new_eff_range * ic.effective_range,
        new_asof_range * ic.asof_range
    FROM
        instrument_closure ic
    WHERE
        ic.ancestor_id = new_child_id AND ic.path_length >= 0 AND
        NOT ISEMPTY(new_eff_range * ic.effective_range) AND
        NOT ISEMPTY(new_asof_range * ic.asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    -- 4. New paths from ancestors of new_parent_id through new edge to descendants of new_child_id
    INSERT INTO instrument_closure (ancestor_id, descendant_id, path_length, effective_range, asof_range)
    SELECT
        ica.ancestor_id,
        icd.descendant_id,
        ica.path_length + 1 + icd.path_length,
        ica.effective_range * new_eff_range * icd.effective_range,
        ica.asof_range * new_asof_range * icd.asof_range
    FROM
        instrument_closure ica
    JOIN
        instrument_closure icd ON icd.ancestor_id = new_child_id
    WHERE
        ica.descendant_id = new_parent_id AND ica.path_length >= 0 AND
        icd.path_length >= 0 AND
        NOT ISEMPTY(ica.effective_range * new_eff_range * icd.effective_range) AND
        NOT ISEMPTY(ica.asof_range * new_asof_range * icd.asof_range)
    ON CONFLICT (ancestor_id, descendant_id, effective_range, asof_range) DO NOTHING;

    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger for UPDATE on position_edge
 CREATE TRIGGER after_position_edge_update
 AFTER UPDATE ON position_edge
 FOR EACH ROW
 EXECUTE FUNCTION trg_position_edge_update();


 -- Function to handle DELETE on position_edge
 -- WARNING: This trigger is highly complex and may have performance implications.
 -- Consider batch processing or application-level logic for robust bitemporal deletions.
 CREATE OR REPLACE FUNCTION trg_position_edge_delete()
 RETURNS TRIGGER AS $$
 DECLARE
    old_parent_id INTEGER := OLD.parent_instrument_id;
    old_child_id INTEGER := OLD.child_instrument_id;
    old_eff_range TSRANGE := OLD.effective_range;
    old_asof_range TSRANGE := OLD.asof_range;
 BEGIN
    -- Step 1: Invalidate all paths that were dependent on the OLD (deleted) edge
    -- This is a simplified invalidation. A full bitemporal invalidation might involve
    -- splitting ranges or setting upper bounds for asof_range.
    -- For simplicity here, we\\'ll delete paths that *exactly* matched the old edge\\'s contribution.
    -- In a real system, this would need to be more sophisticated to handle partial overlaps.

    -- Delete direct old edge
    DELETE FROM instrument_closure
    WHERE ancestor_id = old_parent_id
      AND descendant_id = old_child_id
      AND path_length = 1
      AND effective_range = old_eff_range
      AND asof_range = old_asof_range;

    -- Delete paths where OLD edge was the direct link (A -> OLD_EDGE -> D)
    DELETE FROM instrument_closure ic
    WHERE EXISTS (
        SELECT 1 FROM instrument_closure ica
        WHERE ica.descendant_id = old_parent_id
          AND ica.ancestor_id = ic.ancestor_id
          AND ica.effective_range * old_eff_range * ic.effective_range = ic.effective_range
          AND ica.asof_range * old_asof_range * ic.asof_range = ic.asof_range
    ) AND EXISTS (
        SELECT 1 FROM instrument_closure icd
        WHERE icd.ancestor_id = old_child_id
          AND icd.descendant_id = ic.descendant_id
          AND icd.effective_range * old_eff_range * ic.effective_range = ic.effective_range
          AND icd.asof_range * old_asof_range * ic.asof_range = ic.asof_range
    ) AND ic.path_length = ica.path_length + 1 + icd.path_length;

    RETURN OLD;
 END;
 $$ LANGUAGE plpgsql;

 -- Trigger for DELETE on position_edge
 CREATE TRIGGER after_position_edge_delete
 AFTER DELETE ON position_edge
 FOR EACH ROW
 EXECUTE FUNCTION trg_position_edge_delete();
No results found