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