Dados das tabelas;
spi.operacao --4352044 Registros
spi.auditoria --1014656 Registros
spi.datalegado --0
spi.dataoperacao --14793986 Registros
spi.detalheoperacao--182374 Registros
spi.extrato --3001929 Registros Registros
spi.extrato_cam --28017 Registros
spi.legado --41461940 Registros
spi.log--0 Registros
--Script
DO $$
DECLARE
start_time timestamp := clock_timestamp();
schema_orig text := 'public';
schema_dest text := 'teste';
table_pai text := 'operacao';
tables_ text[] := ARRAY['operacao', 'auditoria', 'datalegado','dataoperacao','detalheoperacao','extrato', 'legado','log','extrato_cam'];
cut_off_date TIMESTAMP := '2021-03-01';
total_rows_ins integer := 0;
total_rows_del integer := 0;
table_name_var text;
msgid_value integer;
ispb_value integer;
rows_affected integer := 0;
BEGIN
PERFORM set_config('max_parallel_workers_per_gather', '12', true);
FOREACH table_name_var IN ARRAY tables_ LOOP
total_rows_ins := 0;
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = schema_orig AND table_name = table_name_var) THEN
FOR msgid_value, ispb_value IN (SELECT msgid, ispb FROM public.operacao WHERE ts_inclusao < cut_off_date) LOOP
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = schema_dest AND table_name = table_name_var) THEN
RAISE NOTICE 'Inserindo na tabela %.%',schema_dest, table_name_var;
IF table_name_var = 'operacao' THEN
EXECUTE format('INSERT INTO %I.%I (msgid, nuop, ispb, codmsg, lgnoperad, mqid, codmsgerro, lgnautoriz, lgnpiloto, sitlanc, cod, coa, identddestinatario, identdemissor, statusop, statusmsg, ts_inclusao, domsist, prioridade, dt_movto, authid)
SELECT msgid, nuop, ispb, codmsg, lgnoperad, mqid, codmsgerro, lgnautoriz, lgnpiloto, sitlanc, cod, coa, identddestinatario, identdemissor, statusop, statusmsg, ts_inclusao, domsist, prioridade, dt_movto, authid
FROM %I.%I WHERE ts_inclusao < $1 ON CONFLICT DO NOTHING',
schema_dest, table_name_var, schema_orig, table_name_var) USING cut_off_date;
GET DIAGNOSTICS rows_affected = ROW_COUNT;
total_rows_ins := total_rows_ins + rows_affected
ELSIF table_name_var = 'extrato_cam' THEN
EXECUTE format('INSERT INTO %I.%I (numctrl, tsop, ispb, flagacao, vlrmsg, numctrl_cam, dt_movto, codser, codmsg)
SELECT numctrl, tsop, ispb, flagacao, vlrmsg, numctrl_cam, dt_movto, codser, codmsg
FROM %I.%I WHERE ispb = $1 ON CONFLICT DO NOTHING',
schema_dest, table_name_var, schema_orig, table_name_var) USING ispb_value;
GET DIAGNOSTICS rows_affected = ROW_COUNT;
total_rows_ins := total_rows_ins + rows_affected;
END IF;
ELSE
RAISE NOTICE 'A tabela %.% não existe no esquema de destino.', schema_dest, table_name_var;
END IF;
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'Dados duplicados encontrados na tabela %.%, pulando para a próxima linha.', schema_orig ,table_name_var;
WHEN foreign_key_violation THEN
RAISE NOTICE 'Violada restrição FOREIGN KEY na tabela %.%, pulando para a próxima linha.', schema_orig, table_name_var;
WHEN others THEN
RAISE NOTICE 'Erro ao inserir a linha na tabela %.%:', schema_dest, table_name_var;
RAISE NOTICE 'Detalhes do erro: SQL STATE: % | SQLSTATE: %', SQLERRM, SQLSTATE;
ROLLBACK; -- Executa o rollback de todas as operações
RAISE NOTICE 'ROLLBACK concluído.';
END;
END LOOP;
RAISE NOTICE 'Total de linhas inseridas na tabela %.%: %', schema_dest, table_name_var, total_rows_ins;
ELSE
RAISE NOTICE 'A tabela %.% não existe no esquema de origem.', schema_orig, table_name_var;
END IF;
END LOOP;
FOR i IN REVERSE array_length(tables_, 1) .. 1 LOOP
table_name_var := tables_[i];
total_rows_del := 0; -- Reinicia o contador de linhas deletadas para esta tabela
IF table_name_var = 'extrato_cam' THEN
query_text := format('DELETE FROM %I.%I WHERE ispb IN (SELECT ispb FROM %I.%I WHERE ts_inclusao < $1)', schema_orig, table_name_var, schema_orig, table_pai);
ELSE
query_text := format('DELETE FROM %I.%I WHERE msgid IN (SELECT msgid FROM %I.%I WHERE ts_inclusao < $1)', schema_orig, table_name_var, schema_orig, table_pai);
END IF;
EXECUTE query_text USING cut_off_date;
GET DIAGNOSTICS rows_affected = ROW_COUNT;
total_rows_del := total_rows_del + rows_affected;
RAISE NOTICE 'Total de linhas deletadas na tabela %.%: %', schema_orig, table_name_var, total_rows_del;
END LOOP;
RAISE NOTICE 'Tempo total de execução: %', clock_timestamp() - start_time;
END $$;
