Esta é a Edição #30 do General Bits. Nossa última novidade foi a tradução em Português e tem sido um grande sucesso, portanto, muito obrigado a Juliano S. Ignacio.

Não se esqueça de assinar o PostgreSQL General Bits. Tenha suporte aos seus projetos locais de código aberto! Novos recursos estão em artigos que estarão disponíveis somente à assinantes.

Como de costume, o retorno é esperado. Envie-nos seus comentários, correções e sugestões.

E todos nós estamos aguardando ansiosamente a versão 7.4!

Felicitações,

Editor: elein em varlena.com

Você pode criar e usar tabelas temporárias dentro de funções em pl/pgsql, no entanto, há uma "pegadinha". Funções em pl/pgsql são traduzidas quando são executadas pela primeira vez. Chamadas subsequentes usam a mesma estrutura da consulta para rodar. Se uma destas consultas acessam uma tabela temporária criada e removida pela função, isto irá funcionar somente a primeira vez, mas não na segunda. A segunda vez que a função for chamada, ela ainda estará tentando acessar a primeira tabela temporária que foi invocada, não a tabela temporária criada mais recentemente.

Para contornar ou corrigir a maneira de tratar esta situação, executamos consultas que acessam tabelas temporárias dentro de funções em pl/pgsql, usando a instrução EXECUTE. Utilizando a instrução EXECUTE, a consulta é retraduzida e a tabela temporária correta (a nova) é acessada.

	-- Isto não irá funcionar
	...
	CREATE TEMP TABLE foo AS SELECT ....;
	FOR r IN SELECT * FROM foo
	LOOP
		....
	END LOOP;
	DROP TABLE foo;

	-- Isto irá funcionar
	...
	CREATE TEMP TABLE foo AS SELECT ....;
	FOR r IN EXECUTE "SELECT * FROM foo"
	LOOP
		....
	END LOOP;
	DROP TABLE foo;
	...

Para converter a unidade monetária de um banco de dados para outra, você precisa somente configurar as variáveis LC_MONETARY e LC_NUMERIC. Isto irá notificar o banco de dados que a separação decimal deve ser vírgula ao invés de ponto, e também, qual o símbolo de moeda que deve ser utilizado.

Infelizmente, nenhuma das entradas e saídas para qualquer tipo de número prestam atenção a estas notificações. Conforme as especificações do SQL, o ponto é o separador decimal e, o PostgreSQL, segue a especificação sempre que possível.

As funções que prestam atenção às notificações de LC_MONETARY e LC_NUMERIC são to_char() e to_number(). Capturando as entradas corretamente através do uso de to_number() e, enviando para a saída, mostrando de maneira apropriada o valor, através do uso de to_char().

Neste exemplo nós temos uma coluna tipo ponto flutuante (float) e outra numérica (numeric) em uma tabela.

	create table to_what ( fl float, num numeric); 

	=# show LC_MONETARY;
	 lc_monetary 
	-------------
	 en_US

	=# show LC_NUMERIC;
	 lc_numeric 
	------------
	 en_US

	=# insert into to_what values ( 1.2, 34.56 );
	INSERT 1622475 1
	elein=# select * from to_what;
	 fl  |  num  
	-----+-------
	 1.2 | 34.56

	=# set LC_MONETARY='fr_FR';
	=# set LC_NUMERIC='fr_FR';
	=# insert into to_what values ( 78.09, 12.34 );
	=# insert into to_what values
		( to_number( '56,78', '99D99'), to_number('90,12','99D99') );

	=# select * from to_what;
	  fl   |  num  
	-------+-------
	   1.2 | 34.56
	 78.09 | 12.34
	 56.78 | 90.12

	=# select to_char( fl, '99D99'), to_char(num, '99D99') from to_what;
	 to_char | to_char 
	---------+---------
	 F  1,20 | F 34,56
	 F 78,09 | F 12,34
	 F 56,78 | F 90,12

Colaboradores: Tony Grant tony em tgds.net, Tom Lane tgl em sss.pgh.pa.us, elein em varlena.com

Embora saibamos que em um banco de dados relacional, todas as tabelas possuem uma chave primária funcional, às vezes acabamos com uma tabela de dados geradas por algum processo ou por qualquer pessoa que não possui esta mesma chave. Em casos como este nós criamos uma chave primária caso a chave natural não seja adequada.

No processo de carregar dados, pode acontecer às vezes de dados serem carregados duas vezes e você, então, acabará com registros duplicados na sua tabela e, a chave criada, não o ajudará em nada. E nem mesmo os OIDs (números sequenciais gerados pelo PostgreSQL para seu próprio controle), especialmente se a sua tabela não possuí-los também. (Mais um argumento para chaves naturais...)

Alguém, que ficará anônimo, possui uma tabela que é carregada diretamente de um arquivo log (de acompanhamento) do apache (servidor www). Alguém, esta mesma pessoa anônima, de alguma maneira errou e acabou carregando diversos registros uma segunda vez. oops.

Para ver o quão ruim é este problema, a consulta abaixo irá mostrar isto. Ela conta as instâncias duplicadas. A cláusula group by possui todas as colunas da tabela, então uma réplica é identificada por duas ou mais linhas contendo todas colunas equivalentes. A cláusula having é avaliada por último. Ela irá mostrar todos os registros que possuem uma contagem maior do que 1.

	select count(*) from
	(select count(*) as howmany from rawlog
		group by
		ip, ldate, ltime, method, doc, qs, code, dsize
		having count(*) > 1) foo;

	select count(*) as howmany, ldate, ltime, doc, ip, method, qs, code, dsize
	from rawlog
	group by ldate, ltime, doc, ip, method, qs, code, dsize
	having count(*) > 1;

Esta função utiliza a consulta acima para selecionar os dados dos registros duplicados. Ela apaga todos eles e re-insere um deles antes de ir para o próximo registro. A remoção e a re-inserção deve funcionar corretamente porque não deve haver dependências nesta tabela -- ela não tem chaves.

	create or replace function fixdupes()
	returns void as '
	DECLARE
	d record;
	BEGIN
	   for d in select oid 
	            from rawlog 
	            group by ldate, ltime, doc, ip, method, qs, code, dsize
	            having count(*) > 1 limit 1;
	   loop
	      delete from rawlog  
	      where ldate=d.ldate and ltime=d.ltime and 
	            doc=d.doc and ip=d.ip and method=d.method and
	            qs=d.qs and code=d.code and dsize=d.dsize;
	
	      insert into rawlog values (d.ip, d.ldate, d.ltime, d.method, d.doc,
	         d.qs, d.code, d.dsize);
	   end loop;
	   RETURN;
	END;
	' language 'plpgsql';

Colaboradores: elein em varlena.com

Esta assunto gera duas discussões em separado -- uma sobre alterar a estrutura das tabelas e a segunda, sobre gravar a ordem de exibição de colunas. Então, este assunto é dividido neste item e num outro seguinte.

Este ponto foi levantado (uma vez mais) sobre o PostgreSQL não suportar alterar as definições de uma coluna. O que não é suportado - particularmente - é alterar o tipo da coluna, e isto era ao que a maioria das dicussões se referiam.

A reclamação é que alguém tem que gerenciar todas as partes relacionadas manualmente incluindo chaves, chaves estrangeiras, restrições (constraints), gatilhos (triggers) e índices.

Não há planos no momento para implementar no PostgreSQL a instrução alter table com "todos os recursos" disponíveis. Esta é uma tarefa complexa que requer intervenção humana voltada para interpretação semântica e, sem isso, corremos um risco enorme de corromper dados. Por exemplo, o MySQL pode converter uma coluna do tipo caracter para o tipo data, mas, ele silenciosamente converte qualquer campo que ele não possa ententer para "um maravilhoso e formidável valor do tipo 0000-00-00". Desde que a transação (que transação?) não seja desfeita (rollback), seu valor original é perdido.

Os problemas que surgem ao mudar o tipo de uma coluna são muitos.

• O armazenamento se torna necessário.
• Deve haver clareza e segurança à falhas para tudo, à partir do momento que houver falha de conversão.
• Para alterar a base de dados atual deve-se considerar a manipulação de planos armazenados ou pré-disponíveis (cached).
• Aplicações existentes em plpgsql e chaves estrangeiras devem continuar a funcionar, incluindo as funções que retornam conjuntos de dados.
• Para manipular as falhas de conversão é necessário maiores informações sobre semântica ao alterar a coluna para algum tipo menor ou muito diferente.
• Os índices precisam ser removidos e reconstruidos. É possível que a classe de operadores para o novo tipo de dado seja diferente da original e, por causa disso, alguns índices podem não ser capazes de serem recriados exatamente como eram.
• Índices funcionais necessitam que a função seja removida e redefinida para o novo tipo, e então, o índice deve ser reconstruido.
• As restrições (constraints) devem ser alteradas para casar com os novos tipos de dados. Quaisquer funções inseridas em restrições devem ser alteradas para aceitar o novo tipo de dado.

A maioria das conversões de dados são tratadas por alterações de tipos (cast) implícitas ou explícitas e rotinas de conversão. São nestes casos onde as alterações de tipos não existem, onde haverá problemas de conversão de dados. Fica por sua conta consertar o dado e tentar novamente, ou usar um processo com uma função de conversão complexa.

A melhor sugestão foi dada por diversas pessoas que sugeriram que uma ferramenta administrativa como o pgAdmin poderia ser construida para fazer esta conversão. Tom Lane reforça dizendo, "Isto poderia levar você através de todas estas considerações, confirmar sua autorização em cada alteração semântica não trivial, e então, aplicar todas as operações dentro de uma única transação. Nós temos todas funcionalidades da instrução ALTER necessárias para nos auxiliar como uma ferramenta."

Aqui estão alguns algoritmos sugeridos para fazer as alterações básicas de colunas. Para elas, eu adicionaria uma lista de verificação dos itens para revisar as possíveis alterações:

• gerenciar erros de conversão de dados
• índices
• gatilhos (triggers)
• regras (rules) ou visões (views)
• restrições (constraints)
• índices funcionais
• chaves estrangeiras

Renomeando uma coluna existente
	BEGIN ;
	ALTER TABLE foo RENAME COLUMN col TO backupcol;
Criando uma nova coluna
	ALTER TABLE foo ADD COLUMN col newtype
Re-populando através de uma coluna existente
	UPDATE foo set col=backupcol;
Assumindo que todos os dados foram copiados com sucesso, remover a coluna existente
	ALTER TABLE foo DROP COLUMN backupcol;
	COMMIT;

Extraindo o esquema e os dados separadamente somente desta tabela.
	pg_dump -a -t tabletochange > tabledata.sql
	pg_dump -s -t tabletochange > tableschema.sql
Editar o esquema e fazer as alterações necessárias.
	vi tableschema.sql 
Recarregar o esquema e os dados. Tratar os erros de carregamento dos dados.
	psql testdatabase < tableschema.sql
	psql testdatabase < tabledata.sql

Criar uma tabela exatamente como a antiga 
exceto pela alteração da definição de uma (ou mais) coluna.
	CREATE TABLE new (
	...
	);
Carregar a nova tabela à partir da antiga.
	INSERT INTO new SELECT ... FROM old;
Se tudo ocorreu bem, remova a tabela antiga e renomeie a nova.
	DROP TABLE old;
	ALTER TABLE new RENAME TO old;

Colaboradores: Sven Koehler skoehler em upb.de, Jonathan Bartlett johnnyb em eskimo.com, Stephan Szabo sszabo em megazone23.bigpanda.com, Reynard Hilman reynardmh em lightsky.com, scott.marlowe scott.marlowe em ihs.com, Alvaro Herrera alvherre em dcc.uchile.cl, weigelt em metux.de, Tino Wildenhain tino em wildenhain.de, Tom Lane tgl em sss.pgh.pa.us

A segunda parte deste artigo trata da ordem de apresentação das colunas. Isto significa, a ordenação padrão quando executamos um SELECT *. Isto não significa reordenar as colunas armazenadas no disco.

Foi apontado que a tabela do catálogo do sistema pg_attribute é o lugar natural para guardar informações sobre a ordem das colunas. A adição da coluna attlognum, distinta da coluna attnum que informa a posição da coluna física, vem sendo discutida já à alguns meses. Ferramentas administrativas poderiam utilizar isto para determinar a ordem de apresentação das colunas também.

No entanto, esta é uma função de uma aplicação cliente, mesmo que o cliente seja uma ferramenta administrativa do PostgreSQL. O pgaccess, por exemplo, armazena a ordenação separadamente do catálogo do sistema. Algumas pessoas não concordam com esta idéia porque não gostam que ferramentas administrativas criem tabelas no banco de dados. Mas as ferramentas administrativas são somente um tipo de cliente. Um banco de dados deve ser construido para múltiplos clientes. Somente SELECT * deveria ser afetado por este atributo e nada mais no lado do servidor.

Outras soluções foram apresentadas, incluindo as mais óbvias onde nunca se deveria usar SELECT *, de qualquer forma, sempre nomeie suas listas de destino (target list) explicitamente, e claro, as visões (views).

Ainda assim, Tom Lane sustenta que o pedido para apresentar as colunas em ordem é razoável (mas ainda não foi feito) e, você pode ver os benefícios caso todos os clientes de banco de dados pudessem desenhar sobre uma apresentação ordenada de colunas centralizada para consistência entre as aplicações.

Colaboradores: Tom Lane tgl em sss.pgh.pa.us, Jim C. Nasby jim em nasby.net, Sven Köhler skoehler em upb.de, Guillaume LELARGE gleu em wanadoo.fr, Bruno Wolff III bruno em wolff.to, weigelt em metux.de, Jay O'Connor joconnor em cybermesa.com, Nigel J. Andrews nandrews em investsystems.co.uk, Bruno BAGUETTE pgsql-ml em baguette.net, Randal L. Schwartz merlyn em stonehenge.com , Ernest E Vogelsinger ernest em vogelsinger.at, Mike Mascari mascarm em mascari.com, Tino Wildenhain tino em wildenhain.de