CODEWITHSUNDEEP
postgresql
punkish
punkish

Reputation: 15318

implementing an UPDATE on SELECT in Postgres

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
);

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>;

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>

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?

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: 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.

Upvotes: 2

Views: 17542

Answers (3)

nad2000
nad2000

Reputation: 4965

PostgreSQL UPDATE features FROM clause that provides data driven updates. Example:

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).

However for your particular case simple update with WHERE ... IN ... will do the trick:

UPDATE t
SET qry_count = qry_count + 1
WHERE entered_by IN ( SELECT entered_by FROM ... WHERE [your condition] )

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

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');

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.

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;

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 127556

Instead of an SELECT query, you could use an UPDATE using RETURNING:

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.

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 <..> = <..>;

Upvotes: 2

Related Questions