Content Comparison

CREATE OR REPLACE FUNCTION is_UUID(string text) RETURNS BOOLEAN AS $$
DECLARE
  uuid_pattern TEXT := '^[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}$';
BEGIN
  IF string IS NOT NULL AND string ~ uuid_pattern THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION rename_subfields_9_for_0(input_jsonb JSONB) RETURNS JSONB AS $$
DECLARE
  field_key TEXT;
  field_obj JSONB;
  subfield_obj JSONB;
  subfields_array JSONB;
  updated_jsonb JSONB;
BEGIN
  updated_jsonb := input_jsonb;

  -- Loop through the "fields" array
  FOR i IN 0..(jsonb_array_length(updated_jsonb->'fields') - 1) LOOP
    field_obj := updated_jsonb->'fields'->i;

    FOR field_key, subfields_array IN SELECT key, value FROM jsonb_each(field_obj) LOOP
      subfields_array := subfields_array->'subfields';

      -- Check if the subfields array exists
      IF subfields_array IS NOT NULL THEN
        FOR j IN 0..(jsonb_array_length(subfields_array) - 1) LOOP
          subfield_obj := subfields_array->j;

          -- If the subfield equals '9' and is not UUID, rename it to '0'
          IF subfield_obj->'9' IS NOT NULL AND NOT is_UUID(subfield_obj->>'9') THEN
            subfield_obj := subfield_obj || jsonb_build_object('0', subfield_obj->'9');
            subfield_obj := subfield_obj - '9';
            updated_jsonb := jsonb_set(updated_jsonb, ARRAY['fields', i::text, field_key, 'subfields', j::text], subfield_obj, true);
          END IF;
        END LOOP;
      END IF;
    END LOOP;
  END LOOP;

  RETURN updated_jsonb;
END;
$$ LANGUAGE plpgsql;

UPDATE {tenant}_mod_source_record_storage.marc_records_lb
SET content = rename_subfields_9_for_0(content);