[GENERAL] INTERVAL in a function

[Ron St-Pierre]

I have a simple function which I use to set up a users' expiry date. If 
a field in a table contains an interval then
this function returns a timestamp some time in the future (usually two 
weeks), null otherwise. I can't pass the
interval from the table into a variable properly within the function. 
Any ideas?

CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS timestamp AS '
 DECLARE
   grpID ALIAS FOR $1;
   intval INTERVAL;
   exptime TIMESTAMP;
 BEGIN
   SELECT INTO intval unitTimeLength::INTERVAL FROM customer.groups WHERE 
groupsID = grpID;
   IF intval IS NULL THEN
     RETURN NULL;
   ELSE
     SELECT INTO exptime current_timestamp + INTERVAL ''intval'';
     RETURN exptime;
   END IF;
 END;
' LANGUAGE 'plpgsql';
SELECT getUnitTimeLength(55);
ERROR:  invalid input syntax for type interval: "intval"
CONTEXT:  PL/pgSQL function "getunittimelength" line 11 at select into variables
However if I change the else clause to this:
   ELSE
     SELECT INTO exptime current_timestamp;
     RETURN exptime;
   END IF;
it works:
----------------------------
2004-11-08 16:14:40.273597
(1 row)
Thanks
Ron



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend