如何从postgresql查询中的嵌套select返回多列

Question

我有一个postgresql查询，如下所示：

update service_requests
set service_request_status = 'Rejected', modified_on = now()
where service_request_id = $1 and service_request_status = 'Submitted'
returning 
(select service_request_id, service_request_status, requestor_id
from service_requests
where service_request_job_uuid in
(select service_request_job_uuid from service_requests where service_request_id = $1)
)

我得到一个错误：子查询必须只返回一列

但我至少需要返回三个：service_request_id、service_requestrongtatus和requestor_id。它们需要从被更新的记录所属的一组记录中返回。

其想法是：用户向一个或多个我们称之为"专业人士"的人提交服务请求。这些专业人士根据服务请求出价。数据库中每个专业都有一行，它们由service_request_job_uuid列分组在一起。在专业人员拒绝服务请求的情况下，将运行此查询。我们希望返回与此服务请求相对应的所有记录，看看是否有其他专业人员尚未拒绝该服务请求。

但是，如果我不能在第一个嵌套选择中选择多个列，我该如何返回所需的信息？

谢谢。

Answer 1

我想你想要：

with upd as (
update service_requests
set service_request_status = 'Rejected', modified_on = now()
where service_request_id = $1 and service_request_status = 'Submitted'
returning service_request_id 
)
select service_request_id, service_request_status, requestor_id
from service_requests sr
where exists (
select 1 
from service_requests sr1
inner join upd u on u.service_request_id = sr1.service_request_id 
where sr1.service_request_job_uuid = sr.service_request_job_uuid
)

或者，也许：

with upd as (
update service_requests
set service_request_status = 'Rejected', modified_on = now()
where service_request_id = $1 and service_request_status = 'Submitted'
returning service_request_job_uuid 
)
select sr.service_request_id, sr.service_request_status, sr.requestor_id
from service_requests sr
inner join upd u on u.service_request_job_uuid = sr.service_request_job_uuid