我希望在一个函数内部跟踪执行时间。例如,我有以下状态跟踪表:
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;
我有一个示例函数,它使用PG_SLEEP(5)来模拟我的函数实际执行的操作:
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;
我随后调用了这个函数三次,在每次调用之间等待约20秒钟:
SELECT * FROM wrapper_func(1);
SELECT * FROM wrapper_func(2);
SELECT * FROM wrapper_func(3);
然而,当我查看我的状态表中的结果时,时间戳都是相同的:
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
能否有人指出我做错了什么或其他实现方法?
谢谢