title: How to call a Postgres SQL function in a view efficiently?
body: <p>I am building a key/value store in Postgres. I have a <code>field</code> table that describes fields and a <code>field_value_log</code> table that stores all values ever written to a field for a user. I also have a view <code>field_value</code> that gets the latest value for each (user, field) pair. I'd like most clients to read from <code>field_value</code>.</p>
  <p>I am extending the key/value system to be able to call an arbitrary SQL function as a getter for a field. The <code>field</code> table has a text column <code>sql_function_getter</code> and if that column has a value, I'd like the <code>field_value</code> view to call an evaluator function to get the value. That evaluator function constructs a SQL query that calls the function specified by <code>sql_function_getter</code>.</p>
  <p>I've written such a view that works in two parts: first it gets the latest value for each (user, field) pair, then it unions the log values with calls to the evaluator function in a <code>LATERAL</code> <code>CROSS JOIN</code>. This works and I wrote several unit tests verifying its correctness, but when I ran it against real data (~5,000 users, 2 fields with SQL getters), it OOM'd my Postgres server hard. I need to analyze the query plan but I'm pretty sure Postgres is eagerly calling the evaluator function against every (user, field) pair, which is pretty much what I asked it to do. That clearly won't work, but I'm curious if there's a way to do this efficiently. Is there a way to lazily call a function?</p>
  <p>Here's my view:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE VIEW &quot;public&quot;.&quot;field_value&quot; AS (
    SELECT
      t1.user_id,
      t1.field_id,
      t1.text_value,
      t1.created_at
    FROM
      (
        field_value_log t1
        JOIN (
          SELECT
            field_value_log.user_id,
            field_value_log.field_id,
            max(field_value_log.created_at) AS max_created_at
          FROM
            field_value_log
          GROUP BY
            field_value_log.user_id,
            field_value_log.field_id
        ) t2 ON (
          (
            (t1._id = t2.user_id)
            AND (t1.field_id = t2.field_id)
            AND (t1.created_at = t2.max_created_at)
          )
        )
      )
  )
  UNION ALL
  (
    SELECT
      R.user_id,
      R.field_id,
      R.text_value,
      R.created_at,
    FROM field F
    CROSS JOIN user U
    CROSS JOIN LATERAL evaluate_field_value_by_function(U.id, F.id) R
    WHERE F.sql_function_getter IS NOT NULL
  )
  ORDER BY created_at DESC;
  </code></pre>