CREATE OR REPLACE FUNCTION actor.usr_purge_data(
src_usr IN INTEGER,
specified_dest_usr IN INTEGER
) RETURNS VOID AS $$
DECLARE
suffix TEXT;
renamable_row RECORD;
dest_usr INTEGER;
BEGIN
IF specified_dest_usr IS NULL THEN
dest_usr := 1; -- Admin user on stock installs
ELSE
dest_usr := specified_dest_usr;
END IF;
UPDATE actor.usr SET
active = FALSE,
card = NULL,
mailing_address = NULL,
billing_address = NULL
WHERE id = src_usr;
-- acq.*
UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
-- Update with a rename to avoid collisions
FOR renamable_row in
SELECT id, name
FROM acq.picklist
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE acq.picklist
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
-- action.*
DELETE FROM action.circulation WHERE usr = src_usr;
UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
DELETE FROM action.hold_request WHERE usr = src_usr;
UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
DELETE FROM action.survey_response WHERE usr = src_usr;
UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
-- actor.*
DELETE FROM actor.card WHERE usr = src_usr;
DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
-- The following update is intended to avoid transient violations of a foreign
-- key constraint, whereby actor.usr_address references itself. It may not be
-- necessary, but it does no harm.
UPDATE actor.usr_address SET replaces = NULL
WHERE usr = src_usr AND replaces IS NOT NULL;
DELETE FROM actor.usr_address WHERE usr = src_usr;
DELETE FROM actor.usr_note WHERE usr = src_usr;
UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
DELETE FROM actor.usr_setting WHERE usr = src_usr;
DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
-- asset.*
UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
-- auditor.*
DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
-- biblio.*
UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
-- container.*
-- Update buckets with a rename to avoid collisions
FOR renamable_row in
SELECT id, name
FROM container.biblio_record_entry_bucket
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE container.biblio_record_entry_bucket
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
FOR renamable_row in
SELECT id, name
FROM container.call_number_bucket
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE container.call_number_bucket
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
FOR renamable_row in
SELECT id, name
FROM container.copy_bucket
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE container.copy_bucket
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
FOR renamable_row in
SELECT id, name
FROM container.user_bucket
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE container.user_bucket
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
-- money.*
DELETE FROM money.billable_xact WHERE usr = src_usr;
DELETE FROM money.collections_tracker WHERE usr = src_usr;
UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
-- permission.*
DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
-- reporter.*
-- Update with a rename to avoid collisions
BEGIN
FOR renamable_row in
SELECT id, name
FROM reporter.output_folder
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE reporter.output_folder
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
EXCEPTION WHEN undefined_table THEN
-- do nothing
END;
BEGIN
UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
EXCEPTION WHEN undefined_table THEN
-- do nothing
END;
-- Update with a rename to avoid collisions
BEGIN
FOR renamable_row in
SELECT id, name
FROM reporter.report_folder
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE reporter.report_folder
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
EXCEPTION WHEN undefined_table THEN
-- do nothing
END;
BEGIN
UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
EXCEPTION WHEN undefined_table THEN
-- do nothing
END;
BEGIN
UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
EXCEPTION WHEN undefined_table THEN
-- do nothing
END;
-- Update with a rename to avoid collisions
BEGIN
FOR renamable_row in
SELECT id, name
FROM reporter.template_folder
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE reporter.template_folder
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
EXCEPTION WHEN undefined_table THEN
-- do nothing
END;
-- vandelay.*
-- Update with a rename to avoid collisions
FOR renamable_row in
SELECT id, name
FROM vandelay.queue
WHERE owner = src_usr
LOOP
suffix := ' (' || src_usr || ')';
LOOP
BEGIN
UPDATE vandelay.queue
SET owner = dest_usr, name = name || suffix
WHERE id = renamable_row.id;
EXCEPTION WHEN unique_violation THEN
suffix := suffix || ' ';
CONTINUE;
END;
EXIT;
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;