title: Recursive query used for transitive closure
body: <p>I've created a simple example to illustrate transitive closure using recursive queries in PostgreSQL.</p>
  
  <p>However, something is off with my recursive query. I'm not familiar with the syntax yet so this request may be entirely noobish of me, and for that I apologize in advance. If you run the query, you will see that node 1 repeats itself in the path results. Can someone please help me figure out how to tweak the SQL?</p>
  
  <pre><code>/*           1
             /   \
            2     3
           / \   /
          4  5  6
         /
        7
       / \
      8   9
  */
  
  create table account(
  acct_id INT,
  parent_id INT REFERENCES account(acct_id),
  acct_name VARCHAR(100),
  PRIMARY KEY(acct_id)
  );
  
  insert into account (acct_id, parent_id, acct_name) values (1,1,'account 1');
  insert into account (acct_id, parent_id, acct_name) values (2,1,'account 2');
  insert into account (acct_id, parent_id, acct_name) values (3,1,'account 3');
  insert into account (acct_id, parent_id, acct_name) values (4,2,'account 4');
  insert into account (acct_id, parent_id, acct_name) values (5,2,'account 5');
  insert into account (acct_id, parent_id, acct_name) values (6,3,'account 6');
  insert into account (acct_id, parent_id, acct_name) values (7,4,'account 7');
  insert into account (acct_id, parent_id, acct_name) values (8,7,'account 8');
  insert into account (acct_id, parent_id, acct_name) values (9,7,'account 9');
  
  WITH RECURSIVE search_graph(acct_id, parent_id, depth, path, cycle) AS (
          SELECT g.acct_id, g.parent_id, 1,
            ARRAY[g.acct_id],
            false
          FROM account g
        UNION ALL
          SELECT g.acct_id, g.parent_id, sg.depth + 1,
            path || g.acct_id,
            g.acct_id = ANY(path)
          FROM account g, search_graph sg
          WHERE g.acct_id = sg.parent_id AND NOT cycle
  )
  SELECT path[1] as Child,parent_id as Parent,path || parent_id as path FROM search_graph
  ORDER BY path[1],depth;
  </code></pre>