Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How do I specify an interval in a function?
Date: 2008-07-31 00:59:11
Message-ID: 26386.1217465951@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"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

In response to

pgsql-novice by date

Next:From: Arjun DattaDate: 2008-08-01 19:31:50
Subject: Upgrading database format on PostgreSQL 8.1 on FC6
Previous:From: Rob RichardsonDate: 2008-07-31 00:24:25
Subject: How do I specify an interval in a function?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group