, Johann Schmitz

Here is a short example on how to build a recursive postgresql query using a Common Table Expression (CTE). A CTE is a kind of subquery which can join itself to build recursive queries.

Let's assume the following simple table layout for some kind of blog application:

CREATE TABLE categories
(
    id serial NOT NULL,
    title character varying(200) NOT NULL,
    parent integer,
    CONSTRAINT categories_pkey PRIMARY KEY (id),
    CONSTRAINT parent_fkey FOREIGN KEY (parent)
    REFERENCES categories (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE pages
(
    id serial NOT NULL,
    title character varying(200) NOT NULL,
    parent integer NOT NULL,
    CONSTRAINT pages_pkey PRIMARY KEY (id),
    CONSTRAINT parent_fkey FOREIGN KEY (parent)
    REFERENCES categories (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
)

The categories table has a nullable parent column which points to the category's parent category. The column is NULL for root categories. Each page belongs to a category (via the parent column in the pages table).

Now, lets fill in some data:

INSERT INTO categories (id, title, parent) VALUES (1, 'Blog', NULL);
INSERT INTO categories (id, title, parent) VALUES (2, 'Computer', 1);
INSERT INTO categories (id, title, parent) VALUES (5, 'Animals', 1);
INSERT INTO categories (id, title, parent) VALUES (3, 'Cats', 5);
INSERT INTO categories (id, title, parent) VALUES (4, 'Dogs', 5);
INSERT INTO categories (id, title, parent) VALUES (6, 'Security', 2);
INSERT INTO pages (id, title, parent) VALUES (1, 'Awesome cat pictures', 3);
INSERT INTO pages (id, title, parent) VALUES (2, 'Some dog movies', 4);
INSERT INTO pages (id, title, parent) VALUES (3, 'About security in IT', 6);
INSERT INTO pages (id, title, parent) VALUES (4, 'New hardware', 2);
INSERT INTO pages (id, title, parent) VALUES (5, 'Welcome', 1);

And here comes the interesting part: Our recursive query to fetch the pages and the category path:

with recursive category_hierarchy (id, title, path) as 
(
    select id, title, '' || title AS path from categories WHERE parent is null
    UNION ALL
    select c.id, c.title, category_hierarchy.path || ' > ' || c.title from categories c
    inner JOIN category_hierarchy ON c.parent = category_hierarchy.id
)
select p.*, hi.path from pages p
JOIN category_hierarchy hi ON p.parent = hi.id

The CTE starts with a query for the root categories (categories whose parent column is NULL). The result is UNIONed with the recursive JOINed result for the result of the categories.

And this is the result:

id  title                   parent  path
1   Awesome cat pictures    3       Blog > Animals > Cats
2   Some dog movies         4       Blog > Animals > Dogs
3   About security in IT    6       Blog > Computer > Security
4   New hardware            2       Blog > Computer
5   Welcome                 1       Blog