title: Immediate update in PL/pgSQL functions
body: <p>I'm working on a PL/pgSQL function "interpolate_values" with some time-consuming computations. A table called "interpolation_jobs" contains surveillance information about every function call, such that the progress of a function call with a given job_id should be observable by</p>
  
  <pre><code>SELECT status FROM interpolation_jobs WHERE id = job_id;
  </code></pre>
  
  <p>The column "status" contains one of the values 'queued', 'running' or 'done'. At the beginning of the function, the status is changed from 'queued' to 'running', at the end it is set to 'done':</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION interpolate_values (job_id INTEGER)
  RETURNS VOID
  LANGUAGE plpgsql VOLATILE
  AS $$
  DECLARE
  BEGIN
      EXECUTE 'UPDATE interpolation_jobs
          SET status = ''running'', progress = 0.0
          WHERE id = ' || job_id || ';';
  
  --
  -- ... some extensive database computations ...
  --
  
      EXECUTE 'UPDATE interpolation_jobs
          SET status = ''done''
          WHERE id = ' || job_id || ';';
  
  END;
  $$;
  </code></pre>
  
  <p>My problem is that the status is not updated during the function call. The updates actually take place when the function call returns. So, the status directly changes from 'queued' to 'done'. The lines </p>
  
  <pre><code>    EXECUTE 'UPDATE interpolation_jobs
          SET status = ''running'', progress = 0.0
          WHERE id = ' || job_id || ';';
  </code></pre>
  
  <p>have no effect. </p>
  
  <p>Is there a possibilty to update the values immediately in PL/pgSQL, so that new values are accessible before the function call returns?</p>
  
  <p>Thanks!</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Thank you for all your answers which helped me a lot to understand the general problems of asynchronous database operations. The dblink approach works for me. It is not necessary to specify IP/Port/User if the same database is used:  </p>
  
  <pre><code>SELECT * FROM current_database() INTO _db_name;
  PERFORM dblink_connect('dbname=' || _db_name);
  PERFORM dblink_exec('UPDATE interpolation_jobs SET status = ''running'' WHERE id =' || _job_id);
  
  -- 
  -- ... some extensive database computations ...
  --
  
  PERFORM dblink_exec('UPDATE interpolation_jobs SET status = ''done'' WHERE id =' || _job_id);
  PERFORM dblink_disconnect();
  </code></pre>