Script to update records with invalid 005 field

The following script will update ALL invalid 005 fields with a predefined date.

Notice: If you need to set another default date, change 20211010113347.0 with the required date

SET SEARCH_PATH = '{tenantId}_mod_source_record_storage';
DROP FUNCTION IF EXISTS check_date_format();

CREATE OR REPLACE FUNCTION check_date_format()
RETURNS TABLE(current_value text, current_marc_id uuid, is_valid boolean) AS $$
DECLARE
    record_value text;
    record_marc_id uuid; 
BEGIN
    FOR record_value, record_marc_id IN
        SELECT value, marc_id FROM marc_indexers_005
    LOOP
        BEGIN
            PERFORM TO_DATE(SUBSTRING(record_value, 1, 8), 'yyyymmdd');
            RETURN QUERY SELECT record_value, record_marc_id, TRUE; 
        EXCEPTION
            WHEN others THEN
                RETURN QUERY SELECT record_value, record_marc_id, FALSE;
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

UPDATE marc_records_lb
SET content = jsonb_set(content, '{fields}',
  (
    SELECT jsonb_agg(
      CASE
        WHEN elem ? '005' THEN jsonb_set(elem, '{005}', '"20211010113347.0"')
        ELSE elem
      END
    )
    FROM jsonb_array_elements(content->'fields') AS elem
  )
)
WHERE id in (SELECT current_marc_id FROM check_date_format() WHERE is_valid = FALSE);

DROP FUNCTION IF EXISTS check_date_format();