Re: How do I specify intervals in functions?

On 2008-07-31, at 8:36 AM, Rob Richardson wrote:

> declare
> ThreeHours interval;
> begin
> ThreeHours = interval '3 hours'; -- throws a syntax error
> ThreeHours = '3 hours'::interval; -- also throws a syntax error
> end;
>
> So how do I specify an interval in a function?

Works for me:

CREATE FUNCTION
three_hours()
RETURNS interval
STABLE
STRICT
LANGUAGE plpgsql AS $body$
declare
ThreeHours interval;
begin
ThreeHours = interval '3 hours'; -- throws a syntax error
ThreeHours = '3 hours'::interval; -- also throws a syntax error
RETURN ThreeHours;
end
$body$;
CREATE FUNCTION

test=# select three_hours();
three_hours
-------------
03:00:00
(1 row)

test=# select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
(1 row)

I can't tell as you haven't provided a complete example (always
helpful when debugging), but are you sure you're specifying the
correct language type (plpgsql in your case)?

Michael Glaesemann
michael(dot)glaesemann(at)myyearbook(dot)com