插入CTE中的行无法加入PostgreSQL

Question

我有一个表 foo

                            Table "public.foo"
      Column       |            Type             |              Modifiers              
-------------------+-----------------------------+-------------------------------------
 foo_id            | uuid                        | not null default uuid_generate_v1()
 col1              | uuid                        | 
 col2              | uuid                        | 
 col3              | uuid                        | not null

我有一个视图 goo_view ，主要从foo中选择，但也加入另一个表

SELECT * from foo LEFT JOIN foo_helper USING (foo_id);

我有以下CTE查询，其中我试图将其插入表 foo 同时返回适当的 goo_view 信息

WITH ins AS (
    INSERT INTO foo (col1,col2, col3) VALUES (111,222,333) RETURNING foo_id
)                              
SELECT v.foo_id, v.col1, v.col2, v.fk1
FROM goo_view v 
JOIN ins 
USING (foo_id)   

但是结果为空。

如果我运行插入物并单独选择，它是否有效，是否有一些导致这种情况失败的时间？

有什么作用？

Answer 1

为什么在CTE中使用插入？

WITH ins AS (
    select 111 as col1, 222 as col2, 333 as col3
) 
. . .

您的语法真的有效吗？我以前没看过。哇，我只是学到了一些东西。这似乎是Postgres语法。

我最好的猜测是您使用的返回子句是返回一个foo_id。。。您刚插入的那个。此foo_id可能不存在于goo_view中，因此没有匹配和空集。

，并且您要做的是在文档中不允许说明：

与彼此同时执行的子仪式 并带有主要查询。因此，使用数据改装 语句中，指定更新的顺序 发生是不可预测的。所有语句都以相同的方式执行 快照（请参阅第13章），因此他们无法"看到"彼此的效果 在目标表上。这减轻了 行更新的实际顺序不可预测性，这意味着 返回数据是传达更改的唯一方法 与子结构和主要查询不同。。。

因此，您不会在视图中看到新行。为了解决此问题，您必须使用returning返回的结果来明确参考from子句中的CTE。

Answer 2

btw：关于隔离级别，（应该）CTE，链式返回，子查询或平原连接之间没有区别。如果"后续"子查询是指实际表元组（即选择），则应"请参见"行询问的行。

在其他情况下，下面具有自加入更新湖的查询无效：

SET search_PATH='tmp';
DROP TABLE ztable CASCADE;
CREATE TABLE ztable
    ( id integer NOT NULL PRIMARY KEY
    , payload varchar
    );
INSERT INTO ztable(id,payload) VALUES (1,'one' ), (2,'two' ), (3,'three')
      ,(4,'four' ), (5,'five' ), (6,'six' );
SELECT * FROM ztable;
    -- This can only work if 
    -- t2 is seen in its original form
    -- **before the update**
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id  = 7-t2.id
    ;
SELECT * FROM ztable;

结果：

CREATE TABLE
INSERT 0 6
 id | payload 
----+---------
  1 | one
  2 | two
  3 | three
  4 | four
  5 | five
  6 | six
(6 rows)
UPDATE 6
 id | payload 
----+---------
  6 | one
  5 | two
  4 | three
  3 | four
  2 | five
  1 | six
(6 rows)

当然，由CTE返回或通过返回而产生的"链式表达式"的元素是虚拟的；他们不是指实际的元素，而是指新鲜的"合成"新实体。

Answer 3

由于该视图从SQL命令开始时从表中的表foo中选择，因此您必须重写：

WITH ins AS (
   INSERT INTO foo (col1,col2, col3) VALUES (111,222,333)
   RETURNING foo_id, col1, col2
   )
SELECT i.foo_id, i.col1, i.col2, h.fk1
FROM   ins i
LEFT   JOIN foo_helper h USING (foo_id);

这样，您可以使用新插入的值，然后加入次要表。