Using primary key & foreign key to build ltree

Question

I am pretty new to postgres & especially new to ltree. Searching the web for ltree brought me to examples where the tree was build by chaining characters. But I want to use the primary key & foreign key.

Therefore I build the following table:

create table fragment(
        id serial primary key,
        description text,
        path ltree
    );
create index tree_path_idx on fragment using gist (path);

Instead of A.B.G I want to have 1.3.5. A root in the examples online is added like so:

insert into fragment (description, path) values ('A', 'A');

Instead of A I want to have the primary key (which I don't know at that moment). Is there a way to do that?

When adding a child I got the same problem:

insert into tree (letter, path) values ('B', '0.??');

I know the id of the parent but not of the child that I want to append.

Is there a way to do that or am I completey off track?

Thank you very much!

unutbu · Accepted Answer · 2019-02-22T16:04:01.637

You could create a trigger which modifies path before each insert. For example, using this setup:

DROP TABLE IF EXISTS fragment;
CREATE TABLE fragment(
    id serial primary key
    , description text
    , path ltree
);
CREATE INDEX tree_path_idx ON fragment USING gist (path);

Define the trigger:

CREATE OR REPLACE FUNCTION before_insert_on_fragment()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    new.path := new.path ||  new.id::text;
    return new;
END $$;

DROP TRIGGER IF EXISTS before_insert_on_fragment ON fragment;
CREATE TRIGGER before_insert_on_fragment
BEFORE INSERT ON fragment
FOR EACH ROW EXECUTE PROCEDURE before_insert_on_fragment();

Test the trigger:

INSERT INTO fragment (description, path) VALUES ('A', '');
SELECT * FROM fragment;
-- | id | description | path |
-- |----+-------------+------|
-- |  1 | A           |    1 |

Now insert B under id = 1:

INSERT INTO fragment (description, path) VALUES ('B', (SELECT path FROM fragment WHERE id=1));
SELECT * FROM fragment;

-- | id | description | path |
-- |----+-------------+------|
-- |  1 | A           |    1 |
-- |  2 | B           |  1.2 |

Insert C under B:

INSERT INTO fragment (description, path) VALUES ('C', (SELECT path FROM fragment WHERE description='B'));
SELECT * FROM fragment;

-- | id | description |  path |
-- |----+-------------+-------|
-- |  1 | A           |     1 |
-- |  2 | B           |   1.2 |
-- |  3 | C           | 1.2.3 |

Moaaz Assali · Answer 2 · 2022-08-02T20:28:10.363

For anyone checking this in the future, I had the same issue and I figured out a way to do it without triggers and within the same INSERT query:

INSERT INTO fragment (description, path)
VALUES ('description', text2ltree('1.' || currval(pg_get_serial_sequence('fragment', 'id'))));

Explanation:

We can get the id of the current insert operation using currval(pg_get_serial_sequence('fragment', 'id')), which we can concatenate as a string with the parent full path 'parent_path' || and finally convert it to ltree using text2ltree(). The id from currval() doesn't have to be incremented because it is called during INSERT, so it is already incremented.

One edge case to be aware of is when you insert a node without any parent then you can't just remove the string concatenation '1.' || because the argument for text2ltree() must be text while id on its own is an integer. Instead you have concatenate the id with an empty string '' ||.

However, I prefer to create this function to get the path and clean up the insert query:

CREATE FUNCTION get_tree_path("table" TEXT, "column" TEXT, parent_path TEXT)
RETURNS LTREE
LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF NOT (parent_path = '') THEN
        parent_path = parent_path || '.';
    END IF;
    RETURN text2ltree(parent_path || currval(pg_get_serial_sequence("table", "column")));
END;
$$

Then, you can call it like this:

INSERT INTO fragment (description, path)
VALUES ('description', get_tree_path('fragment', 'id', '1.9.32'));

If you don't have any parent, then replace the parent_path '1.9.32' with empty text ''.

fares · Answer 3 · 2022-10-29T21:18:48.247

I came up with this, needs the full parent path for insert, but the updates and deletes are simply cascaded :)

create table if not exists tree
(
    -- primary key
    id serial,
    
    -- surrogate key
    path ltree generated always as (coalesce(parent_path::text,'')::ltree || id::text::ltree) stored unique,
    
    -- foreign key
    parent_path ltree, 
    constraint fk_parent
    foreign key(parent_path) 
    references tree(path)
    on delete cascade
    on update cascade,

    -- content
    name text      
);

Using primary key & foreign key to build ltree

3 Answers3