una terminada recursivo recursividad recursivas recursiva máxima jerarquica instrucción hacer finalizar ejemplos datos consultas consulta como arboles antes agotó sql postgresql tree common-table-expression recursive-query

sql - recursivo - instrucción terminada. se agotó la recursividad máxima(100) antes de finalizar la instrucción.



Estructura del árbol y recursión (1)

Utilizando una base de datos PostgreSQL 8.4.14, tengo una tabla que representa una estructura de árbol como el siguiente ejemplo:

CREATE TABLE unit ( id bigint NOT NULL PRIMARY KEY, name varchar(64) NOT NULL, parent_id bigint, FOREIGN KEY (parent_id) REFERENCES unit (id) ); INSERT INTO unit VALUES (1, ''parent'', NULL), (2, ''child'', 1) , (3, ''grandchild A'', 2), (4, ''grandchild B'', 2);

id | name | parent_id ----+--------------+----------- 1 | parent | 2 | child | 1 3 | grandchild A | 2 4 | grandchild B | 2

Quiero crear una Lista de control de acceso para esas unidades, donde cada unidad puede tener su propia ACL o heredarla del ancestro más cercano con una ACL propia.

CREATE TABLE acl ( unit_id bigint NOT NULL PRIMARY KEY, FOREIGN KEY (unit_id) REFERENCES unit (id) ); INSERT INTO acl VALUES (1), (4);

unit_id --------- 1 4

Estoy usando una vista para determinar si una unidad está heredando su ACL de un ancestro:

CREATE VIEW inheriting_acl AS SELECT u.id AS unit_id, COUNT(a.*) = 0 AS inheriting FROM unit AS u LEFT JOIN acl AS a ON a.unit_id = u.id GROUP BY u.id;

unit_id | inheriting ---------+------------ 1 | f 2 | t 3 | t 4 | f

Mi pregunta es: ¿cómo puedo obtener la unidad más cercana que NO está heredando la ACL de un antepasado? Mi resultado esperado debe ser similar a la siguiente tabla / vista:

unit_id | acl ---------+------------ 1 | 1 2 | 1 3 | 1 4 | 4


Una consulta con un CTE recursivo podría hacer el trabajo. Requiere PostgreSQL 8.4 o posterior:

WITH RECURSIVE next_in_line AS ( SELECT u.id AS unit_id, u.parent_id, a.unit_id AS acl FROM unit u LEFT JOIN acl a ON a.unit_id = u.id UNION ALL SELECT n.unit_id, u.parent_id, a.unit_id FROM next_in_line n JOIN unit u ON u.id = n.parent_id AND n.acl IS NULL LEFT JOIN acl a ON a.unit_id = u.id ) SELECT unit_id, acl FROM next_in_line WHERE acl IS NOT NULL ORDER BY unit_id

La condición de salto en el segundo tramo de la UNION es n.acl IS NULL . Con eso, la consulta deja de atravesar el árbol tan pronto como se encuentra un acl .
En el SELECT final solo devolvemos las filas donde se encontró un acl . Voilá.

Como un aparte: es un anti-patrón para usar el id . Genérico, no descriptivo como nombre de columna. Lamentablemente, algunos ORM lo hacen de forma predeterminada. Llámalo unit_id y no tienes que usar alias en las consultas todo el tiempo.