postgresql 在 Postgres 中对 SELECT 执行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9919056/
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
implementing an UPDATE on SELECT in Postgres
提问by punkish
I realize there are is no TRIGGER ON SELECT in Postgres. Given a table like so
我意识到 Postgres 中没有 TRIGGER ON SELECT。给定一张像这样的桌子
CREATE TABLE t (
a INTEGER PRIMARY KEY,
b TEXT,
entered_by INTEGER,
qry_count INTEGER
);
I want to increment "qry_count" for every SELECT for every "entered_by", essentially keeping track of how many times any record is queried for every "enterer". For example,
我想为每个“entered_by”的每个 SELECT 增加“qry_count”，基本上跟踪每个“enterer”查询任何记录的次数。例如，
SELECT * a, b FROM t WHERE <condition>;
might return "n" rows entered by different enterers. For each enterer, I want to qry_count++. Pseudocode ahead
可能会返回不同输入者输入的“n”行。对于每个进入者，我想 qry_count++。前面的伪代码
FOR EVERY entered_by IN SELECT
UPDATE t
SET qry_count = qry_count + 1
WHERE entered_by = <entered_by>
I could do this most easily in my application, but I am wondering if doing this in the database itself might be best. I found an exampleof where I think I want to go, but it is for PL/SQL. What is the best way to accomplish this with Pg?
我可以在我的应用程序中最容易地做到这一点，但我想知道在数据库本身中这样做是否最好。我找到了一个我想去的地方的例子，但它是针对 PL/SQL 的。使用 Pg 完成此任务的最佳方法是什么？
Update:In Perl I would do it like so
更新：在 Perl 中，我会这样做
$sth_sel = $dbh->prepare( .. complicated SELECT includes "entered_by" ..);
$sth_upd = $dbh->prepare("UPDATE t SET qry_count = qry_count + 1 WHERE entered_by = ?");
$sth_sel->execute( .. bind params ..);
while (my $r = $sth_sel->fetchrow_arrayref) {
my $entered_by = $r->[ 7 ]; # or whatever
$sth_upd->execute($entered_by);
.. do other things with $sth_sel, perhaps build a JSON obj to return ..
}
That does seem the easiest, but it would be nice to build this functionality right as a core part of the data schema.
这似乎是最简单的，但最好将此功能构建为数据模式的核心部分。
UPDATE2:Most examples (including suggestions below) rely on creating a PL/PgSQL function. The problem with this approach is that my query is hard-coded in the function. Even if it can take input params, it is still a pre-declared query. This means I have to create a separate function for each query. In reality, in my application I construct the queries dynamically based on what is requested by the user (via the web). The requested columns can change, the supplied params can change. I think I am looking for a SQL analogous to the Perl pseudocode above without a predeclared function (SQL pseudocode below)
UPDATE2：大多数示例（包括下面的建议）依赖于创建 PL/PgSQL 函数。这种方法的问题在于我的查询是在函数中硬编码的。即使它可以接受输入参数，它仍然是一个预先声明的查询。这意味着我必须为每个查询创建一个单独的函数。实际上，在我的应用程序中，我根据用户的请求（通过网络）动态构建查询。请求的列可以更改，提供的参数可以更改。我想我正在寻找一个类似于上面的 Perl 伪代码的 SQL，没有预先声明的函数（下面的 SQL 伪代码）
BEGIN
FOR row IN
SELECT to my hearts content
FROM whatever tables I want JOINed horrendously
WHERE any arbitrary param
LOOP
eb := row[entered_by]
UPDATE t SET qry_count = qry_count + 1 WHERE entered_by = eb
RETURN NEXT row;
END LOOP;
RETURN;
END
Hope this makes my objective clearer.
希望这能让我的目标更清晰。
采纳答案by Clodoaldo Neto
Notice the default value for the qry_count column:
注意 qry_count 列的默认值：
CREATE TABLE t (
a INTEGER PRIMARY KEY,
b TEXT,
entered_by INTEGER,
qry_count INTEGER default 0
);
create function select_and_update(parameter text)
returns setof t as $$
update t
set qry_count = qry_count + 1
from (
select a
from t
where b =
) s
where t.a = s.a
;
select *
from t
where b =
;
$$ language sql;
Now query the table using the above function:
现在使用上述函数查询表：
select * from select_and_update('a');
Update according to comment:
根据评论更新：
You can build it dynamically and in instead of a function just wrap the sql code, whatever it is, in a transaction. No need for cursors.
您可以动态构建它，而不是一个函数，只需将 sql 代码包装在事务中即可。不需要游标。
begin;
update t
set qry_count = qry_count + 1
from (
select a
from t
where b = 'a'
) s
where t.a = s.a
;
select *
from t
where b = 'a'
;
commit;
回答by Frank Heikens
Instead of an SELECT query, you could use an UPDATE using RETURNING:
您可以使用RETURNING使用 UPDATE 代替 SELECT 查询：
UPDATE t
SET
qry_count = qry_count + 1
WHERE
entered_by = <entered_by>
RETURNING a, b;
If you want to update the counter and join the result to another table, you can use a Common Table Expression. CTE's using UPDATE's are available as of version 9.1.
如果要更新计数器并将结果加入另一个表，可以使用公共表表达式。使用 UPDATE 的 CTE 从 9.1 版开始可用。
WITH cte AS (
UPDATE t
SET
qry_count = qry_count + 1
WHERE
entered_by = <entered_by>
RETURNING a, b
)
SELECT
*
FROM cte
JOIN other_table ON <..> = <..>;
回答by nad2000
PostgreSQL UPDATEfeatures FROMclause that provides data driven updates. Example:
PostgreSQL UPDATE具有提供数据驱动更新的FROM子句。例子：
UPDATE table1 t1
SET blah = t2.c1
FROM table2 t2
WHERE t1.id = t2.t1id
from_list ::= A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).
from_list ::= 表表达式列表，允许来自其他表的列出现在 WHERE 条件和更新表达式中。这类似于可以在 SELECT 语句的 FROM 子句中指定的表列表。请注意，目标表不得出现在 from_list 中，除非您打算进行自联接（在这种情况下，它必须以别名出现在 from_list 中）。
However for your particular case simple update with WHERE ... IN ...will do the trick:
但是，对于您的特定情况，简单的更新WHERE ... IN ...就可以解决问题：
UPDATE t
SET qry_count = qry_count + 1
WHERE entered_by IN ( SELECT entered_by FROM ... WHERE [your condition] )
