Simple SQL path query?

Question

I am working through an intro SQL textbook and am confused by the following problem, where we are given the 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 a 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 as how to iterate through this table multiple times to string the path together properly. Any ideas? Thanks all!

@McNets did you delete your answer? I didn't read it all the way through but it looked promising! — ABlueCrayon, Apr 29 '17 at 19:30
BTW, your function does not use the parameter: `name VARCHAR(50)` — McNets, Apr 29 '17 at 19:30
Have a look at some similar question for MySql http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — McNets, Apr 29 '17 at 19:32
@McNets oh I see, well can you still post your answer for Postgres? The exact format does not matter to me, just the idea of how to do the problem. I changed the tag as well. — ABlueCrayon, Apr 29 '17 at 19:41

McNets · Accepted Answer · 2017-04-29T20:06:38.967

4

On Postgres you can use a 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

edited Apr 29 '17 at 20:06

answered Apr 29 '17 at 19:29

McNets

10,352
3
32
61

Great but then how do I get the path as a string from that? – ABlueCrayon Apr 29 '17 at 19:48
Please, add an example of the desired result. – McNets Apr 29 '17 at 19:49
For example, `GetPath('Sirenia');` returns `'Sirenia -> Paenungulata -> Afrotheria'` – ABlueCrayon Apr 29 '17 at 19:50
That gives me `Sirenia->Dugongidae->Trichechidae`, which I don't think is right? At least not compared to my example. – ABlueCrayon Apr 29 '17 at 20:03

Simple SQL path query?

1 Answers1