title: implementing an UPDATE on SELECT in Postgres
body: <p>I realize there are is no TRIGGER ON SELECT in Postgres. Given a table like so</p>
  
  <pre><code>CREATE TABLE t (
      a INTEGER PRIMARY KEY, 
      b TEXT, 
      entered_by INTEGER, 
      qry_count INTEGER
  );
  </code></pre>
  
  <p>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,</p>
  
  <pre><code>SELECT * a, b FROM t WHERE &lt;condition&gt;;
  </code></pre>
  
  <p>might return "n" rows entered by different enterers. For each enterer, I want to qry_count++. Pseudocode ahead</p>
  
  <pre><code>FOR EVERY entered_by IN SELECT
      UPDATE t
      SET qry_count = qry_count + 1
      WHERE entered_by = &lt;entered_by&gt;
  </code></pre>
  
  <p>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 <a href="http://www.adp-gmbh.ch/ora/plsql/cursors/for_update.html" rel="nofollow">example</a> of where I think I want to go, but it is for PL/SQL. What is the best way to accomplish this with Pg?</p>
  
  <p><strong>Update:</strong> In Perl I would do it like so</p>
  
  <pre><code>$sth_sel = $dbh-&gt;prepare( .. complicated SELECT includes "entered_by" ..);
  $sth_upd = $dbh-&gt;prepare("UPDATE t SET qry_count = qry_count + 1 WHERE entered_by = ?");
  
  $sth_sel-&gt;execute( .. bind params ..);
  while (my $r = $sth_sel-&gt;fetchrow_arrayref) {
      my $entered_by = $r-&gt;[ 7 ]; # or whatever
      $sth_upd-&gt;execute($entered_by);
  
      .. do other things with $sth_sel, perhaps build a JSON obj to return ..
  }
  </code></pre>
  
  <p>That does seem the easiest, but it would be nice to build this functionality right as a core part of the data schema.</p>
  
  <p><strong>UPDATE2:</strong> 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)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Hope this makes my objective clearer.</p>