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.glaesemann@myyearbook.com