Skip site navigation (1) Skip section navigation (2)

Re: pl/pgsql and returns timestamp type

From: Terry Yapt <yapt(at)technovell(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: pl/pgsql and returns timestamp type
Date: 2002-09-10 17:58:22
Message-ID: 3D7E32BE.17F3D3AE@technovell.com (view raw or flat)
Thread:
Lists: pgsql-novice

Josh Berkus wrote:
> 
> Terry,
> 
> Can you explain what you are trying to accomplish with this function?
>  I'll tell you below why it won't work, but to help you find a
> workaround, I'll need to know what you're appempting.

I am only testing different datatypes.  In the example I would like to know how many time was spent by the function execution.
 
> -- First off, don't include limits in your function type declarations.
>  That is,
> -- use f_test(numeric, numeric) not f_test (numeric(7,0),
> numeric(7,0)).
> -- Type limits are ignored by the function parser, and will sometimes
> cause
> -- errors.

Ok.. thanks...

> 
>  RETURNS
> > timestamp AS '
> >   DECLARE
> >     p_datod ALIAS FOR $1;
> >     p_datoh ALIAS FOR $2;
> >     --
> >     tdesde timestamp;
> >     thasta timestamp;
> >   BEGIN
> >     tdesde := now();
> >     --
> >     FOR X IN p_datod..p_datoh LOOP
> >       INSERT INTO test VALUES (x);
> >     END LOOP;
> >     --
> >     thasta := now() - tdesde;
> 
> --Problem #1:  A TIMESTAMP minus another TIMESTAMP returns an
> --INTERVAL, not a TIMESTAMP.   See my paper on timestamps and
> --intervals on http://techdocs.postgresql.org/

Sorry.  I had no luck looking for your document... :-(

> 
> --Problem #2: since functions are inherently a single
> --transaction, the values of global database variables -- such as NOW()
> 
> --are frozen at the beginning of the function.  Thus, the function as
> you
> --have written it will always return an interval of 0:00

Hummm... ok.   I have read this in somewhere.  But I have too much information about pgsql to digest (for the moment).
But then... How could I obtain "variable global values" (like the timestamp for example) in different function locations whit its "at time" values ???

> 
> >     RETURN thasta;
> >   COMMIT;
> 
> --Problem #3: A commmit statement is entirely superflous within a
> --function, which is transactional regardless, and will cause an error.

But...then.. 
Do you mean all or nothing of the function statements will be executed regardless how many tables and/or sentences will be involved ????

> 
> >   END;
> > ' LANGUAGE 'plpgsql';
> > --====================================
> > select f_test(1,9);
> 

Is, at least, the above statement right ??????  ;-) <g>

It is a joke.. thanks for your help Josh...

In response to

Responses

pgsql-novice by date

Next:From: Terry YaptDate: 2002-09-10 18:03:01
Subject: Re: pl/pgsql and returns timestamp type
Previous:From: Ernesto JardimDate: 2002-09-10 17:07:31
Subject: Re: login problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group