postgresql - 在 postgresql 中嵌套 with 子句

Question

我正在尝试使用嵌套的 with:

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

INSERT INTO audit_trail(old_email, new_email)
  VALUES ('harold_gim@yahoo.com', 'hgimenez@hotmail.com'),
         ('hgimenez@hotmail.com', 'harold.gimenez@gmail.com'),
         ('harold.gimenez@gmail.com', 'harold@heroku.com'),
         ('foo@bar.com', 'bar@baz.com'),
         ('bar@baz.com', 'barbaz@gmail.com');

with iter2 as (
    with iter1 as (
        select old_email, new_email from audit_trail where old_email = 'harold_gim@yahoo.com'
        ) 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" at character 264
STATEMENT:  with iter2 as (
        with iter1 as (
            select old_email, new_email from audit_trail where old_email = 'harold_gim@yahoo.com'
            ) 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;

显然是语法错误。是否支持嵌套？

PostgreSQL 版本 9.4.4

Answer 1

错误信息涉及不正确的union语法，应该是

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

但是在这种情况下你会得到错误

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

因为可以使用嵌套的with 语句，但是在内部查询中定义的表在外部查询之外是不可见的。使用查询列表:

with iter1 as (
    select old_email, new_email 
    from audit_trail 
    where old_email = 'harold_gim@yahoo.com'
    ),
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;