Forum .LRN Q&A: Fix for bug when editing recurring events on calendars (postgres)

When editing a recurring event on calendar and you choose to apply the change to all associated entries, you'll get a "query did not return any rows" error.

The error is due to misnamed variables and an extra set of parenthesis in the function acs_event__recurrence_timespan_edit in the file /packages/acs-events/sql/postgresql/acs-events-create.sql

Below is the fixed function declaration/body

create function acs_event__recurrence_timespan_edit (
) returns integer as '
        p_event_id                      alias for $1;
        p_start_date                    alias for $2;
        p_end_date                      alias for $3;
        v_timespan                   RECORD;
        v_one_start_date             timestamptz;
        v_one_end_date               timestamptz;
        -- get the initial offsets
        select start_date, end_date into v_one_start_date, v_one_end_date
        from time_intervals, timespans, acs_events
        where time_intervals.interval_id = timespans.interval_id and
        timespans.timespan_id = acs_events.timespan_id and

        FOR v_timespan in 
        	select * from time_intervals where interval_id in (select
interval_id from timespans where timespan_id in (select timespan_id
from acs_events where recurrence_id = (select recurrence_id from
acs_events where event_id = p_event_id)))
                PERFORM time_interval__edit(v_timespan.interval_id,
v_timespan.start_date + (p_start_date - v_one_start_date),
v_timespan.end_date + (p_end_date - v_one_end_date));
        END LOOP;

        return p_event_id;
' language 'plpgsql';

And since it's a plpgsql function, after applying fix, you need to drop and recreate the function.

applied. thanks.

what's the "right way" to upgrade this? JonG?

The most seamless would probably be to create an upgrade script inside the sql directory of acs-events that will drop and recreate the function. This is the solution which will cause less hassle to those who are not too technically inclined and I think it's the correct way as evident in previous threads in the OpenACS bboards.

One question: Are upgrade scripts automatically executed by the APM? Or do they need to be run manually?