CODEWITHSUNDEEP
sqlpostgresql
theSemenov
theSemenov

Reputation: 477

How to combine simple with statement and recurcive statement in postgresql

In your SQL statement, you can aggregate some statement into with statement e.g

with first_ds as (
    ...
), second_ds as (
    ...
)
select * from second_ds;

But how to combine statement with recursive e.g:

with first_ds as (
    ...
), 
RECURSIVE second_ds_rec as (
    -- init part
    select 
        1 as level, 
        f.* 
    from first_ds f
    ...

    UNION 

    -- recursive part
    select 
      f.level + 1, 
      f.* 
    from first_ds f
    join second_ds_rec rec on ...
    ...
)
select * from second_ds_rec;

Every time with this construction I have got exception

Upvotes: 1

Views: 39

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

The RECURSIVE keyword should be moved to the first cte:

with RECURSIVE  first_ds as (
    ...
), 
second_ds_rec as (
    -- init part
    select 
        1 as level, 
        f.* 
    from first_ds f
    ...

    UNION 

    -- recursive part
    select 
      f.level + 1, 
      f.* 
    from first_ds f
    join second_ds_rec rec on ...
    ...
)
select * from second_ds_rec;

Upvotes: 1

Related Questions