Re: PGsql function timestamp issue

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

Cheers,
Steve