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

"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