Lists: | pgsql-general |
---|
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 |
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
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Ron St-Pierre <rstpierre(at)syscor(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: INTERVAL in a function |
Date: | 2004-11-09 01:00:32 |
Message-ID: | 20041109010032.GA71358@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/
From: | Ron St-Pierre <rstpierre(at)syscor(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: INTERVAL in a function |
Date: | 2004-11-09 17:07:08 |
Message-ID: | 4190F93C.7060905@syscor.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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!