Re: pl/pgsql and returns timestamp type

Terry,

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

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.

> DROP FUNCTION f_test(numeric(7,0), numeric(7,0));
> CREATE OR REPLACE FUNCTION f_test(numeric(7,0), numeric(7,0))

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

 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/

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

>     RETURN thasta;
>   COMMIT;

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

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

-Josh Berkus