Estoy trabajando en un libro de texto de introducción SQL y estoy confundido por el siguiente problema, donde se nos da la tabla y los valores:
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');Y quiero escribir una función que devuelva una cadena de texto que represente la ruta desde el nombre dado hasta la raíz deseada
Mi intento:
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 $$Sin embargo, estoy confundido sobre cómo iterar a través de esta tabla varias veces para unir la ruta correctamente. ¿Algunas ideas? ¡Gracias a todos!
En Postgres puede usar una consulta RECURSIVA :
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 aquí