Обсуждение: 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.
On 2008-07-31, at 8:36 AM, Rob Richardson wrote: > 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? Works for me: CREATE FUNCTION three_hours() RETURNS interval STABLE STRICT LANGUAGE plpgsql AS $body$ declare ThreeHours interval; begin ThreeHours = interval '3 hours'; -- throws a syntax error ThreeHours = '3 hours'::interval; -- also throws a syntax error RETURN ThreeHours; end $body$; CREATE FUNCTION test=# select three_hours(); three_hours ------------- 03:00:00 (1 row) test=# select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2) (1 row) I can't tell as you haven't provided a complete example (always helpful when debugging), but are you sure you're specifying the correct language type (plpgsql in your case)? Michael Glaesemann michael.glaesemann@myyearbook.com
"Rob Richardson" <Rob.Richardson@rad-con.com> writes: > So how do I specify an interval in a function? Does this help? (vy@[local]:5432/test) [2008-07-31 15:49:54] # CREATE OR REPLACE FUNCTION time_cmp_with_1w_offset (_l_ts timestamp, _r_ts timestamp) RETURNS boolean AS $$ DECLARE _offset interval; BEGIN _offset = '1 week'; RETURN (_l_ts < _r_ts - _offset); END; $$ LANGUAGE plpgsql; (vy@[local]:5432/test) [2008-07-31 15:49:28] # SELECT time_cmp_with_1w_offset(CAST('2008-07-10' AS timestamp), ] CAST('2008-07-31' AS timestamp)); time_cmp_with_1w_offset ------------------------- t (1 row) Regards.
My thanks to all for the quick replies. Now I can't get it not to work!
I guess the computer gremlins that all of us are paid to deny they exist were playing games with me.
RobR
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;
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;
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
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] 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.
One thing I left out of my last post:
Thanks to all of you for your assitance.
RobR