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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Richardson Sent: Thursday, July 31, 2008 8:37 AM To: pgsql-general@postgresql.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.