Thread: INTERVAL in a function
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
On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote: > SELECT INTO exptime current_timestamp + INTERVAL ''intval''; You're using the literal value 'intval' instead of its value, thus the syntax error. You can simplify the statement to this: exptime := current_timestamp + intval; But I think the entire function can be shortened to: CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS TIMESTAMP AS ' SELECT CURRENT_TIMESTAMP::timestamp + unitTimeLength FROM customer.groups WHERE groupsID = $1 ' LANGUAGE sql; You don't need to check for NULL because the result of the addition will already be NULL if either operand is NULL. Casting CURRENT_TIMESTAMP is necessary to avoid a "return type mismatch" error. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: >On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote: > > > >>SELECT INTO exptime current_timestamp + INTERVAL ''intval''; >> >> > >You're using the literal value 'intval' instead of its value, thus >the syntax error. > Of course, I should have caught that. >You can simplify the statement to this: > >exptime := current_timestamp + intval; > >But I think the entire function can be shortened to: > >CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS TIMESTAMP AS ' >SELECT CURRENT_TIMESTAMP::timestamp + unitTimeLength >FROM customer.groups >WHERE groupsID = $1 >' LANGUAGE sql; > >You don't need to check for NULL because the result of the addition >will already be NULL if either operand is NULL. Casting CURRENT_TIMESTAMP >is necessary to avoid a "return type mismatch" error. > > > Perfect. Thanks Michael!