Extensao do postgresql para resolver hierarquia no banco

ltree.sql

-- Baseado na extensão ltree: https://www.postgresql.org/docs/current/static/ltree.html
-- Deve-se criar um banco

$ createdb mydb
$ psql mydb

-- Instalando extensão do ltree no banco
mydb=# CREATE EXTENSION ltree

-- Criando tabela com Categoria
mydb=# CREATE TABLE category (
mydb(# name varchar(80),
mydb(# path ltree);

-- Criando hierarquia baseado em https://github.com/olist/work-at-olist
mydb=# INSERT INTO category values ('Books', 'Books');
INSERT 0 1
mydb=# INSERT INTO category values ('Games', 'Games');
INSERT 0 1
mydb=# INSERT INTO category values ('XBOX 360', 'Games.XBOX_360');
INSERT 0 1
mydb=# INSERT INTO category values ('Console', 'Games.XBOX_360.Console');
INSERT 0 1
mydb=# INSERT INTO category values ('Games', 'Games.XBOX_360.Games');
INSERT 0 1
mydb=# INSERT INTO category values ('Accessories', 'Games.XBOX_360.Accessories');
INSERT 0 1
mydb=# INSERT INTO category values ('XBOX One', 'Games.XBOX_One');
INSERT 0 1
mydb=# INSERT INTO category values ('Console', 'Games.XBOX_One.Console');
INSERT 0 1
mydb=# INSERT INTO category values ('Games', 'Games.XBOX_One.Games');
INSERT 0 1
mydb=# INSERT INTO category values ('Accessories', 'Games.XBOX_One.Accessories');
INSERT 0 1
mydb=# SELECT * FROM category;
    name     |            path            
-------------+----------------------------
 Books       | Books
 Games       | Games
 XBOX 360    | Games.XBOX_360
 Console     | Games.XBOX_360.Console
 Games       | Games.XBOX_360.Games
 XBOX One    | Games.XBOX_One
 Console     | Games.XBOX_One.Console
 Games       | Games.XBOX_One.Games
 Accessories | Games.XBOX_One.Accessories
 Accessories | Games.XBOX_360.Accessories
(10 rows)
     
-- Creating index to speed up search;
mydb=# CREATE INDEX path_gist ON category USING GIST (path);

-- Querying Channels
mydb=# SELECT * FROM category WHERE path ~ '*{1}';
 name  | path  
-------+-------
 Books | Books
 Games | Games
(2 rows)

-- Querying for Categories from Games
mydb=# SELECT * FROM category WHERE path ~ 'Games.*{1}';
   name   |      path      
----------+----------------
 XBOX 360 | Games.XBOX_360
 XBOX One | Games.XBOX_One
(2 rows)

-- Querying subcategories of Games > XBOX 360
mydb=# SELECT * FROM category WHERE path ~ 'Games.XBOX_360.*{1}';
    name     |            path            
-------------+----------------------------
 Console     | Games.XBOX_360.Console
 Games       | Games.XBOX_360.Games
 Accessories | Games.XBOX_360.Accessories
(3 rows)

-- Querying subcategories of Games > XBOX One
SELECT * FROM category WHERE path ~ 'Games.XBOX_One.*{1}';
    name     |            path            
-------------+----------------------------
 Console     | Games.XBOX_One.Console
 Games       | Games.XBOX_One.Games
 Accessories | Games.XBOX_One.Accessories
(3 rows)

-- Querying for all categoris and subcategoris of Games:     
 mydb=# SELECT * FROM category WHERE path ~ 'Games.*{1,}';
    name     |            path            
-------------+----------------------------
 XBOX 360    | Games.XBOX_360
 Console     | Games.XBOX_360.Console
 Games       | Games.XBOX_360.Games
 XBOX One    | Games.XBOX_One
 Console     | Games.XBOX_One.Console
 Games       | Games.XBOX_One.Games
 Accessories | Games.XBOX_One.Accessories
 Accessories | Games.XBOX_360.Accessories
(8 rows)

     
-- Querying for Parents
     
 mydb=# SELECT * FROM category WHERE path @> 'Games.XBOX_360.Console';
   name   |          path          
----------+------------------------
 Games    | Games
 XBOX 360 | Games.XBOX_360
 Console  | Games.XBOX_360.Console
(3 rows)

mydb=# SELECT * FROM category WHERE path @> 'Games.XBOX_360';
   name   |      path      
----------+----------------
 Games    | Games
 XBOX 360 | Games.XBOX_360
(2 rows)