PostgreSQL, иерархический, дерево категорий

Question

ENV: postgresql-8.4

Я пытаюсь построить дерево категорий. В основном я ожидаю окончательный вывод такой:

CategoryName 
categoryPath
leafcategory

например:

Цифровая камера
Электроника::: Цифровая камера 
правда

Структура таблицы

CREATE TABLE категории (
    id  SERIAL PRIMARY KEY,
    категориид бигинт,
    категория парентид бигинт,
    текст названия категории,
    статус целое число ПО УМОЛЧАНИЮ 0,
    текст lang,
    текст Eysiteid,
    текст страны,
    временный текст,
    листовая категория);

Пока что я получил это, но не работает. Любая помощь будет высоко оценен:

С деревом RECURSIVE (CategoryID, CategoryParentID, CategoryName, category_tree, глубина) 
КАК (ВЫБРАТЬ 
        CategoryID,
        CategoryParentID,
        CategoryName,
        CategoryName AS category_tree,
        0 как глубина 
    ОТ категории 
    ГДЕ CategoryParentID НЕДЕЙСТВИТЕЛЕН 
СОЮЗ ВСЕХ 
    ВЫБРАТЬ 
        c.CategoryID,
        c.CategoryParentID,
        c.CategoryName,
        tree.category_tree  || '/' || c.CategoryName AS category_tree,
        глубина +1 как глубина 
    ИЗ дерева 
        СОВМЕСТНЫЕ категории c ON (tree.category_tree  = c.CategoryParentID)) 
SELECT * FROM дерева ORDER BY category_tree;

Образец из базы данных

кошка => выбрать * из категорий;
  id   | CategoryID | категория парентид | имя категории | статус | язык | эейситид | страна | Темпид | leafcategory 
-------+------------+------------------+--------------------------------+--------+------+------------+---------+--------+--------------
     1 |         -1 |                0 | Корень | 1 | en | 0 | нас | | е
     2 |      20081 |               -1 | Антиквариат | 1 | en | 0 | нас | | е
    17 |       1217 |            20081 | Примитивы | 0 | en | 0 | нас | | T
    23 |      22608 |            20081 | Репродукция Антиквариат | 0 | en | 0 | нас | | T
    24 |         12 |            20081 | Другое | 0 | en | 0 | нас | | T
    25 |        550 |               -1 | Искусство | 1 | en | 0 | нас | | е
    29 |       2984 |               -1 | Детка | 1 | en | 0 | нас | | е

Answer 1

Похоже, вы присоединились не на том поле.

 --  create some test data
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE categories
    -- ( id  SERIAL PRIMARY KEY
    ( categoryid SERIAL PRIMARY KEY
    , categoryparentid bigint REFERENCES categories(categoryid)
    , categoryname text
    -- , status integer DEFAULT 0
    -- , lang text
    -- , ebaysiteid text
    -- , country text
    -- , tempid text
    -- , leafcategory boolean
        );
INSERT INTO categories(categoryid,categoryparentid) SELECT gs, 1+(gs/6)::integer
FROM generate_series(1,50) gs;

UPDATE categories SET categoryname = 'Name_' || categoryid::text;
UPDATE categories SET categoryparentid = NULL WHERE categoryparentid <= 0;
UPDATE categories SET categoryparentid = NULL WHERE categoryparentid  >= categoryid;


WITH RECURSIVE tree (categoryid, categoryparentid, categoryname, category_tree, depth)
AS (
    SELECT
        categoryid
        , categoryparentid
        , categoryname
        , categoryname AS category_tree
        , 0 AS depth
    FROM categories
    WHERE categoryparentid IS NULL
UNION ALL
    SELECT
        c.categoryid
        , c.categoryparentid
        , c.categoryname
        , tree.category_tree  || '/' || c.categoryname AS category_tree
        , depth+1 AS depth
    FROM tree
        JOIN categories c ON tree.categoryid  = c.categoryparentid
    )
SELECT * FROM tree ORDER BY category_tree;

РЕДАКТИРОВАТЬ: другая ("не функциональная") запись для рекурсивного, кажется, работает лучше:

WITH RECURSIVE tree AS (
    SELECT
        categoryparentid AS parent
        , categoryid AS self
        , categoryname AS treepath
        , 0 AS depth
    FROM categories
    WHERE categoryparentid IS NULL
UNION ALL
    SELECT
        c.categoryparentid AS parent
        , c.categoryid AS self
        , t.treepath  || '/' || c.categoryname AS treepath
        , depth+1 AS depth
    FROM categories c
    JOIN tree t ON t.self  = c.categoryparentid
    )
SELECT * FROM tree ORDER BY parent,self
   ;

ОБНОВЛЕНИЕ: в исходном запросе вы должны заменить

WHERE CategoryParentID IS NULL

от:

WHERE CategoryParentID = 0

или, может быть, даже:

WHERE COALESCE(CategoryParentID, 0) = 0

Answer 2

Посмотрите на эту суть, это более или менее то, что вы хотите сделать. В вашем случае я бы лучше использовал LTree материализованное расширение Postgresql.