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