Re: pl/pgsql and returns timestamp type

On Tue, Sep 10, 2002 at 05:32:04PM +0200, Terry Yapt wrote:
^^^^^^^^^^^^^^^^^^^^
Somebody's clock is over two years behind.

> I cannot to get this to run...  I think I am mistaking some basic
> concept or I have a big brain-lock .  Somebody know what is the
> problem to execute this function ?

What are you trying to do and what actually happens?  Saying simply
"it doesn't work" means we have to guess.  When I run your code in
PostgreSQL 7.4.6 I get the following:

SELECT f_test(1,9);
ERROR:  invalid input syntax for type timestamp: "00:00:00"
CONTEXT:  PL/pgSQL function "f_test" line 14 at assignment

I've found several problems:

1. Here's line 14:

    thasta := now() - tdesde;

You're trying to measure how long an operation is taking but you're
assigning an INTERVAL (the result of the subtraction) to a TIMESTAMP
variable.  I'd suggest declaring the function to return INTERVAL
and do something like this:

    tdesde := timeofday();
    ...
    thasta := timeofday();
    RETURN thasta - tdesde;

I changed now() to timeofday() because now() doesn't advance inside
a transaction.

2. I'd recommend using TIMESTAMPTZ instead of TIMESTAMP to avoid
bogus results if the code happens to run across the boundary between
Summer Time (Daylight Saving Time) and Standard Time.

3. The function has a COMMIT statement that isn't executed (because
you RETURN first) but that would cause an error if it did.  Functions
are executed within the outer query's transaction, so you can't do a
COMMIT or ROLLBACK within the function.

Hope this helps.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/