在Postgres中的SELECT上实现UPDATE [英] implementing an UPDATE on SELECT in Postgres

问题描述

我知道Postgres中没有TRIGGER ON SELECT。给定这样的表

I realize there are is no TRIGGER ON SELECT in Postgres. Given a table like so

CREATE TABLE t (
    a INTEGER PRIMARY KEY, 
    b TEXT, 
    entered_by INTEGER, 
    qry_count INTEGER
);

我想为每个 entered_by的每个SELECT增加 qry_count，本质上是跟踪如何很多时候查询每个输入者的记录。例如，

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,

SELECT * a, b FROM t WHERE <condition>;

可能返回由不同输入者输入的 n行。对于每个输入者，我要qry_count ++。伪代码

might return "n" rows entered by different enterers. For each enterer, I want to qry_count++. Pseudocode ahead

FOR EVERY entered_by IN SELECT
    UPDATE t
    SET qry_count = qry_count + 1
    WHERE entered_by = <entered_by>

我可以在应用程序中最轻松地执行此操作，但是我想知道是否可以在数据库本身中执行此操作可能是最好的。我找到了示例关于我想去的地方，但它适用于PL / SQL。用Pg完成此操作的最佳方法是什么？

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 example of where I think I want to go, but it is for PL/SQL. What is the best way to accomplish this with Pg?

更新：在Perl中，我会这样做

Update: In Perl I would do it like so

$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：：大多数示例（包括以下建议）都依赖于创建PL / PgSQL函数。这种方法的问题是我的查询是在函数中硬编码的。即使可以接受输入参数，它仍然是预先声明的查询。这意味着我必须为每个查询创建一个单独的函数。实际上，在我的应用程序中，我根据用户（通过Web）的请求动态构建查询。请求的列可以更改，提供的参数可以更改。我想我正在寻找类似于上述Perl伪代码的SQL，但没有预先声明的函数（下面的SQL伪代码）

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)

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.

推荐答案

注意qry_count列的默认值：

Notice the default value for the qry_count column:

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 = $1
        ) s
    where t.a = s.a
    ;
    select *
    from t
    where b = $1
    ;
$$ language sql;

现在使用上述函数查询表：

Now query the table using the above function:

select * from select_and_update('a');

根据评论进行更新：

您可以动态地构建它，而不是使用它来构建函数，只需将sql代码包装在事务中即可。

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.

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;

这篇关于在Postgres中的SELECT上实现UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！