Re: How do I specify an interval in a function?

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com> writes:
> In the help file under date and time functions, I see that intervals can be specified as "interval '3 hours' ".  In a PgAdmin SQL window, I can enter "select interval '3 hours' ", and it will return me "03:00:00", as expected.  I can also enter "select '3 hours'::interval", and get the same result.  Yet neither syntax works inside a function.  
 
> declare
>      ThreeHours interval;
> begin
>      ThreeHours = interval '3 hours';  -- throws a syntax error
>      ThreeHours = '3 hours'::interval; -- also throws a syntax error
> end;

Either of those should work.  I think your problem is that you're not
quoting the whole function body correctly.  Remember that the function
body is itself a string constant.  So if you were to try to write this
as a single-quoted string, you'd need to double those embedded quotes:

CREATE FUNCTION ... AS '
declare
     ThreeHours interval;
begin
     ThreeHours = interval ''3 hours'';  -- throws a syntax error
     ThreeHours = ''3 hours''::interval; -- also throws a syntax error
end;
' LANGUAGE plpgsql;

In any reasonably modern version of PG, there's a string constant syntax
called "dollar quoting", which was invented specifically to make this
less painful:

CREATE FUNCTION ... AS $$
declare
     ThreeHours interval;
begin
     ThreeHours = interval '3 hours';  -- throws a syntax error
     ThreeHours = '3 hours'::interval; -- also throws a syntax error
end;
$$ LANGUAGE plpgsql;

If you need to use '$$' inside the function body, you could instead
use $func$ or something like that as the outer quoting boundaries.

(BTW, I would think that PgAdmin could handle these quoting details
for you, but I'm really not very familar with it.  Are you editing
the function in a window that's specifically for function editing?
If you're just typing the CREATE FUNCTION command as-is in a command
window, then you'll have to deal with the nested-quoting issues for
yourself.)

			regards, tom lane