Copia de tabelas em servidores diferentes com PLPGSQL

Um desafio que apareceu foi fazer cópia de 54 tabelas de um servidor Postgres de um servidor para outro. Estas tabelas são formadas por 2 grupos de 27 tabelas referentes à 27 unidades da federação. O primeiro caminho que segui para solução do problema, foi fazer uma cópia de segurança, vulgo BACKUP, e restaurar em outro servidor. O problema apareceu quando percebi que o arquivo resultante da cópia era muito grande e o processo de restauração levaria mais tempo que a cópia. Assim passei para outra solução, criar um script para fazer a cópia da tabela. Para isso utilizei o dblink e a linguagem plpgsql.

O código para criar a função foi:


create or replace function copia_pessoa(esquema varchar, uf varchar) returns text as $$
declare
     tabela1 varchar;

begin
        tabela1 := 'tab_cad_'|| esquema || '.tb_pessoa_uf_' || uf;
RAISE NOTICE 'Tabela destino %' ,tabela1;
execute 'create table '|| tabela1 ||' as
select * from dblink 
(''port=porta dbname=database host=iphost user=user password=senha'',
''select * from  ' || tabela1 || ' '') as t1
(
  cd_ibge character varying(13),
  cod_familiar_fam character varying(11),
  num_membro_fmla character varying(11),
  marc_pbf numeric(8,0),
  marc_bsp numeric(8,0)
)' ;

execute ' CREATE INDEX idx_cd_ibge_uf'||uf||'_pess_'||esquema||'   ON ' || tabela1 || '  USING btree   (cd_ibge)';

execute ' CREATE INDEX idx_cod_familiar_fam_uf' ||uf|| '_pess_' ||esquema||' ON '|| tabela1 ||' USING btree   (cod_familiar_fam)';



return 'Sucesso ' || tabela1;
end;

$$ language plpgsql;


Destaco que neste código pude fazer com que o nome da tabela é uma variável conforme o parâmetro passado.

tabela1 := 'tab_cad_'|| esquema || '.tb_pessoa_uf_' || uf;


O nome da tabela é composto pelo nome do esquema e código do ibge da UF.

O comando EXECUTE permite que o comando SQL seja executado com parâmetros, neste caso o nome da tabela.

execute 'create table '|| tabela1 ||' as
select * from dblink ...';

Para utilizar a função pode-se utilizar o comando abaixo, onde o primeiro parâmetro é o nome do esquema e o segundo parâmetro é o código ibge da UF.

select copia_pessoa('nome_do_esquema','12')

Uma outra forma de utilização foi:

select copia_pessoa('18052013',ibge::varchar) from estados

onde na tabela estados se obtém os códigos do IBGE dos estado no campo ibge.

create table estados (ibge integer, uf char(2));

 insert into estados (ibge, uf) values (11,'RO');
 insert into estados (ibge, uf) values (12,'AC');
 insert into estados (ibge, uf) values (13,'AM');
 insert into estados (ibge, uf) values (14,'RR');
 insert into estados (ibge, uf) values (16,'AP');
 insert into estados (ibge, uf) values (17,'TO');
 insert into estados (ibge, uf) values (22,'PI');
 insert into estados (ibge, uf) values (23,'CE');
 insert into estados (ibge, uf) values (24,'RN');
 insert into estados (ibge, uf) values (25,'PB');
 insert into estados (ibge, uf) values (27,'AL');
 insert into estados (ibge, uf) values (28,'SE');
 insert into estados (ibge, uf) values (29,'BA');
 insert into estados (ibge, uf) values (31,'MG');
 insert into estados (ibge, uf) values (41,'PR');
 insert into estados (ibge, uf) values (43,'RS');
 insert into estados (ibge, uf) values (50,'MS');
 insert into estados (ibge, uf) values (51,'MT');
 insert into estados (ibge, uf) values (52,'GO');
 insert into estados (ibge, uf) values (53,'DF');
 insert into estados (ibge, uf) values (15,'PA');
 insert into estados (ibge, uf) values (21,'MA');
 insert into estados (ibge, uf) values (33,'RJ');
 insert into estados (ibge, uf) values (35,'SP');
 insert into estados (ibge, uf) values (42,'SC');
 insert into estados (ibge, uf) values (26,'PE');

 insert into estados (ibge, uf) values (32,'ES');