plpgsql - PostgreSQL Function / Stored Procedure CURRENT_TIMESTAMP does not change

Question

I want to track the execution time within a function. As an example, I have the following status tracking table:

    CREATE TABLE status_table
    (
    run_id numeric NOT NULL,
      start_ts timestamp(6) without time zone NOT NULL,
      end_ts timestamp(6) without time zone NOT NULL
    )
WITH (
  OIDS=FALSE
)
DISTRIBUTED RANDOMLY;

I have the following example function that uses PG_SLEEP(5) as the simulation of what my function will actually do:

CREATE OR REPLACE FUNCTION wrapper_func(p_run_id numeric)
  RETURNS numeric AS
$BODY$
Declare  
tmp_start_ts timestamp(6) without time zone;
tmp_end_ts timestamp(6) without time zone;

BEGIN
tmp_start_ts := CURRENT_TIMESTAMP;
PERFORM PG_SLEEP(5);
tmp_end_ts := CURRENT_TIMESTAMP;
INSERT INTO status_table (run_id,start_ts,end_ts) VALUES (p_run_id,tmp_start_ts,tmp_end_ts);
RETURN 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

I then called this function three times, waiting about 20 seconds between each invocation:

SELECT * FROM wrapper_func(1);
SELECT * FROM wrapper_func(2);
SELECT * FROM wrapper_func(3);

However, when I look at the results in my status_table, the timestamps are all the same:

SELECT * FROM status_table ORDER BY run_id;

run_id|start_ts|end_ts
1|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294
2|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294
3|2015-01-18 17:54:43.641294|2015-01-18 17:54:43.641294

Could someone point out what I'm doing wrong or another approach to accomplish this?

Thanks

Answer 1

use clock_timestamp() instead of CURRENT_TIMESTAMP in your function

CURRENT_TIMESTAMP will return values based on the start time of the current transaction.

see document