Message-ID: <5387A2DE.20207@pinpointresearch.com>
Date: Thu, 29 May 2014 14:13:02 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: ng <pipelines@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: PGsql function timestamp issue
References: 
 <CAAQKod115J-w7hiJ+rG8kn2FVqW51+ZVJsVpELNcU1Q4cw69dQ@mail.gmail.com>
In-Reply-To: 
 <CAAQKod115J-w7hiJ+rG8kn2FVqW51+ZVJsVpELNcU1Q4cw69dQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql