CODEWITHSUNDEEP
sqlpostgresqlrecursion
Alexey Petrushin
Alexey Petrushin

Reputation: 1351

WITH RECURSIVE SQL failed in PostgreSQL

I'm trying to execute the following recursive query in PostgreSQL, but it complains

with
  animals as (
    select *
    from (values
      ('cat', 'feline'),
      ('feline', 'mammal'),
      ('dog', 'canidae'),
      ('canidae', 'mammal'),
      ('mammal', 'animal')
    ) as t (child, parent)
  ),
  recursive ranimals as (
      select child, parent
      from animals
      where child = 'cat'
    union
      select child, parent
      from animals a, ranimals ra
      where ra.parent = a.child
  )
select * from ranimals;

It fails with the following message:

ERROR:  syntax error at or near "ranimals"
LINE 12:   recursive ranimals as (

Why?

Upvotes: 1

Views: 1027

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The syntax is confusing. The clause is with recursive for the entire with. recursive is not a modifier on a given CTE. So, try this:

with recursive
  animals as (
    select *
    from (values
      ('cat', 'feline'),
      ('feline', 'mammal'),
      ('dog', 'canidae'),
      ('canidae', 'mammal'),
      ('mammal', 'animal')
    ) as t (child, parent)
  ),
  ranimals as (
      select child, parent
      from animals
      where child = 'cat'
    union all
      select ra.child, a.parent
      from animals a join
           ranimals ra
           on ra.parent = a.child
  )
select * from ranimals;

I also fixed the join syntax, fixed the select to use table aliases for the column, and changed the union to union all.

This version actually runs. Here is a SQL Fiddle.

Upvotes: 3

Related Questions