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
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.