Skip site navigation (1) Skip section navigation (2)

Re: pl/pgsql and returns timestamp type

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Terry Yapt <yapt(at)technovell(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: pl/pgsql and returns timestamp type
Date: 2002-09-10 15:46:10
Message-ID: web-1642970@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-09-10 15:51:29
Subject: Re: login problem
Previous:From: Duncan Adams (DNS)Date: 2002-09-10 15:39:37
Subject: Re: Problems with substring

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group