带有连接的 Postgres RETURNING 子句 [英] Postgres RETURNING clause with join

问题描述

在下面的 SQL 中，我怎样才能使 RETURNING 子句加入其他内容并返回加入的行?在这里它只返回 mytable 中更新的行，但我希望它返回连接到另一个表中的某些内容的行.

In the following SQL, how could I make the RETURNING clause join to something else and return the joined row(s)? Here it only returns the row from mytable that was updated, but I'd like it to return that row joined to something in another table.

UPDATE mytable
SET    status = 'A' 
FROM  (
   SELECT myid
   FROM   mytable
   WHERE  status = 'B'
   ORDER BY mycolumn
   LIMIT  100
   FOR   UPDATE
   ) sub
WHERE  mytable.myid = sub.myid
RETURNING *;

我可以从我的客户端应用程序执行另一个查询，但我想知道是否有一种方法可以从 Postgres 中执行此操作，而不必单独往返数据库.

I could do another query from my client application, but I'd like to know if there's a way to do it from within Postgres within having to make a separate roundtrip to the DB.

推荐答案

FROM 子句中的任何内容对于 RETURNING 来说都是公平的游戏:

Anything in the FROM clause is fair game for RETURNING:

UPDATE mytable
SET status = 'A'
FROM
  (
    SELECT
      myid
    FROM mytable
    WHERE status = 'B'
    ORDER BY mycolumn
    LIMIT 100
    FOR UPDATE
  ) sub
  JOIN jointable j ON j.id = sub.myid
WHERE mytable.myid = sub.myid
RETURNING mytable.mycolumn, j.othercolumn
;    

这篇关于带有连接的 Postgres RETURNING 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！