Thread: The trouble with dynamic plpgsql functions

The trouble with dynamic plpgsql functions

From
Динар
Date:
Hello all!!!
Is it bug or it is should be so???

I made a dynamic function on plpgsql which calculates the time period
in seconds for a given item. The calculation begins from now untill
the time that is given in field. For the first time everything
calculates ok, but the second query obtains the same result.

For example I have to calculate time period in seconds from now to
20:00.
now 10:00. I run query and obtain 36000 seconds.

after 1 minute I run query and also obtain the same result.

I checked it twice the function is not cachable.

What is wrong here???



Re: The trouble with dynamic plpgsql functions

From
Oliver Elphick
Date:
On Fri, 2002-05-31 at 10:54, Динар wrote:
> Hello all!!!
> Is it bug or it is should be so???
>
> I made a dynamic function on plpgsql which calculates the time period
> in seconds for a given item. The calculation begins from now untill
> the time that is given in field. For the first time everything
> calculates ok, but the second query obtains the same result.
>
> For example I have to calculate time period in seconds from now to
> 20:00.
> now 10:00. I run query and obtain 36000 seconds.
>
> after 1 minute I run query and also obtain the same result.
>
> I checked it twice the function is not cachable.
>
> What is wrong here???
>


You must be using functions such as now() or CURRENT_TIMESTAMP which
always return the same value within one transaction.

If you want real time within a transaction you must use timeofday().

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "How precious also are thy thoughts unto me, O God! how
      great is the sum of them! If I should count them, they
      are more in number than the sand; when I awake, I am
      still with thee."    Psalms 139: 17,18

Attachment