Pivotal Knowledge Base

Follow

Date function(like now(),current_timestamp etc) doesn't change the values dynamically when run within a function

Problem

Passing date/time functions like now(), current_date, current_timestamp etc doesn't change dynamically within the function and due to this the query give wrong results.

Lets take the below function for example, the date function supplies the values to the variable

create or replace function check_timestamp_1() returns "varchar"
as
$BODY$
declare
now1 timestamp;
current_time1 timestamp;
output varchar;
begin
 	now1 := now();
 	current_time1 := current_timestamp;
 	output := 'Now: '|| now1 || ' current timestamp: ' || current_time1 ;
        return output ;        
end;
$BODY$
language 'plpgsql';

So each call of the function within the session results in having the same values when it should be dynamic

flightdata=# select check_timestamp_1();

								check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:05:40.628531 current timestamp: 2015-02-20 04:05:40.628531
(1 row)

flightdata=# select now();
              now
-------------------------------
 2015-02-20 04:05:53.556095-08

flightdata=# select check_timestamp_1() ;
                               check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:05:40.628531 current timestamp: 2015-02-20 04:05:40.628531
(1 row)

flightdata=# select check_timestamp_1() ;
                               check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:05:40.628531 current timestamp: 2015-02-20 04:05:40.628531
(1 row)

Cause

current_timestamp is parsed into the function call now() and now() is a stable function, check the link for more information on the function volatility categories.

Also this was a feature in postgres and left this way to avoid multiple transaction having different values when calling the date function, check the link for more information.

Solution

Here are some of the solution to overcome the problem.

-- Place the function in a select to execute each time in order to retrieve the value.

create or replace function check_timestamp_2() returns "varchar"
as
$BODY$
declare
now1 timestamp;
current_time1 timestamp;
output varchar;
begin
 	now1 := (select now());
 	current_time1 := (select current_timestamp);
 	output := 'Now: '|| now1 || ' current timestamp: ' || current_time1 ;
        return output ;        
end;
$BODY$
language 'plpgsql';

-- Or use the timeofday() function, which is a violate function. When used with different cast it can yield the desired results.

create or replace function check_timestamp_3() returns "varchar"
as
$BODY$
declare
now1 timestamp;
current_time1 timestamp with time zone;
current_date1 date;
output varchar;
begin
 	now1 := timeofday()::timestamp;
 	current_time1 := timeofday()::timestamp with time zone;
 	current_date1 := timeofday()::date;
 	output := 'Now: '|| now1 ||' current timestamp: ' || current_time1 ||' current date: ' || current_date1 ;
        return output ;       
end;
$BODY$
language 'plpgsql';

-- Or disconnect and reconnect after each execute, which might not be feasiable always.

flightdata=# \q
[gpadmin@mdw pg_log]$ psql
psql (8.2.15)
Type "help" for help.

flightdata=# select check_timestamp_1() ;
                               check_timestamp_1
-------------------------------------------------------------------------------
 Now: 2015-02-20 04:07:08.775462 current timestamp: 2015-02-20 04:07:08.775462
(1 row)

Comments

Powered by Zendesk