INTERVAL in a function

From: Ron St-Pierre <rstpierre(at)syscor(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: INTERVAL in a function
Date: 2004-11-09 00:15:34
Message-ID: 41900C26.1010500@syscor.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karim Nassar 2004-11-09 00:28:53 Re: how to edit a function from psql?
Previous Message J. Michael Crawford 2004-11-09 00:03:47 Re: [JDBC] Using Postgres with Latin1 (ISO8859-1)