Recursive query template for CTE and "connect by"

recursive_queries.sql

-- == setup ==
CREATE TABLE tree_table
(
   tree_id          integer         NOT NULL,
   tree_name        varchar(100)    NOT NULL,
   tree_parent_id   integer,
   tree_order       integer,
   active           boolean         NOT NULL
);

ALTER TABLE tree_table
   ADD CONSTRAINT tree_table_pkey
   PRIMARY KEY (tree_id);

ALTER TABLE tree_table
   ADD CONSTRAINT tree_parent_fkey
   FOREIGN KEY (tree_parent_id)
   REFERENCES tree_table;
-- == end setup ==


-- ===================================
-- Common Table Expression (PostgreSQL, Microsoft SQL Server)
-- ===================================
WITH RECURSIVE q AS (
    -- initial subquery
    SELECT h AS h, 1 AS tree_level
         , '/' || h.tree_name AS tree_path
         , ARRAY[h.tree_id] AS breadcrumb
      FROM tree_table h
     WHERE h.tree_id = 1 -- initial ID
  UNION ALL 
    -- recursive subquery
    SELECT hi AS hi, q.tree_level + 1 AS tree_level
         , (q.tree_path || '/') || hi.tree_name AS tree_path
         , q.breadcrumb || hi.tree_id AS breadcrumb
      FROM q JOIN tree_table hi ON hi.tree_parent_id = (q.h).tree_id
)
SELECT (q.h).tree_id
     , (q.h).tree_name
     , (q.h).tree_parent_id
     , (q.h).tree_order
     , (q.h).active
     , q.tree_level
     , q.tree_path
     , q.breadcrumb
  FROM q
 ORDER BY q.breadcrumb, (q.h).tree_order;


-- =====================
-- "Connect By" (Oracle)
-- =====================
 SELECT tree_id
      , tree_name
      , tree_parent_id
      , tree_order
      , active
      , LEVEL AS tree_level
      , SYS_CONNECT_BY_PATH(tree_name, '/') AS tree_path
      , SYS_CONNECT_BY_PATH(tree_id, ',') AS breadcrumb
   FROM tree_table
  START WITH tree_id = 1 -- initial ID
CONNECT BY PRIOR tree_id = tree_parent_id
  ORDER SIBLINGS BY SYS_CONNECT_BY_PATH(tree_id, ','), tree_order;