Simple SQL path query?

Question

I work through the built-in SQL tutorial and am confused by the following problem, where we are given a table and values:

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');

And I want to write a function that will return a text string representing the path from the given name to the desired root

My attempt:

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 $$

However, I am confused about how to repeat this table many times in order to correctly associate the path. Any ideas? Thanks everyone!

Answer 1

In Postgres, you can use the RECURSIVE query :

WITH RECURSIVE Rec as 
(
    SELECT id, parent_id, Name 
    FROM   Hierarchy
    WHERE  Name = 'Sirenia'
    UNION ALL
    SELECT     Hierarchy.id, Hierarchy.parent_id, Hierarchy.Name
    FROM       Hierarchy
    INNER JOIN Rec
    ON         Hierarchy.id = Rec.parent_Id
)
SELECT string_agg(Name, '->') path
FROM   Rec;

|                path               |
|:---------------------------------:|
| Sirenia->Paenungulata->Afrotheria |

Rextester here