authen.public

Parameters

Name	Type	Mode
i_prop_code	text	IN (DEFAULT NULL)

Definition

DECLARE
    batch        record;
    file_rec     record;
    date_str     text;
    file_date    date;
    filename     text;
    old_key      text;
    new_key      text;
    moved_count  int := 0;
BEGIN
    FOR batch IN
        SELECT DISTINCT lower(p.code) AS prop_code
        FROM batch_report br 
        JOIN property p ON p.id = br.prop_id
        WHERE br.batch_name = '@BACKUP' AND br.output_type = 'file' 
            AND (i_prop_code IS NULL OR upper(p.code) = upper(i_prop_code))
    LOOP
        FOR file_rec IN
            SELECT * FROM doc.s3_list_file_details(
                batch.prop_code || '/report/file/BackupReport/',
                'fromas'
            )
        LOOP
            -- file_rec.file_name is relative, e.g. "20260118/Daily CheckIn.pdf"
            -- Match only old-format files whose first path segment is 8 digits
            date_str := (regexp_match(file_rec.file_name, '^(\d{8})'))[1];
            CONTINUE WHEN date_str IS NULL;

file_date := to_date(date_str, 'YYYYMMDD');
            filename  := regexp_replace(file_rec.file_name, '^[^/]+/', '');
            old_key   := batch.prop_code || '/report/file/BackupReport/' || file_rec.file_name;

new_key := format('%s/report/file/BackupReport/%s/%s-%s/%s/%s',
                batch.prop_code,
                to_char(file_date, 'YYYY'),
                to_char(file_date, 'MM'),
                to_char(file_date, 'FMMonth'),
                to_char(file_date, 'YYYYMMDD'),
                filename
            );

PERFORM doc.s3_move_file(old_key, new_key);
            moved_count := moved_count + 1;
        END LOOP;
    END LOOP;

RETURN moved_count;
END

sp_migrate_backup_report_paths

Parameters

Definition