"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.
> ThreeHours interval;
> ThreeHours = interval '3 hours'; -- throws a syntax error
> ThreeHours = '3 hours'::interval; -- also throws a syntax error
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 '
ThreeHours = interval ''3 hours''; -- throws a syntax error
ThreeHours = ''3 hours''::interval; -- also throws a syntax error
' 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
CREATE FUNCTION ... AS $$
ThreeHours = interval '3 hours'; -- throws a syntax error
ThreeHours = '3 hours'::interval; -- also throws a syntax error
$$ 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
regards, tom lane
In response to
pgsql-novice by date
|Next:||From: Arjun Datta||Date: 2008-08-01 19:31:50|
|Subject: Upgrading database format on PostgreSQL 8.1 on FC6|
|Previous:||From: Rob Richardson||Date: 2008-07-31 00:24:25|
|Subject: How do I specify an interval in a function?|