without_scope

This topic has honestly been covered really well by the various PostgreSQL-covered bloggers. I’m simply hoping to expose my handful of friends who read this blog to some of the new beauty of 8.4.

Recursive queries in PostgreSQL are part of Common Table Expressions or CTEs. They’re kinda like views defined on demand as part of a query.

Jumping right into code

I think the perfect example for recursive queries is a tree structure such as categories. So that’s what we’ll build:

Now, if we want to select all categories that are children of the ‘three’ category, we can with one recursive query:

Let’s go through that line by line.

• Line 1: we create a recursive CTE called all_categories
• Line 2: we define the first query executed which selects the initial data
• Line 3: for a recursive query, PostgreSQL requires a UNION in the CTE. This UNIONs the initial data with the data collected through recursion.
• Line 4: defines the query that actually does the recursion. This query joins the categories table with the all_categories CTE.
• Line 5: query the CTE we just defined.

And the results of the single query:

 id | parent_id | name  
—-+———–+——-
  7 |         3 | no
  8 |         3 | yes
  9 |         3 | maybe
 10 |         8 | good
 11 |         8 | bad
 12 |         8 | evil
(6 rows)

I generally find this far more palatable than the various other solutions I’ve seen that involved either mountains of queries, mangling the schema or both.