0

I'm trying to define a recursion on a view that I created with "with". However, there is a syntax error. Is is not possible to define a temp. view with "with" and then also define a recrusion with "with"?

Thanks in advance for your help!

select version();

Does return: PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit

with symmMergesWith as (
        select *
        from (
             select m1.sea1, m1.sea2
             from mergeswith m1

             union

             select m2.sea2, m2.sea1
             from mergeswith m2
         ) as mws
),

recursive reachable(city, sea) as (
    select l.city, l.sea
    from located l
    where l.city = 'Athenes'

    union

    select located.city, symmMergesWith.sea2
    from located, symmMergesWith
    where sea = symmMergesWith.sea1
)
select * from reachable;

[42601] ERROR: Syntaxerror et "reachable"

Nicolas Huber
  • 3
  • 2
  • `recursive` must follow `with` whatever of cte's is recursive in fact. – Serg Apr 03 '22 at 12:58
  • Sorry, I'm not sure If I understand you. Do you mean that recursive alwasy needs a "with" before? Because I tried this as well, but then I get the same error but at "with". – Nicolas Huber Apr 03 '22 at 13:55
  • `with recursive symmMergesWith(..) as ( .. ), reachable(..) as(..) select ..` – Serg Apr 03 '22 at 14:23
  • Oh wow, now I get it... Thank you for your help! Also thanks for linking the relevant thread about CTE. – Nicolas Huber Apr 04 '22 at 09:30

0 Answers0