BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0021'); -- Must drop a dependent view temporarily: DROP VIEW extend_reporter.full_circ_count; -- Now drop and create the view we want to change: DROP VIEW action.all_circulation; CREATE OR REPLACE VIEW action.all_circulation AS SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date, stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine, max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule, max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ FROM action.aged_circulation UNION ALL SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year, cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, circ.fine_interval, circ.recuring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, circ.recuring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, circ.parent_circ FROM action.circulation circ JOIN asset.copy cp ON (circ.target_copy = cp.id) JOIN asset.call_number cn ON (cp.call_number = cn.id) JOIN actor.usr p ON (circ.usr = p.id) LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id) LEFT JOIN actor.usr_address b ON (p.billing_address = a.id); -- Recreate the temporarily dropped view, with a revised view action.all_circulation: CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS SELECT cp.id, COALESCE(sum(c.circ_count), 0::bigint) + COALESCE(count(circ.id), 0::bigint) + COALESCE(count(acirc.id), 0::bigint) AS circ_count FROM asset."copy" cp LEFT JOIN extend_reporter.legacy_circ_count c USING (id) LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id GROUP BY cp.id; -- Change the pre-delete trigger to copy the new columns CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ DECLARE found char := 'N'; BEGIN -- If there are any renewals for this circulation, don't archive or delete -- it yet. We'll do so later, when we archive and delete the renewals. SELECT 'Y' INTO found FROM action.circulation WHERE parent_circ = OLD.id LIMIT 1; IF found = 'Y' THEN RETURN NULL; -- don't delete END IF; -- Archive a copy of the old row to action.aged_circulation INSERT INTO action.aged_circulation (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date, stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine, max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule, max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ) SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date, stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine, max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule, max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ FROM action.all_circulation WHERE id = OLD.id; RETURN OLD; END; $$ LANGUAGE 'plpgsql'; -- New post-delete trigger to propagate deletions to parent(s) CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$ BEGIN -- Having deleted a renewal, we can delete the original circulation (or a previous -- renewal, if that's what parent_circ is pointing to). That deletion will trigger -- deletion of any prior parents, etc. recursively. IF OLD.parent_circ IS NOT NULL THEN DELETE FROM action.circulation WHERE id = OLD.parent_circ; END IF; RETURN OLD; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER age_parent_circ AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.age_parent_circ_on_delete (); COMMIT;