Re: pl/pgsql and returns timestamp type

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
locationswhit 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
beinvolved ???? 

>
> >   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...