如何选择返回结果 [英] How to SELECT the result of RETURNING

问题描述

如何放置 RETURNING 子句的结果进入PostgreSQL中的SELECT吗?也就是说，我希望SELECT语句返回任何RETURNING返回的值.

How to put the result of a RETURNING clause into a SELECT in PostgreSQL? I.e., I would like the SELECT statement to return whatever RETURNING returns.

我尝试了以下两种语法，但它们似乎都不起作用:

I have tried the following two syntaxes, none of them seem to work:

(1) SELECT (INSERT INTO ... RETURNING *)

(2) SELECT * FROM (INSERT INTO ... RETURNING *) ...

您可能会问，尽管RETURNING已经给了我想要的结果，但为什么我还是要这样做呢? 这是因为我有一个使用两个WITH子句的结构，并且插入将在第二个子句中，但是我也需要第一个子句的结果. (请参阅

You might ask, why I'm trying to do this, in spite of RETURNING already giving me the desired result? It's because I have a structure using two WITH clauses, and the insertion would be in the second one, but I also need the results of the first one. (See related question.)

备注:不幸的是，我无法提供具体的SQL查询(因为它是在封闭源产品中)，但是我认为这个问题足够通用，无需此具体查询就可以回答.

Remark: unfortunately, I cannot provide the concrete SQL query (because it is in a closed source product), but I think the question is generic enough to be answerable without this concrete query.

编辑添加了示例:

假设我们有此表:

CREATE TABLE test(id INT, name VARCHAR)

这有效:

INSERT INTO test VALUES (1, 'Joe') RETURNING *

我正在尝试做的，但是不起作用:

What I'm trying to do, but does not work:

SELECT INSERT INTO test VALUES (1, 'Joe') RETURNING *

SELECT * FROM INSERT INTO test VALUES (1, 'Joe') RETURNING *

推荐答案

insert ... returning *将已经返回结果集，无需在其中添加SELECT语句.

insert ... returning * will already return a result set, there is no need to add a SELECT statement to it.

但是，如果您坚持要做的话

But if you insist, you could do

with i as (
   INSERT INTO test VALUES (1, 'Joe') 
   RETURNING *
)
select *
from i;

但是返回的内容与以下内容完全相同:

But that returns exactly the same thing as:

INSERT INTO test VALUES (1, 'Joe') 
RETURNING *

这篇关于如何选择返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！