By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,372 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,372 IT Pros & Developers. It's quick & easy.

INTERVAL in a function

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/

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

Nov 23 '05 #2

P: n/a
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!
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.