Re: How do I specify intervals in functions?

I found my problem. I trimmed down the function I was having trouble
with to the following:

CREATE OR REPLACE FUNCTION recalculate_heating_time(int4)
RETURNS int4 AS
$BODY$
declare
ChargeNum ALIAS for $1;
ChargeRec charge%rowtype;
HeatingTime int4;
IntervalMinutes float4;
NewRevisionTime timestamp;
PredictedEndTime timestamp;
Interval interval;
PredictedSpan interval;
Message varchar;
EightHours interval;

begin
Message = '07:00:00'::varchar;
EightHours = '08:00:00'::interval;
return 1;
end;

This gave me the following error message:
ERROR: syntax error at or near "$1" at character 22
QUERY: SELECT '08:00:00':: $1
CONTEXT: SQL statement in PL/PgSQL function "recalculate_heating_time"
near line 15

I stripped out all the declarations before Message, and the function
loaded successfully.

I'm primarily a C++/C# developer, and in those languages, there's no
problem differentiating between "Interval" and "interval". In the
Postgres SQL dialect (and probably in all other SQL variants), the two
words are treated identically. The line where I declared a variable
named "Interval" of type "interval" screwed everything up.

RobR
Using PostGreSQL 8.1 under Windows XP Pro

________________________________

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rob Richardson
Sent: Thursday, July 31, 2008 8:37 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] How do I specify intervals in functions?

Greetings!

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;

So how do I specify an interval in a function?

Specifically, I'm trying to do something like the following:

if NewRevisionTime < PredictedEndTime - '08:00:00'::interval then

Since both of the shown forms give syntax errors, how do I subtract
eight hours from a time???

Thank you very much.

RobR, who posted this on the novice list but got no answers.