I was trying to modify next_day(timestamptz,varchar) function to make
it work on 7.1 and 7.2. I've tried a few approaches but they all seem
to fail. Most recent looks thusly:
declare
next_day__somedate alias for $1;
next_day__weekday alias for $2;
v_dow integer;
v_ref_dow integer;
v_add_days integer;
v_add_interval varchar;
v_pg_version varchar;
v_rz timestamptz;
begin
-- I cant find a function that converts days of the week to
-- the corresponding integer value, so I roll my own (above)
-- We avoid extract(dow from timestamp) because of incompatible output
with to_char.
v_ref_dow := dow_to_int(next_day__weekday);
v_dow := to_number(to_char(next_day__somedate,'D'),'9');
-- If next_day__weekday is the same day of the week as
-- next_day__somedate, we add a full week.
if v_dow < v_ref_dow
then
v_add_days := v_ref_dow - v_dow;
else
v_add_days := v_ref_dow - v_dow + 7;
end if;
select version() into v_pg_version;
-- Do date month, but consider PG changes
if v_pg_version ~ ' 7.2' then
v_add_interval := v_add_days || ' days';
v_rz := next_day__somedate + interval v_add_interval;
else
v_rz := next_day__somedate + to_interval(v_add_days,'days');
end if;
return v_rz;
end;
And I get
openacs-dev=# select next_day('3-JUL-2002'::date,'Sunday');
NOTICE: Error occurred while executing PL/pgSQL function next_day
NOTICE: line 31 at assignment
ERROR: parser: parse error at or near "$2"
When I try to execute it. Any idea what's failing?
