CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressionrecursive-query
MaestroGlanz
MaestroGlanz

Reputation: 381

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

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?

Upvotes: 1

Views: 2183

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 2

user330315
user330315

Reputation:

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;

Upvotes: 4

Related Questions