SQL 无法从 postgres 中的 UPDATE RETURNING 子句中选择

Cannot SELECT from UPDATE RETURNING clause in postgres

提问by markus

I isolatet the problem from a much more complex query. Here the test scenario

我将问题与更复杂的查询隔离开来。这里是测试场景

DROP TABLE test; 
CREATE TABLE test (
  id integer,
  description varchar(100)
);

INSERT INTO test(id, description) VALUES (1,'new'); 
INSERT INTO test(id, description) VALUES (2,'new'); 

If I run the query:

如果我运行查询：

SELECT * FROM test WHERE id IN (UPDATE test set description='test' RETURNING id)

I'm getting the following error:

我收到以下错误：

ERROR: syntax error at or near "test" LINE 1: SELECT * FROM test WHERE id (UPDATE test set description='test' RE... ^

错误：在“test”第 1 行或附近出现语法错误：SELECT * FROM test WHERE id (UPDATE test set description='test' RE... ^

***Fehler ***

***费勒 ** *

ERROR: syntax error at or near "test" SQL Status:42601 Zeichen:37

错误：“测试”处或附近的语法错误 SQL 状态：42601 Zeichen：37

However if I only run the statemennt

但是，如果我只运行语句

UPDATE test set value='test' RETURNING id

I get a result with 2 rows:

我得到了 2 行的结果：

1 2

1 2

If I substitude that result I would have a query like:

如果我替代该结果，我将有如下查询：

SELECT * FROM test WHERE id IN (1,2);

with the result:

结果：

1;"test" 2;"test"

1;“测试” 2;“测试”

Why do I not get the same result with my initial statement?

为什么我的初始陈述没有得到相同的结果？

回答by Ants Aasma

Before PostgreSQL 9.1 INSERT/UPDATE/DELETE could only be used as top level statements. This is why you are getting a syntax error.

在 PostgreSQL 9.1 INSERT/UPDATE/DELETE 之前只能用作顶级语句。这就是您收到语法错误的原因。

Starting from 9.1 you can use data-modifying statements with common table expressions. Your example query would look like this:

从 9.1 开始，您可以将数据修改语句与公共表表达式一起使用。您的示例查询如下所示：

WITH updated AS (UPDATE test SET description = 'test' RETURNING id)
SELECT * FROM test WHERE id IN (SELECT id FROM updated);

Be careful with selecting from the just modified table. You can get confusing results that way. Becuse the queries are executed in the same snapshot, the SELECT will not see the effects of the UPDATE statement.

从刚刚修改的表中选择时要小心。这样你会得到令人困惑的结果。因为查询是在同一个快照中执行的，所以 SELECT 不会看到 UPDATE 语句的效果。

回答by JK.

You update two rows in your UPDATEquery, add a WHEREclause to restrict the rows affected.

您更新UPDATE查询中的两行，添加一个WHERE子句来限制受影响的行。

UPDATE test SET description = 'test' WHERE id = 1 RETURNING id

to return a single row.

返回单行。

回答by NPE

Are you missing an IN: ... WHERE id IN (UPDATE ...?

你错过了IN:... WHERE id IN (UPDATE ...吗？

However if I only run the statemennt "UPDATE test set value='test' RETURNING id", I get a result with 2 rows. Why is that?

但是，如果我只运行语句“UPDATE test set value='test' RETURNING id”，我会得到 2 行的结果。这是为什么？

Your UPDATEhas no WHEREclause and therefore it updates every row, of which there are two.

您UPDATE没有WHERE子句，因此它更新每一行，其中有两行。

回答by RMS

UPDATE test set description='test' RETURNING *

would give you the result set that you expect from the initial query.

会给你你期望从初始查询中得到的结果集。

But I suspect you were trying something more complex?

但我怀疑你在尝试更复杂的东西？

回答by Tony318

You are not limiting your where clause. You need to have id = (blahblah) or id IN (blahblah)

您没有限制您的 where 子句。你需要有 id = (blahblah) 或 id IN (blahblah)

回答by Alex Bloom

DROP TABLE IF EXISTS test_tab;

CREATE TABLE test_tab (
  id integer,
  description varchar(100)
);

INSERT INTO test_tab(id, description) VALUES (1,'new'); 
INSERT INTO test_tab(id, description) VALUES (2,'new'); 

SELECT * from test_tab;

DO $$
DECLARE
    myID    test_tab.id%TYPE;
    testID  test_tab.id%TYPE;
    cur_IDs CURSOR for select id from test_tab;
BEGIN
    OPEN cur_IDs;
    LOOP
        FETCH cur_IDs into testID;
        EXIT WHEN testID is NULL;

        UPDATE test_tab SET description='test' WHERE id = testID RETURNING id into myID;
        raise notice 'myID %', myID;
    END LOOP;
    CLOSE cur_IDs;
END$$;


DROP TABLE IF EXISTS test_tab;

回答by bnson

I'm addition from Ants Aasma, if select on same table, using:

我是 Ants Aasma 的补充，如果在同一张桌子上选择，使用：

WITH updated AS (UPDATE test SET description = 'test' RETURNING id, description)
SELECT * FROM updated;