renomear_tbls_novo_bucket.sql

renomear_tbls_novo_bucket.sql

DO $$
DECLARE
  table_name   TEXT := 'financeiro_retornos';
  column_name  TEXT := 'url';
  batch_size   INT  := 50000;
  total        INT;
  sql          TEXT;
BEGIN
  LOOP
    sql := format($f$
      UPDATE %I
      SET %I = CASE
        WHEN %I LIKE '%%scproduction.s3.sa-east-1.amazonaws.com%%' THEN
          REPLACE(%I, 'scproduction.s3.sa-east-1.amazonaws.com', 'erp-prod-virginia.s3.us-east-1.amazonaws.com')
        WHEN %I LIKE '%%scproduction.s3.amazonaws.com%%' THEN
          REPLACE(%I, 'scproduction.s3.amazonaws.com', 'erp-prod-virginia.s3.amazonaws.com')
        ELSE
          %I
      END
      WHERE id IN (
        SELECT id
        FROM %I
        WHERE %I IS NOT NULL
          AND (
            %I LIKE '%%scproduction.s3.amazonaws.com%%'
            OR %I LIKE '%%scproduction.s3.sa-east-1.amazonaws.com%%'
          )
        ORDER BY id
        LIMIT %s
      );
    $f$,
      table_name, column_name,
      column_name, column_name,
      column_name, column_name,
      column_name,
      table_name, column_name,
      column_name, column_name,
      batch_size
    );

    EXECUTE sql;

    GET DIAGNOSTICS total = ROW_COUNT;
    EXIT WHEN total = 0;
    RAISE NOTICE 'Atualizadas % linhas na tabela % (coluna %)...', total, table_name, column_name;
  END LOOP;
END $$;