SQL - WITH RECURSIVE doesn't work, when there is another query before

Question

I have a (postgresql) query, that goes like

with 
recursive account_tree as (<recursive function depending on path>), -- 1
path as (<some other query>)                                        -- 2
select * from account_tree;

This works perfectly fine.

But when I reorder the with queries to that

with 
path as (<some other query>),                                      -- 2
recursive account_tree as (<recursive function depending on path>) -- 1
select * from account_tree;

it suddenly shows a syntax error. This behaviour doesn't occur, when I have standard non-recursive queries. With non-recursive queries, I can order them, as they please me.

Why does it do that?

user330315 · Accepted Answer · 2021-03-25 13:53:57Z

4

The recursive keyword always goes right behind WITH regardless which of the CTE is actually the recursive one:

with recursive path as (
  <some other query>
), account_tree as (
  <recursive function depending on path>
)
select * 
from account_tree;

answered Mar 25, 2021 at 13:53

user330315

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-03-25 14:01:21Z

2

recursive refers to the entire with clause. So just use:

with recursive path as (<some other query>),                                      -- 2

What recursive really means is that Postgres will search for "table" names first as CTEs and then as tables/views in the recursive part of the CTE. The issue is really resolving identifiers. It has no effect on other CTEs.

edited Mar 25, 2021 at 14:01

answered Mar 25, 2021 at 13:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

MaestroGlanz Over a year ago

So, the recursive makes only queries recursive, which are designed to be recursive. It has no effect on standard queries, right?

Gordon Linoff Over a year ago

@MaestroGlanz . . . It should have no effect on "standard" CTEs.

Collectives™ on Stack Overflow

SQL - WITH RECURSIVE doesn't work, when there is another query before

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related