-- -- acs_event__recurrence_timespan_edit/3 -- create or replace function acs_event__recurrence_timespan_edit( integer, timestamp without time zone, timestamp without time zone ) returns int4 as $$ DECLARE 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 timestamp; v_one_end_date timestamp; BEGIN -- 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 event_id=p_event_id; -- RBM: Converted inneficient query to INNER JOINs (2002-10-06) -- -- 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))) FOR v_timespan in SELECT ti.* FROM time_intervals ti, timespans t, acs_events ae WHERE ti.interval_id = t.interval_id AND t.timespan_id = ae.timespan_id AND ae.event_id = p_event_id LOOP 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; END; $$ language plpgsql; -- -- acs_event__recurrence_timespan_edit/3 -- create or replace function acs_event__recurrence_timespan_edit( p_event_id integer, p_start_date timestamp with time zone, p_end_date timestamp with time zone ) returns int4 as $$ DECLARE BEGIN return acs_event__recurrence_timespan_edit ( p_event_id, p_start_date, p_end_date, 't'); END; $$ language plpgsql; -- -- acs_event__recurrence_timespan_edit/4 -- create or replace function acs_event__recurrence_timespan_edit( p_event_id integer, p_start_date timestamp with time zone, p_end_date timestamp with time zone, p_edit_past_events_p boolean ) returns int4 as $$ DECLARE v_timespan RECORD; v_one_start_date timestamptz; v_one_end_date timestamptz; BEGIN -- 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 event_id=p_event_id; 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))) and (p_edit_past_events_p = 't' or start_date >= v_one_start_date) LOOP PERFORM time_interval__edit(v_timespan.interval_id, (to_char(v_timespan.start_date,'yyyy-mm-dd') || ' ' || to_char(p_start_date,'hh24:mi:ss')) :: timestamptz, (to_char(v_timespan.end_date,'yyyy-mm-dd') || ' ' || to_char(p_end_date,'hh24:mi:ss')) :: timestamptz); END LOOP; return p_event_id; END; $$ language plpgsql;