CODEWITHSUNDEEP
postgresqlrecursion
PlatinTato
PlatinTato

Reputation: 378

recursive call in nonrecursive statement

this is the "solution" to a question from a course i am taking. but when i copied it into my postgrSQL 9.5.1 program, and it is riddled with errors.

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))

It threw an error in line:1, with a syntaxerror for 'AS'. After looking into some declarations here and trying some stuff, it seemed like changing it to 

CREATE RECURSIVE VIEW Ancestor(Anc,Dec) AS

worked, but now it throws an error the first time i try to call Ancestor in a FROM command. the Error this time is:

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

or in my broken english: 

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

like i said this is not my code. it is a "solution" for a question from a course i am attending. i am trying to fix this answer but dont want to drift too far away from the original intent with this code.

cheers - Jacobus

Upvotes: 1

Views: 497

Answers (2)

Renzo
Renzo

Reputation: 27424

The error is caused by the fact that in your view definition, you have two different recursive clauses, while only one is admitted.

In the PostgreSQL manual the recursive view is defined as following:

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.

(note that the last statement require that you use CREATE RECURSIVE VIEW Ancestor(Anc,Dec))

Then, in the page about 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).

So, you should change your query by using only a recursive subselect.

Edit

Here is a possible solution, with a small modification of the query published by @JacobusConradi in a comment below:

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

The modification concerns: 1) the elimination of DISTINCT (we can assume that child is a primary key of the original table, and in any case the use of the UNION operator automatically eliminates duplicates), 2) the addition of a test for null values, otherwise in the result would appear tuples with null values for either anc or dec.

Upvotes: 1

Nick Barnes
Nick Barnes

Reputation: 21346

As Renzo pointed out, you've got two issues to work around:

  • Only one recursive reference is allowed
  • That recursive reference must appear in the last part of the UNION

The second restriction is easily addressed by combining the last two elements of the UNION into a subquery; instead of A UNION B UNION C UNION D, simply say A UNION B UNION (C UNION D).

As for factoring out the recursive call to a single reference, this is pretty straightforward to do with a common table expression (CTE), giving you something like this:

CREATE RECURSIVE VIEW Ancestor(Anc,Dec) 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
(
  WITH cte AS (SELECT * FROM Ancestor)
  SELECT parents.Father AS Anc, cte.Dec As Dec
  FROM windsor.parents, cte
  WHERE parents.child = cte.Anc
  UNION
  SELECT parents.Mother AS Anc, cte.Dec As Dec
  FROM windsor.parents, cte
  WHERE parents.child = cte.Anc
)

Upvotes: 0

Related Questions