implementing an UPDATE on SELECT in Postgres

Question

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.

Clodoaldo Neto · Accepted Answer · 2012-03-29 15:45:58Z

2

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;

edited Mar 29, 2012 at 15:45

answered Mar 29, 2012 at 15:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

punkish Over a year ago

Many thanks for your answer, however, please see UPDATE2 in my original question above. I think I am looking for a different solution.

punkish Over a year ago

brilliant... this actually does work, but almost. In other words, it updates the result set's qry_count correctly, however, at least in a terminal (I am using PgAdmin to test), I get no results back. Instead, I get the message Query result with <n> rows discarded. Query returned successfully with no result in 15 ms. How do I actually return the results of the SELECT?

Clodoaldo Neto Over a year ago

@punkish I'm using psql and it returns the result of the select. Your language (perl?) postgresql driver will also return the results.

punkish Over a year ago

interesting... yes, I can see the results in psql as well, and I am sure I will be able to use the results in my Perl query. However, no rows are displayed in PgAdmin. Any idea why? In any case, many thanks for your help with this.

Frank Heikens · Accepted Answer · 2012-03-29 06:54:57Z

2

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

edited Mar 29, 2012 at 6:54

answered Mar 29, 2012 at 6:12

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

punkish Over a year ago

well, this is a fascinating bit of knowledge I would not have otherwise unearthed. Thanks. However, it seems that CTE/RETURNING (aka SELECT) are being driven by the UPDATE. I really want the SELECT driving the UPDATE. In other words, I am not necessarily SELECTing by "entered_by". The SELECT can be from multiple tables with arbitrarily complicated WHERE (which could include "entered_by") but would always have "entered_by" in the SELECTed cols. Once I have that, I want to UPDATE "qry_count" for each "entered_by"

nad2000 · Accepted Answer · 2012-03-30 04:13:01Z

1

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

edited Mar 30, 2012 at 4:13

answered Mar 30, 2012 at 4:03

nad2000

5,0141 gold badge33 silver badges25 bronze badges

2 Comments

punkish Over a year ago

thanks, but, I believe the problem is not so much performing the UPDATE, but doing so on SELECT so that when results are returned for any arbitrary query (which could involve JOINs with multiple tables and complex WHERE clause), the qry_count for each row in the result set is incremented. I want to do so with the greatest efficiency.

Petar Zivkovic Over a year ago

Thanks, my problem was not the same as the OP's but your answer helped me out so +1

Collectives™ on Stack Overflow

implementing an UPDATE on SELECT in Postgres

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related