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