如何在事务中获取SELECT语句的结果? [英] How to get the result of SELECT statement inside a transaction?
问题描述
我无法通过 PostgreSQL 文档、网络甚至 StackOverflow 获得有关这个简单问题的信息......我一定不明白这里的一些重要内容.
I can't get information of that simple question over the PostgreSQL documentation, over the Web or even here on StackOverflow... I must not understand something essential here.
我正在 PostgreSQL 中做一个简单的 SELECT
/UPDATE
事务:
I am making a simple SELECT
/UPDATE
transaction in PostgreSQL:
START TRANSACTION;
SELECT "column" FROM "table" WHERE "criterion" = 'value' AND "activated" = true;
UPDATE "table" SET "activated" = false WHERE "criterion" = 'value';
COMMIT
基本上,当列的 activated
状态为 true
时,我需要获取该列的值,然后将其停用.PostgreSQL 告诉我有 1 行结果已被取消
Basically, I need to get the value of a column when its activated
state is true
and then deactivate it.
PostgreSQL tells me that there was a 1 row result that has been cancelled
如果我执行以下操作(基本上是没有 UPDATE
语句的相同事务),也会发生同样的情况:
The same happens if I do the following (basically the same transaction without the UPDATE
statement):
START TRANSACTION;
SELECT "column" FROM "table" WHERE "criterion" = 'value' AND "activated" = true;
COMMIT
我对交易有什么不了解?任何 SELECT
输出都不能从交易块中取出吗?
What don't I understand about transactions? Can't any SELECT
output get out of a transaction block?
推荐答案
这将从更新的行中返回所有列"的值:
This will return all "column"'s values from the updated rows:
UPDATE "table" SET "activated" = false WHERE "criterion" = 'value' AND "activated" = true
returning "column";
与交易无关.
returning
将返回值,就像发出了 select
一样:
returning
will return the values as if a select
was issued:
insert into foo (ticket, row, archived) values (3,7,true) returning *;
ticket | row | archived
--------+-----+----------
3 | 7 | t
<小时>
update foo
set archived = true
where not archived
returning *;
ticket | row | archived
--------+-----+----------
2 | 5 | t
这篇关于如何在事务中获取SELECT语句的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!