title: PostgreSQL: running a query for each row and saving the result in it
body: <p>I store weekly game score in a table called <strong>pref_money</strong>:</p>
  
  <pre><code># select * from pref_money limit 5;
         id       | money |   yw
  ----------------+-------+---------
   OK32378280203  |   -27 | 2011-44
   OK274037315447 |   -56 | 2011-44
   OK19644992852  |     8 | 2011-44
   OK21807961329  |   114 | 2011-44
   FB1845091917   |   774 | 2011-44
  (5 rows)
  </code></pre>
  
  <p>And for the winners of each week I display medal(s):</p>
  
  <p><img src="https://i.stack.imgur.com/4jiAw.png" alt="screenshot"></p>
  
  <p>I find the number of medals for a user by running:</p>
  
  <pre><code># select count(id) from (
       select id,
              row_number() over(partition by yw order by money desc) as ranking
       from pref_money
  ) x
  where x.ranking = 1 and id='OK260246921082';
   count
  -------
       3
  (1 row)
  </code></pre>
  
  <p>And that query is quite costly:</p>
  
  <pre><code># explain analyze select count(id) from (
      select id,
             row_number() over(partition by yw order by money desc) as ranking
      from pref_money
  ) x
  where x.ranking = 1 and id='OK260246921082';
                                                                  QUERY PLAN
  -------------------------------------------------------------------------------------------------------------------------------------------
   Aggregate  (cost=18946.46..18946.47 rows=1 width=82) (actual time=2423.145..2423.145 rows=1 loops=1)
     -&gt;  Subquery Scan x  (cost=14829.44..18946.45 rows=3 width=82) (actual time=2400.004..2423.138 rows=3 loops=1)
           Filter: ((x.ranking = 1) AND ((x.id)::text = 'OK260246921082'::text))
           -&gt;  WindowAgg  (cost=14829.44..17182.02 rows=117629 width=26) (actual time=2289.079..2403.685 rows=116825 loops=1)
                 -&gt;  Sort  (cost=14829.44..15123.51 rows=117629 width=26) (actual time=2289.069..2319.575 rows=116825 loops=1)
                       Sort Key: pref_money.yw, pref_money.money
                       Sort Method:  external sort  Disk: 4320kB
                       -&gt;  Seq Scan on pref_money  (cost=0.00..2105.29 rows=117629 width=26) (actual time=0.006..22.566 rows=116825 loops=1)
   Total runtime: 2425.001 ms
  (9 rows)
  </code></pre>
  
  <p>That is why (and because my web site is struggling during peak times, with 50 queries/s displayed in pgbouncer log) I'd like to cache that value and have added a column <strong>medals</strong> to another table - the <strong>pref_users</strong>:</p>
  
  <pre><code>pref=&gt; \d pref_users;
                  Table "public.pref_users"
     Column   |            Type             |   Modifiers
  ------------+-----------------------------+---------------
   id         | character varying(32)       | not null
   first_name | character varying(32)       |
   last_name  | character varying(32)       |
   female     | boolean                     |
   avatar     | character varying(128)      |
   city       | character varying(32)       |
   lat        | real                        |
   lng        | real                        |
   login      | timestamp without time zone | default now()
   last_ip    | inet                        |
   medals     | smallint                    | default 0
   logout     | timestamp without time zone |
  Indexes:
      "pref_users_pkey" PRIMARY KEY, btree (id)
  Check constraints:
      "pref_users_lat_check" CHECK ((-90)::double precision &lt;= lat AND lat &lt;= 90::double precision)
      "pref_users_lng_check" CHECK ((-90)::double precision &lt;= lng AND lng &lt;= 90::double precision)
      "pref_users_medals_check" CHECK (medals &gt;= 0)
  </code></pre>
  
  <p>I would like to create a cronjob to be run every 15 minutes to update that column for all users in the <strong>pref_users</strong> table:</p>
  
  <pre><code>*/15       *       *       *       *       psql -a -f $HOME/bin/medals.sql
  </code></pre>
  
  <p>As you see, I've got almost everything in-place. My problem is that I haven't come up with the SQL statement yet for updating the <strong>medals</strong> column.</p>
  
  <p>Any help please?</p>
  
  <p>I'm using PostgreSQL 8.4.8 with CentOS Linux 5.6 / 64 bit.</p>
  
  <p>Thank you!
  Alex</p>