postgresql 通过 RAW plpgsql 中的 UPDATE 语句获取受影响的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13647925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting Affected Rows by UPDATE statement in RAW plpgsql
提问by Olivier MATROT
This has been asked multiple times hereand here, but none of the answers are suitable in my case because I do not want to execute my update statement in a PL/PgSQL function and use GET DIAGNOSTICS integer_var = ROW_COUNT.
这已经在这里和这里被多次询问，但没有一个答案适合我的情况，因为我不想在 PL/PgSQL 函数中执行我的更新语句并使用GET DIAGNOSTICS integer_var = ROW_COUNT.
I have to do this in raw SQL.
我必须在原始 SQL 中执行此操作。
For instance, in MS SQL SERVER we have @@ROWCOUNT which could be used like the following :
例如，在 MS SQL SERVER 中，我们有 @@ROWCOUNT 可以像下面这样使用：
UPDATE <target_table>
SET Proprerty0 = Value0
WHERE <predicate>;
SELECT <computed_value_columns>
FROM <target>
WHERE @@ROWCOUNT > 0;
In one roundtrip to the database I know if the update was successfull and get the calculated values back.
在对数据库的一次往返中，我知道更新是否成功并取回计算值。
What could be used instead of '@@ROWCOUNT' ? Can someone confirm that this is in fact impossible at this time ?
什么可以代替 '@@ROWCOUNT' ？有人可以确认这在这个时候实际上是不可能的吗？
Thanks in advance.
提前致谢。
EDIT 1: I confirm that I need to use raw SQL (I wrote "raw plpgsql" in the original description).
编辑 1：我确认我需要使用原始 SQL（我在原始描述中写了“原始 plpgsql”）。
In an attempt to make my question clearer please consider that the update statement affects only one row and think about optimistic concurrency:
为了让我的问题更清楚，请考虑更新语句仅影响一行并考虑乐观并发：
The client did a
SELECTStatement at first.He builds the UPDATE and knows which database computed columns are to be included in the SELECT clause. Among other things, the predicate includes a timestamp that is computed each time the rows is updated.
So, if we have 1 row returned then everything is OK. If no row is returned then we know that there was a previous update and the client may need to refresh the data before trying to update clause again. This is why we need to know how many rows where affected by the update statement before returning computed columns. No row should be returned if the update fails.
客户首先做了一个
SELECT声明。他构建 UPDATE 并知道哪些数据库计算列将包含在 SELECT 子句中。其中，谓词包括每次更新行时计算的时间戳。
因此，如果我们返回了 1 行，那么一切正常。如果没有返回任何行，那么我们知道有一个先前的更新，客户端可能需要在再次尝试更新子句之前刷新数据。这就是为什么我们需要在返回计算列之前知道有多少行受更新语句影响。如果更新失败，则不应返回任何行。
回答by Craig Ringer
What you want is not currently possible in the form that you describe, but I think you can do what you want with UPDATE ... RETURNING. See UPDATE ... RETURNINGin the manual.
你想要的目前不可能以你描述的形式出现，但我认为你可以用UPDATE ... RETURNING. 参见UPDATE ... RETURNING手册。
UPDATE <target_table>
SET Proprerty0 = Value0
WHERE <predicate>
RETURNING Property0;
It's hard to be sure, since the example you've provided is so abstract as to be somewhat meaningless.
很难确定，因为你提供的例子太抽象了，有点毫无意义。
You can also use a wCTE, which allows more complex cases:
您还可以使用 wCTE，它允许更复杂的情况：
WITH updated_rows AS (
UPDATE <target_table>
SET Proprerty0 = Value0
WHERE <predicate>
RETURNING row_id, Property0
)
SELECT row_id, some_computed_value_from_property
FROM updated_rows;
See common table expressions (WITHqueries)and depesz's article on wCTEs.
请参阅通用表表达式（WITH查询）和depesz 关于 wCTE 的文章。
UPDATEbased on some added detail in the question, here's a demo using UPDATE ... RETURNING:
根据问题中的一些附加细节更新，这是一个使用的演示UPDATE ... RETURNING：
CREATE TABLE upret_demo(
id serial primary key,
somecol text not null,
last_updated timestamptz
);
INSERT INTO upret_demo (somecol, last_updated) VALUES ('blah',current_timestamp);
UPDATE upret_demo
SET
somecol = 'newvalue',
last_updated = current_timestamp
WHERE last_updated = '2012-12-03 19:36:15.045159+08' -- Change to your timestamp
RETURNING
somecol || '_computed' AS a,
'totally_new_computed_column' AS b;
Output when run the 1st time:
第一次运行时的输出：
a | b
-------------------+-----------------------------
newvalue_computed | totally_new_computed_column
(1 row)
When run again, it'll have no effect and return no rows.
再次运行时，它将无效并且不返回任何行。
If you have more complex calculations to do in the result set, you can use a wCTE so you can JOIN on the results of the update and do other complex things.
如果您在结果集中有更复杂的计算要做，您可以使用 wCTE，以便您可以对更新的结果进行 JOIN 并执行其他复杂的操作。
WITH upd_row AS (
UPDATE upret_demo SET
somecol = 'newvalue',
last_updated = current_timestamp
WHERE last_updated = '2012-12-03 19:36:15.045159+08'
RETURNING id, somecol, last_updated
)
SELECT
'row_'||id||'_'||somecol||', updated '||last_updated AS calc1,
repeat('x',4) AS calc2
FROM upd_row;
In other words: Use UPDATE ... RETURNING, either directly to produce the calculated rows, or in a writeable CTE for more complex cases.
换句话说：使用UPDATE ... RETURNING，或者直接生成计算行，或者在更复杂的情况下使用可写的 CTE。
