What combination of select query and SQL triggers will return each node's ancestry and depth, with triggers maintaining the closure table?

Question

Given a "location" table and a location_closure table which stores the depth between the various nodes in location tree(s), what combination of select query and SQL triggers will consistently return a result set containing each node's ancestry and depth, assuming triggers alone maintain the closure table?

In the screenshot, if this were working as envisioned, there would be two additional entries in the closure table; one with parent "d592...", child "b1cd..." and depth2 of 1, and the other with parent "c6af...", child "b1cd..." with depth2 of 2. (Really "parent" should be "ancestor" for correctness, since that second missing entry would be a grandparent).

Links provided are what I've been trying to follow. Working with Postgres. Am aware of recursive queries, but was sold on the supposed ease and simplicity of a closure table.

CREATE TABLE location_closure (
    parent VARCHAR(100),
    child VARCHAR(100),
    -- Apparently "depth" is officially part of SQL
    depth2 INT
);

CREATE TABLE location (
    id VARCHAR NOT NULL DEFAULT md5(random()::text),
    name VARCHAR(100),
    startdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- example: warehouse within city; or shelf within warehouse
    within VARCHAR(100),
    PRIMARY KEY (id)
)
;

CREATE FUNCTION location_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    -- If no parent given, assume this is a top level entry, which will need self references
    IF NEW.within IS NULL OR NEW.within = '' THEN
    INSERT INTO location_closure (parent, child, depth2) VALUES (NEW.id, NEW.id, 0);
    ELSE
    -- Will c.parent ever contain the new id? This new ID was just created and added to the location table; it has yet to be added to the closure table at all- that's supposed to be the job of this code!
    --INSERT INTO location_closure (parent, child, depth2) SELECT p.parent, c.child, p.depth2+c.depth2+1 FROM location_closure p, location_closure c WHERE p.child=NEW.within AND c.parent=NEW.id;
    -- Entries inserted into the location table with 'within' result in no entries in the location_closure table with that ID as a parent, 
    INSERT INTO location_closure (parent, child, depth2) SELECT p.parent, c.id, p.depth2+1 FROM location c, location_closure p WHERE c.id=NEW.id AND p.parent=NEW.within;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER location_trigger
AFTER INSERT ON location
FOR EACH ROW
EXECUTE PROCEDURE location_trigger_function()
;

Inserts:

INSERT INTO location (name) VALUES ('Smith County');
INSERT INTO location (name) VALUES ('Geneva County');
INSERT INTO location (name, within) SELECT 'Paradise City', id FROM location WHERE name LIKE '%Smith County%' LIMIT 1;
INSERT INTO location (name, within) SELECT 'Local Mart', id FROM location WHERE name LIKE '%Paradise City%' LIMIT 1;

Reference: https://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html https://technobytz.com/closure_table_store_hierarchical_data.html

score 0 · Answer 1 · answered Jul 23 '22 at 19:31

Problem was with my logic.

Remove conditional; always have trigger add 0 depth parent / child entry; reverting back to original select statement from the examples now works correctly.

Credit goes to this page: https://gist.github.com/dongryphon/13d6aa71941e2a50d52f0fc8c87e6df6

Working code (only had to modify trigger function):

CREATE FUNCTION location_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    
    --IF NEW.within IS NULL OR NEW.within = '' THEN
    INSERT INTO location_closure (parent, child, depth2) VALUES (NEW.id, NEW.id, 0);
    --ELSE
    --INSERT INTO location_closure (parent, child, depth2) VALUES (NEW.within, NEW.id, );
    INSERT INTO location_closure (parent, child, depth2) SELECT p.parent, c.child, p.depth2+c.depth2+1 FROM location_closure p, location_closure c WHERE p.child=NEW.within AND c.parent=NEW.id;
    --INSERT INTO location_closure (parent, child, depth2) SELECT p.parent, c.id, p.depth2+1 FROM location c, location_closure p WHERE c.id=NEW.id AND p.parent=NEW.within;
    --END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

What combination of select query and SQL triggers will return each node's ancestry and depth, with triggers maintaining the closure table?

1 Answers1