title: Determine whether a specific entry has any children in a hierarchical database
body: <p>I'm working on a recursive query for a hierarchal table in psql. While I'm able to produce an ordered, hierarchal list, I cannot figure out how to determine whether a parent has any children.</p>
  
  <p>My code at the moment:</p>
  
  <pre><code>WITH RECURSIVE q AS (
    SELECT h, 1 AS level, ARRAY[ordering] AS ordered_path, ARRAY[id] AS breadcrumb
    FROM report h
    WHERE parent IS NULL
    UNION ALL
    SELECT hi, q.level + 1 AS level, ordered_path || ordering, breadcrumb || id FROM q
    JOIN report hi ON hi.parent = (q.h).id )
  SELECT (q.h).id, (q.h).parent, (q.h).name, array_to_json(breadcrumb) AS breadcrumbs,
    row_number() OVER (order by ordered_path) AS flat_order
  FROM q
  ORDER BY ordered_path
  </code></pre>
  
  <p>Which produces the following table:</p>
  
  <pre><code>id  | parent |           name        | ordering |    trail     | rownum 
  ----+--------+-----------------------+----------+--------------+--------
    1 |        | Entry 1               |        1 | [1]          |      1
    2 |      1 | Entry 2               |        1 | [1,2]        |      2
   15 |      2 | Entry 3               |        1 | [1,2,15]     |      3
  159 |      2 | Entry 4               |        2 | [1,2,159]    |      4
   16 |      2 | Entry 5               |        3 | [1,2,16]     |      5
  </code></pre>
  
  <p>Essentially, I'd like a column that shows if a specific entry has any children.  In this example, Entry 5 has no children.</p>
  
  <p>The format of the original table is:</p>
  
  <pre><code>id  |                        name              |  type   | parent | ordering 
  -----+-----------------------------------------+---------+--------+----------
  186 | Entry 1                                  | page    |    172 |       23
  154 | Entry 2                                  | page    |     63 |        3
  169 | Entry 3                                  | page    |    163 |        3
  </code></pre>
  
  <p>Thanks!</p>