postgresql - 非递归语句中的递归调用

Question

这是我正在学习的类(class)中问题的“解决方案”。但是当我将它复制到我的 postgrSQL 9.5.1 程序中时，它充满了错误。

CREATE RECURSIVE VIEW Ancestor AS
((SELECT parents.child AS Dec, parents.father AS Anc
FROM windsor.parents)
UNION
(SELECT parents.child AS Dec, parents.mother AS Anc
FROM windsor.parents)
UNION
(SELECT parents.Father AS Anc, Ancestor.Dec As Dec
FROM windsor.parents,Ancestor
WHERE parents.child = Ancestor.Anc)
UNION
(Select parents.mother AS Anc, Ancestor.Dec As Dec
FROM windsor.parents, Ancestor
WHERE parents.child = Ancestor.Anc))

它在第 1 行抛出一个错误，其中包含“AS”的语法错误。在查看了一些声明之后here并尝试了一些东西，似乎将其更改为

CREATE RECURSIVE VIEW Ancestor(Anc,Dec) AS 

有效，但现在它在我第一次尝试在 FROM 命令中调用 Ancestor 时抛出错误。这次的错误是:

Error: rekursiver Verweis auf Anfrage „ancestor“ darf nicht in ihrem nicht-rekursiven Teilausdruck erscheinen
SQL Status:42P19

或者用我蹩脚的英语:

recursive call for "ancestor" mustn't be in a non-recursive part

就像我说的，这不是我的代码。这是我正在参加的类(class)中问题的“解决方案”。我正在尝试修复此答案，但不想偏离此代码的初衷太远。

干杯 - 雅各布斯

Answer 1

错误是由于在您的 View 定义中，您有两个 不同的递归子句，而只有一个被承认。

在 PostgreSQL manual递归 View 定义如下:

CREATE RECURSIVE VIEW name (columns) AS SELECT ...;is equivalent to

CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT columns FROM name;

A view column list must be specified for a recursive view.

(请注意，您使用 CREATE RECURSIVE VIEW Ancestor(Anc,Dec) 的最后一个语句要求)

然后，在 page关于 WITH RECURSIVE:

If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name. Such a subquery must have the form

non_recursive_term UNION [ ALL | DISTINCT ] recursive_term

where the recursive self-reference must appear on the right-hand side of the UNION. Only one recursive self-reference is permitted per query. (emphasis is mine).

因此，您应该仅使用递归子选择来更改查询。

编辑

这是一个可能的解决方案，对@JacobusConradi 在下面的评论中发布的查询进行了小幅修改:

CREATE RECURSIVE VIEW ancestor(anc, dec) AS 
  SELECT  father AS anc, child AS dec
  FROM windsor.parents 
  WHERE father is not null
     UNION 
  SELECT  mother AS anc, child AS dec
  FROM windsor.parents 
  WHERE mother is not null
     UNION 
  SELECT anc, child AS dec
     FROM windsor.parents, ancestor
     WHERE dec = father OR dec = mother

修改涉及:1)消除DISTINCT(我们可以假设child是原表的主键，无论如何使用UNION 运算符自动消除重复项)，2) 添加空值测试，否则结果中将出现 anc 或 dec 的空值元组。