logo标签列表

PostgreSQL中的嵌套WITH子句

postgresql
9

我正在尝试使用嵌套的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

1个回答
15

错误信息涉及不正确的 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;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接