Таинственный рекурсивный SQL запрос
Я плохо понимаю логику рекурсивных запросов, есть запрос
| Код |
UPDATE "Table" main_table
SET
"Field1" =
(
WITH RECURSIVE
Hier("Field2", "Fileld3", "Fileld4")
AS
(
SELECT 1 AS "Field2", "Fileld3", "Fileld4"
FROM "Table" t1
WHERE t1."Fileld3" = main_table."Fileld3"
UNION ALL
SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM "Table" t2
INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
)
SELECT h."Field2" FROM Hier h WHERE h."Fileld4" <> 1 LIMIT 1
)
,
"Fileld4" =
(
WITH RECURSIVE
Hier("Fileld3", "Fileld4")
AS
(
SELECT "Fileld3", "Fileld4"
FROM "Table" t1
WHERE t1."Fileld3" = main_table."Fileld3"
UNION ALL
SELECT t2."Field2", t2."Fileld4" FROM "Table" t2
INNER JOIN Hier h ON ( t2."@Fileld3" = h."Fileld4" )
)
SELECT COUNT(*) FROM Hier
)
|
Можно ли вынести стартовую часть рекурсии, то есть сделать что-то типа этого?
| Код |
WITH my("Fileld3", "Fileld4") as
(
SELECT t1."Fileld3", t1."Fileld4"
FROM "Table" t1
INNER JOIN "Table" t2
ON (t1."Fileld3" = t2."Fileld3")
)
UPDATE "Table" main_table
SET
"Field1" =
(
WITH RECURSIVE
Hier("Field2", "Fileld3", "Fileld4")
AS
(
SELECT 1 AS "Field2", "Fileld3", "Fileld4"
FROM my
UNION ALL
SELECT h."Field2" + 1 AS "Field2", k2."Fileld3", k2."Fileld4" FROM "Table" t2
INNER JOIN Hier h ON ( t2."Fileld3" = h."Fileld4" )
)
SELECT h."Field2" FROM Hier ht WHERE ht."Fileld4" <> 1 LIMIT 1
)
,
"Fileld4" =
(
WITH RECURSIVE
Hier("Fileld3", "Fileld4")
AS
(
SELECT "Fileld3", "Fileld4"
FROM my
UNION ALL
SELECT t2."Field2", t2."Fileld4" FROM "Table" t2
INNER JOIN Hier h ON ( t2."@Fileld3" = h."Fileld4" )
)
SELECT COUNT(*) FROM Hier
)
|
Можно ли верхний запрос заменить нижним?
|