title: Refresh a materialized view automatically using a rule or notify
body: <p>I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly.</p>
  <p>Here is what I was thinking about so far:</p>
  <p>There is a materialized view <code>mat_view</code> which gets its data from tables <code>table1</code> and <code>table2</code> using some join statement.</p>
  <p>Whenever something in <code>table1</code> or <code>table2</code> changes, I already have a trigger which updates a little configuration table <code>config</code> consisting of</p>
  <pre><code>table_name | mat_view_name | need_update
  -----------+---------------+------------
  table1     | mat_view      | TRUE/FALSE
  table2     | mat_view      | TRUE/FALSE
  </code></pre>
  <p>So if anything in <code>table1</code> changes (there's a trigger on UPDATE and on DELETE for every statement), the field <code>need_update</code> in the first row is set to <code>TRUE</code>.
  The same goes for <code>table2</code> and the second row.</p>
  <p>Obviously, if <code>need_update</code> is TRUE, then the materialized view must be refreshed.</p>
  <p><strong>UPDATE</strong>:
  Since materialized views do not support rules (as @pozs mentioned in a comment below), I would go one step further. I'd create a dummy view <code>v_mat_view</code> with the definition &quot;<code>SELECT * FROM mat_view</code>&quot;. When the user does a SELECT on this view, I need to create a rule ON SELECT which does the following:</p>
  <ul>
  <li>check whether <code>mat_view</code> should be updated (<code>SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE</code>)</li>
  <li>reset the <code>need_update</code> flag with <code>UPDATE config SET need_update=FALSE where mat_view_name='mat_view'</code></li>
  <li><code>REFRESH MATERIALIZED VIEW mat_view</code></li>
  <li>and at last do the original SELECT statement but with <code>mat_view</code> as the target.</li>
  </ul>
  <p><strong>UPDATE2</strong>:
  I tried creating the steps above:</p>
  <p>Create a function that handles the four points mentioned above:</p>
  <pre><code>CREATE OR REPLACE FUNCTION mat_view_selector()
  RETURNS SETOF mat_view AS $body$
  BEGIN
    -- here is checking whether to refresh the mat_view
    -- then return the select:
    RETURN QUERY SELECT * FROM mat_view;
  END;
  $body$ LANGUAGE plpgsql;
  </code></pre>
  <p>Create the view <code>v_mat_view</code> which really selects from the function <code>mat_view_selector</code>:</p>
  <pre><code>CREATE TABLE v_mat_view AS SELECT * from mat_view LIMIT 1;
  DELETE FROM v_mat_view;
  
  CREATE RULE &quot;_RETURN&quot; AS
      ON SELECT TO v_mat_view
      DO INSTEAD 
          SELECT * FROM mat_view_selector();
      -- this also converts the empty table 'v_mat_view' into a view.
  </code></pre>
  <h2>The result is unsatisfying:</h2>
  <pre><code># explain analyze select field1 from v_mat_view where field2 = 44;
  QUERY PLAN
  Function Scan on mat_view_selector (cost=0.25..12.75 rows=5 width=4)
  (actual time=15.457..18.048 rows=1 loops=1)
  Filter: (field2 = 44)
  Rows Removed by Filter: 20021
  Total runtime: 31.753 ms
  </code></pre>
  <p>in comparison to selecting from the mat_view itself:</p>
  <pre><code># explain analyze select field1 from mat_view where field2 = 44;
  QUERY PLAN
  Index Scan using mat_view_field2 on mat_view (cost=0.29..8.30 rows=1 width=4)
    (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (field2 = 44)
  Total runtime: 0.036 ms
  </code></pre>
  <p>So essentially it DOES work, but performance might be an issue.</p>
  <p>Anyone have better ideas?
  If not, then I would have to implement it somehow in the application logic or worse: run a simple cronjob that runs every minute or so.</p>