title: Simple SQL path query?
body: <p>I am working through an intro SQL textbook and am confused by the following problem, where we are given the table and values:</p>
  
  <pre><code>CREATE TABLE LineageTable (
      parent INT,
      id INT,
      genus_name VARCHAR(30),
      PRIMARY KEY (id)
  );
  
  INSERT INTO LineageTable VALUES
      (3, 1, 'FamilyA'),
      (2, 4, 'FamilyB'),
      (7, 2, 'FamilyC');
  </code></pre>
  
  <p>And I want to write a function that will return a text string representing the path from the a given name to the desired root</p>
  
  <p>My Attempt:</p>
  
  <pre><code>CREATE FUNCTION LineageTable (input VARCHAR(50))
  RETURNS TABLE (input VARCHAR(50))
  AS $$
      BEGIN
          RETURN QUERY 
              SELECT input
              FROM LineageTable1 
              INNER JOIN LineageTable ON LineageTable.parent = LineageTable.id
              WHERE LineageTable1.genus_name = LineageTable1.genus_name;
      END $$
  </code></pre>
  
  <p>However, I am confused as how to iterate through this table multiple times to string the path together properly. Any ideas? Thanks all!</p>