-- -- cr_scheduled_release_exec/0 -- create or replace function cr_scheduled_release_exec( ) returns int4 as $$ declare exec__last_exec timestamptz; exec__this_exec timestamp default now(); exec__items_released integer default 0; exec__items_expired integer default 0; exec__err_num integer; -- sqlcode exec__err_msg varchar; -- substr(sqlerrm, 1, 500); item_rec record; begin select last_exec into exec__last_exec from cr_scheduled_release_job; for item_rec in select p.item_id, live_revision from cr_release_periods p, cr_items i where start_when between exec__last_exec and now() and p.item_id = i.item_id LOOP -- update publish status update cr_items set publish_status = 'live' where item_id = item_rec.item_id; exec__items_released := exec__items_released + 1; end loop; for item_rec in select p.item_id, live_revision from cr_release_periods p, cr_items i where end_when between exec__last_exec and now() and p.item_id = i.item_id LOOP -- update publish status update cr_items set publish_status = 'expired' where item_id = item_rec.item_id; exec__items_expired := exec__items_expired + 1; end loop; -- exception -- when others then -- err_num := SQLCODE; -- err_msg := substr(SQLERRM, 1, 500); -- end; -- keep a record of the update insert into cr_scheduled_release_log ( items_released, items_expired, err_num, err_msg ) values ( exec__items_released, exec__items_expired, exec__err_num, exec__err_msg ); -- Table was growing without bound (OpenACS DanW) delete from cr_scheduled_release_log where exec_date < now() - '4 week'::interval; -- Reset the last time of execution to start of processing update cr_scheduled_release_job set last_exec = exec__this_exec; return 0; end;$$ language plpgsql;