Recursieve CTE — een stuklijst uitklappen zonder in een lus te blijven hangen
WITH RECURSIVE van anker tot afbreekconditie, met cyclusdetectie via een padarray. Waarom UNION ALL geen stijlkeuze is en waarom een dieptelimiet ook mét cyclusdetectie nodig blijft.
Een fiets bestaat uit een frame, twee wielen en een aandrijving. Een wiel bestaat uit een velg, spaken en een band. Zo diep als je wilt. Deze functie klapt zo'n boom uit en rekent onderweg door hoeveel je van elk onderdeel nodig hebt.
WITH RECURSIVE is het stuk SQL waar de meeste mensen op afhaken, meestal omdat de uitleg begint bij
de syntaxis in plaats van bij wat er gebeurt. Lees het als: begin met één rij, en blijf er zolang
mogelijk nieuwe rijen aan vastplakken.
CREATE OR REPLACE FUNCTION productie.stuklijst_uitklappen(
p_artikel_id bigint,
p_aantal numeric DEFAULT 1,
p_max_diepte int DEFAULT 20
)
RETURNS TABLE (
niveau int,
artikel_id bigint,
omschrijving text,
aantal_totaal numeric,
pad bigint[],
is_cyclus boolean
)
LANGUAGE sql
STABLE
AS $$
WITH RECURSIVE uitklap AS (
-- Ankerdeel: het artikel waar we mee beginnen.
SELECT 0 AS niveau,
a.id AS artikel_id,
a.omschrijving,
p_aantal AS aantal_totaal,
ARRAY[a.id] AS pad,
false AS is_cyclus
FROM productie.artikelen a
WHERE a.id = p_artikel_id
UNION ALL
-- Recursieve deel: elk onderdeel van wat we al gevonden hebben.
SELECT u.niveau + 1,
c.onderdeel_id,
a.omschrijving,
u.aantal_totaal * c.aantal,
u.pad || c.onderdeel_id,
c.onderdeel_id = ANY(u.pad)
FROM uitklap u
JOIN productie.stuklijstregels c ON c.artikel_id = u.artikel_id
JOIN productie.artikelen a ON a.id = c.onderdeel_id
WHERE NOT u.is_cyclus
AND u.niveau < p_max_diepte
)
SELECT niveau, artikel_id, omschrijving, aantal_totaal, pad, is_cyclus
FROM uitklap
ORDER BY pad;
$$;
Wijs het ankerdeel en het recursieve deel aan. Wat zorgt ervoor dat dit ooit stopt?
Er staat UNION ALL. Wat zou er veranderen als je er UNION van maakt?
Wat staat er in aantal_totaal, en waarom wordt er vermenigvuldigd in plaats van opgeteld?
De cyclusdetectie is opgesplitst: c.onderdeel_id = ANY(u.pad) staat in de SELECT, maar
NOT u.is_cyclus staat in de WHERE. Waarom niet allebei op dezelfde plek?
Er is cyclusdetectie. Waarom is p_max_diepte dan nog nodig?
ORDER BY pad — waarom sorteren op een array, en wat levert dat op?
Het pad-array groeit bij elke stap. Wat kost dat, en biedt PostgreSQL zelf iets beters?