closure_table_postgres_functions.sql

----------------------------------------------------------------------------------------
-- This function is used to update paths when inserted a new node
----------------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS after_insert_node();

-- Step 1: Create a function used to update paths

CREATE OR REPLACE FUNCTION after_insert_node() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO prefix_nodes_paths(ancestor_id, descendant_id, depth)
    SELECT
      ancestor_id, NEW.id, depth + 1
    FROM
      prefix_nodes_paths WHERE descendant_id = NEW.parent_id
    UNION ALL
    SELECT NEW.id, NEW.id, 0;
  END IF;
  RETURN NULL;
END;
$$;


-- Add trigger function on prefix_nodes table
CREATE TRIGGER after_insert_node AFTER INSERT ON prefix_nodes FOR EACH ROW EXECUTE PROCEDURE after_insert_node();


--
-- If you want modify the `after_insert_node` function, you must delete the trigger from `prefix_nodes` table first, and re-create it.
--

-- Drop trigger function on a table
DROP TRIGGER IF EXISTS after_insert_node ON prefix_nodes;


----------------
-- Get child nodes
----------------

DROP FUNCTION IF EXISTS get_child_nodes(INTEGER);

-- Create function
CREATE FUNCTION get_child_nodes(INTEGER)
  RETURNS SETOF record
  LANGUAGE SQL
AS $$
SELECT d.id,
       d.is_deleted,
       d.parent_id,
       concat(repeat('-', p.depth), d.name) AS tree,
       p.depth,
       array_to_string(array_agg(crumbs.ancestor_id::character varying order by crumbs.ancestor_id),',','*') breadcrumbs
  FROM prefix_nodes AS d
  JOIN prefix_nodes_paths AS p ON d.id = p.descendant_id
  JOIN prefix_nodes_paths AS crumbs ON crumbs.descendant_id = p.descendant_id
 WHERE p.ancestor_id = $1 AND d.is_deleted = false
 GROUP BY d.id, p.depth
 ORDER BY d.id ASC
 ;
$$

-- Get query result
SELECT * FROM get_child_nodes(22) AS (
  id          INTEGER,
  is_deleted  BOOLEAN,
  parent_id   INTEGER,
  tree        VARCHAR(255),
  depth       INTEGER,
  breadcrumbs VARCHAR(255)
);

TODO

• Replace serial id with uuid
• Delete child nodes
• Move sub tree to another