title: Function to return a table of all children of a node
body: <p>Let's say I've got the following table structure:</p>
  
  <pre><code>| ID | ParentID | Name |
  </code></pre>
  
  <p>I'd like to write a recursive PostgreSQL function for getting all child nodes of a node ID passed to it as a parameter.</p>
  
  <p>Here's my code so far (I only have one part of the function which gets all the children of the passed ID, and now I need the recursive part):</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION GetAllChildren(IN NodeID INTEGER) RETURNS INTEGER AS $$
  DECLARE
      Crs CURSOR FOR SELECT ID, ParentID, Name FROM Tree WHERE ParentID=NodeID;
      VarRow Tree%ROWTYPE;
  BEGIN
      OPEN Crs;
  
      CREATE TEMPORARY TABLE TBL(
          ID SERIAL,
          ParentID INTEGER,
          Name CHARACTER(100)
      );
  
      LOOP
          FETCH Crs INTO VarRow;
          IF VarRow IS NULL THEN
              EXIT;
          END IF;
          INSERT INTO TBL(ID, ParentID, Name) VALUES(VarRow.ID, VarRow.ParentID, VarRow.Name);
      END LOOP;
  
      CLOSE Crs;
  
      RETURN 0;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>Perhaps the biggest problem is that I don't know where to save the output between the calls of the recursion.</p>
  
  <p>If you haven't figured out so far, it's about the adjacency list, getting all the children of a node and printing them out to a table.</p>
  
  <p>Does anyone have a solution?</p>