Ordering a WITH RECURSIVE query in Postgres

Question

Hi,

I'm executing a recursive query in Postgres to retrieve a list of emails and their threaded children as follows:

WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
  SELECT  id, 
          title,
          array[id] AS path,
          parent_id, 
          1 AS depth
  FROM    emails
  WHERE   parent_id IS NULL

  UNION ALL

  SELECT  emails.id,
          emails.title,
          cte.path || emails.id,
          emails.parent_id, 
          cte.depth + 1 AS depth
  FROM    emails
          JOIN cte ON emails.parent_id = cte.id
)
SELECT id, title, path, parent_id, depth FROM cte
ORDER BY path;

How would go about changing the order of the list (for example sorting on title) before finding children emails. I obviously need to keep the outer ORDER BY so that the list is retrieved in it's tree order, and Postgres won't let me insert an ORDER BY clause before the UNION ALL.

Thanks,

Answer 1

Create a view consisting of the first part of your query, ordered by title. Maybe something like this?

      CREATE VIEW title_Sort AS
      SELECT  id,  
      title, 
      array[id] AS path, 
      parent_id,  
      1 AS depth  
      FROM    emails 
      WHERE   parent_id IS NULL 
      ORDER BY title;

Then UNION ALL that view with your other query as you did before. I think that will work. On my netbook right now so I can't test :/

Answer 2

This is untested, but usually i can add any ORDER BY before a union so long as there are parentheses...

WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
(  SELECT  id, 
          title,
          array[id] AS path,
          parent_id, 
          1 AS depth
  FROM    emails
  WHERE   parent_id IS NULL
  ORDER BY title
)
  UNION ALL

  SELECT  emails.id,
          emails.title,
          cte.path || emails.id,
          emails.parent_id, 
          cte.depth + 1 AS depth
  FROM    emails
          JOIN cte ON emails.parent_id = cte.id
)
SELECT id, title, path, parent_id, depth FROM cte
ORDER BY path;