PostgreSQL如何将select查询结果添加到临时表中？

Question

我想选择一组行并将它们返回给客户端，但我也想仅将结果集中的主键（整数ID）插入到临时表中，以供同一事务中稍后的连接使用。这是用于同步，后续查询往往涉及到对先前查询结果的连接。最有效的方法是什么？我不愿意执行两次查询，尽管如果将其添加到查询缓存中，它可能会很快。另一种方式是将整个结果集存储到临时表中，然后从临时表中选择。那似乎是浪费的（我只需要在临时表中的整数ID）。如果有一个SELECT INTO TEMP也返回结果，我会很高兴。目前使用的技术是在客户端构建整数ID的数组，并在随后的IN查询中使用该数组。我希望有更有效的方法。我猜这可以通过存储过程来完成？但没有那样的方法吗？

- Eloff

Answer 1

我认为你可以利用Postgres的一项功能，在CTE中进行数据修改步骤。这个功能被使用的更典型的原因是，比如说删除表中的记录然后将它们插入到日志表中。但是，它也可以用于这个目的。下面是一种可能的方法（我手头没有Postgres来测试这个）：

with q as (
      <your query here>
     ),
     t as (
      insert into temptable(pk)
          select pk
          from q
     )
select *
from q;

通常，您在数据修改查询中使用returning子句来捕获被修改的数据。

- Gordon Linoff

---

有趣，我会尝试这些的。使用第二种格式，是否有理由将整个内容都包含在“with”中？“insert into temp select * from ... returning *”本身不应该就可以工作吗？ - Eloff

@Eloff . . . 是的。我只是更喜欢select语句返回数据，而update/delete/insert修改数据，就像其他数据库一样。 - Gordon Linoff

第一个（更便宜的）查询应该实际起作用。 “WITH中的数据修改语句仅执行一次，并始终完成。” 详情请参见：https://dev59.com/3GUo5IYBdhLWcg3wvBeX#15810159 - Erwin Brandstetter

@ErwinBrandstetter . . . 我的担忧是 with 中的 q 是否在 insert 的正确作用域中。 - Gordon Linoff

CTE（公共表达式）互相引用时会自动按顺序执行（在没有交叉引用的情况下执行顺序是任意的）。所以，是的，可见性是可以解决的。我以前用过这个方法。 - Erwin Brandstetter