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
