带连接的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中进行，而不必进行一次单独的DB往返.

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屋！