title: Why is PostgreSQL calling my STABLE/IMMUTABLE function multiple times?
body: <p>I'm trying to optimise a complex query in PostgreSQL 9.1.2, which calls some functions. These functions are marked STABLE or IMMUTABLE and are called several times with the same arguments in the query. I assumed PostgreSQL would be smart enough to only call them once for each set of inputs - after all, that's the point of STABLE and IMMUTABLE, isn't it? But it appears that the functions are being called multiple times. I wrote a simple function to test this, which confirms it:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION test_multi_calls1(one integer)
  RETURNS integer
  AS $BODY$
  BEGIN
      RAISE NOTICE 'Called with %', one;
      RETURN one;
  END;
  $BODY$ LANGUAGE plpgsql IMMUTABLE;
  
  
  WITH data AS
  (
      SELECT 10 AS num
      UNION ALL SELECT 10
      UNION ALL SELECT 20
  )
  SELECT test_multi_calls1(num)
  FROM data;
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>NOTICE:  Called with 10
  NOTICE:  Called with 10
  NOTICE:  Called with 20
  </code></pre>
  
  <p>Why is this happening and how can I get it to only execute the function once?</p>