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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arjun Datta | 2008-08-01 19:31:50 | Upgrading database format on PostgreSQL 8.1 on FC6 |
Previous Message | Rob Richardson | 2008-07-31 00:24:25 | How do I specify an interval in a function? |