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?

Answer

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.