Re: PGsql function timestamp issue

On Thu, May 29, 2014 at 2:13 PM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> On 05/29/2014 01:46 PM, ng wrote:
>
>>
>> create or replace function dw.fx_nish()
>> returns text
>> language plpgsql
>> as
>> $$
>> declare
>> x timestamp with time zone;
>> y timestamp with time zone;
>> begin
>> x:= current_timestamp;
>> perform pg_sleep(5);
>> y:= current_timestamp;
>> if x=y then
>> return 'SAME';
>> else
>> return 'DIFFERENT';
>> end if;
>>
>> end;
>> $$
>>
>>
>> select dw.fx_nish()
>> This give me 'SAME'
>>
>> Any work around for this?
>>
>
> No and yes.
>
> The value of current_timestamp will remain constant throughout a
> transaction so the function is returning the expected result.
>
> You can use timeofday() but since that returns a string representing
> wall-clock time and does increment within a transaction. To get a
> timestamptz you will need to cast it: timeofday()::timestamptz
>

Or use clock_timestamp()