Volume rename/merge - Evergreen

Volume Rename Version 4.sql

-- This version supports moving a call number to a new prefix
-- Move volume to new volume name on same bib.  Used to rename volumes that get entered
-- incorrectly.
-- If there is already a volume with the same name, move copies into it.  Otherwize rename it.
-- Credit to tsbere for showing me the way with his parts moving function.
-- Update 10-29-2018 - excluded deleted volumes from search for existing volumes.
CREATE OR REPLACE FUNCTION evergreen.larl_moveto_volume(volume_id bigint, new_label text, new_prefix_id bigint)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  record_id bigint;
  call_number_id bigint;
  existing_volume integer;
  owning_lib_id integer;
  prefix_id integer;
BEGIN

  -- grab record_id and owning lib from asset.call_number - 
  SELECT INTO record_id,owning_lib_id,prefix_id record, owning_lib, prefix 
  FROM asset.call_number 
  WHERE id = volume_id and deleted=false and (label!=new_label or prefix!=new_prefix_id);
  IF NOT FOUND THEN
    RETURN;
  END IF;

  RAISE NOTICE 'record_id is % and owning lib is % and prefix_id is %', record_id, owning_lib_id, prefix_id;

  -- See if there is another existing volume for the current record that already has that label.
  SELECT INTO existing_volume id 
  FROM asset.call_number acn 
  WHERE acn.owning_lib=owning_lib_id and
  acn.record=record_id and 
  acn.label=new_label and
  acn.prefix=new_prefix_id and
  not acn.deleted;
  
  IF FOUND THEN  -- If there already is a volume with that label and prefix, switch to using it.
    RAISE NOTICE 'Existing volume found: %',existing_volume;
    UPDATE asset.copy SET call_number = existing_volume WHERE call_number = volume_id and deleted=false;
    UPDATE action.hold_request SET target=existing_volume WHERE target = volume_id and hold_type = 'V';
    UPDATE action.aged_hold_request SET target=existing_volume WHERE target = volume_id and hold_type = 'V';
    UPDATE asset.call_number set deleted=true where id = volume_id;
  ELSE -- Otherwise rename current volume
    RAISE NOTICE 'Rename Volume: %  %  %', volume_id, new_label, new_prefix_id;
    UPDATE asset.call_number set label=new_label, prefix=new_prefix_id where id=volume_id;
  END IF;
END;
$function$
;

volume rename.sql

-- Move volume to new volume name on same bib.  Used to rename volumes that get entered
-- incorrectly.
-- If there is already a volume with the same name, move copies into it.  Otherwize rename it.
-- Credit to tsbere for showing me the way with his parts moving function.
-- Update 10-29-2018 - exclude deleted call numbers when searching for existing.
CREATE OR REPLACE FUNCTION evergreen.larl_moveto_volume(volume_id bigint, new_label text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  record_id bigint;
  call_number_id bigint;
  existing_volume integer;
  owning_lib_id integer;
BEGIN

  -- grab record_id and owning lib from asset.call_number - 
  SELECT INTO record_id,owning_lib_id record, owning_lib 
  FROM asset.call_number 
  WHERE id = volume_id and deleted=false and label!=new_label;
  IF NOT FOUND THEN
    RETURN;
  END IF;

  RAISE NOTICE 'record_id is % and owning lib is %', record_id, owning_lib_id;

  -- See if there is another existing volume for the current record that already has that label.
  SELECT INTO existing_volume id 
  FROM asset.call_number acn 
  WHERE acn.owning_lib=owning_lib_id and
  acn.record=record_id and 
  acn.label=new_label and
  not acn.deleted;
  
  IF FOUND THEN  -- If there already is a volume with that label, switch to using it.
    RAISE NOTICE 'Existing volume found: %',existing_volume;
    UPDATE asset.copy SET call_number = existing_volume WHERE call_number = volume_id and deleted=false;
    UPDATE action.hold_request SET target=existing_volume WHERE target = volume_id and hold_type = 'V';
    UPDATE action.aged_hold_request SET target=existing_volume WHERE target = volume_id and hold_type = 'V';
    UPDATE asset.call_number set deleted=true where id = volume_id;
  ELSE -- Otherwise rename current volume
    RAISE NOTICE 'Rename Volume: %  %', volume_id, new_label;
    UPDATE asset.call_number set label=new_label where id=volume_id;
  END IF;
END;
$function$
;

Z example updates.sql

Begin;
--- Fix board books that don't have the BB E prefix
-- BB E prefix = 73

-- 
with fixme as (
select

aou.shortname, ccs.name, mrfr.value, acp.barcode, 

acn.id cn_id, acn.label

from container.copy_bucket_item ccbi
join asset.copy acp on acp.id=ccbi.target_copy
join asset.call_number acn on acn.id=acp.call_number
join asset.call_number_prefix acnp on acnp.id=acn.prefix
join config.copy_status ccs on ccs.id=acp.status
join actor.org_unit aou on aou.id=acp.circ_lib
left outer join metabib.real_full_rec mrfr on mrfr.record=acn.record and mrfr.tag='245' and mrfr.subfield='h'
where 

ccbi.bucket=5550 -- Copy bucket ID that contains copies you want to change the call number/prefix of.

order by 1,2
)

select * , evergreen.larl_moveto_volume(fm.cn_id,fm.label,73)
from fixme fm
--limit 5
;

Rollback;