Nested with clause in postgresql

Question

I am trying to use nested with:

CREATE TABLE audit_trail (
      old_email TEXT NOT NULL,
      new_email TEXT NOT NULL
);

INSERT INTO audit_trail(old_email, new_email)
  VALUES ('[email protected]', '[email protected]'),
         ('[email protected]', '[email protected]'),
         ('[email protected]', '[email protected]'),
         ('[email protected]', '[email protected]'),
         ('[email protected]', '[email protected]');

with iter2 as (
    with iter1 as (
        select old_email, new_email from audit_trail where old_email = '[email protected]'
        ) select a.old_email, a.new_email from audit_trail a join iter1 b on (a.old_email = b.new_email)
) select * from iter1 union iter2;

I got this error:

ERROR:  syntax error at or near "iter2" at character 264
STATEMENT:  with iter2 as (
        with iter1 as (
            select old_email, new_email from audit_trail where old_email = '[email protected]'
            ) select a.old_email, a.new_email from audit_trail a join iter1 b on (a.old_email = b.new_email)
    ) select * from iter1 union iter2;
ERROR:  syntax error at or near "iter2"
LINE 5: ) select * from iter1 union iter2;

Syntax error, obviously. Is nested with supported?

PostgreSQL version 9.4.4

Answer 1

The error message concerns inproper union syntax, it should be

...
select * from iter1 
union 
select * from iter2;

In this case however you will get the error

ERROR:  relation "iter1" does not exist
LINE 6: select * from iter1 

because nested with statement can be used but the table defined in an inner query is not visible outside the outer query. Use list of queries:

with iter1 as (
    select old_email, new_email 
    from audit_trail 
    where old_email = '[email protected]'
    ),
iter2 as (
    select a.old_email, a.new_email 
    from audit_trail a 
    join iter1 b on (a.old_email = b.new_email)
    )
select * from iter1 
union 
select * from iter2;