Categorias de Volatilidade de Funções no Postgres

O processamento de funções em bancos de dados é uma opção bastante utilizado em certos ambientes. A quantidade e complexidade das lógicas de negócio que são alocadas nas funções podem ser expressivas. O lado negativo de se empregar funções no Postgres ou em qualquer SGBD é a necessidade de um tempo significativo de processamento.

Caso se utilize muitas funções o sistema, ao mesmo tempo em que gerencia os dados, passa a ser também um processador de regras da camada de negócios do sistema. Um recurso válido para facilitar o processamento de funções, reduzindo o esforço computacional, é empregar as categorias de volatilidade de funções no Postgres ao escrever os seus códigos. Esta feature promete maior desempenho no processamento das funções sem a necessidade de alterações no corpo das funções implementadas.

As categorias de funções são:

- VOLATILE - Uma função volatile pode alterar os dados de um banco. Também pode retornar valores diferentes em chamadas sucessivas com os mesmos parâmetros. Uma função com essas características terá seu plano de execução recalculado a cada chamada da função, portanto não é passível de otimização;

- STABLE - Quando uma função é STABLE, assume-se que a mesma não modifica a base de dados e retornará os mesmos valores caso sejam fornecidos os mesmos parâmetros na mesma chamada. Desta forma, o processador de consultas do postgres pode otimizar múltiplas chamadas da função para uma única chamada;

- IMMUTABLE - Se a sua função não altera jamais a base de dados e sempre retorna os mesmos valores para os mesmos parâmetros, não importando o contexto, deve ser classificada como IMMUTABLE, facilitando a otimização das consultas. Caso uma função seja assinalada como IMMUTABLE, mas não seguir este comportamento, apresentando respostas distintas para um mesmo conjunto de parâmetros, por exemplo, este será um erro difícil de rastrear.

* Como definir a classificação das funções?

Se a função apresentar comandos INSERT, DELETE ou UPDATE, pode portanto alterar o banco de dados, e deve ser assinalada como VOLATILE. Caso utilize ou produza valores aleatórios (random()), ou empregue funções que variam seu resultado como currval(), timeofday(), também se classifica como VOLATILE.

Caso ao sua função não possua comandos de alteração de dados, mas apresente o comando SELECT internamente, o resultado da consulta pode variar a cada chamada da função, pois o banco de dados está sujeito a atualizações entre as execuções da função, então a mesma possivelmente pode ser classificada como STABLE. Utilizei o termo "possivelmente" porque a consulta pode ser feita, por exemplo, em tabelas que não sofrem atualização, e neste caso, a função até poderia ser classificada como IMMUTABLE(!). SE sua função emprega funções da família de current_timestamp, possivelmente se alinhará com a classificação STABLE, pois o valor destas funções não muda no decorrer da transação.

Funções que realizem cálculos matemáticos sem utilização de valores aleatórios e funções com resultado variável são candidatas a IMMUTABLE, assim como funções que realizem validações simples sobre os parâmetros fornecidos, sem uso do comando SELECT.

O valor padrão é VOLATILE, assumido pelo postgres quando não fornecido pelo programador.

Uma função VOLATILE enxerga alterações no banco de dados ocorridas durante sua execução, enquanto que funções STABLE e IMMUTABLE não apresentam esta visibilidade.

* IMMUTABLE

O exemplo 1 gera erro em tempo de execução por alterar o banco de dados com o comando INSERT em uma função IMMUTABLE.

Exemplo 1:

CREATE TABLE teste (codigo integer, descricao varchar(20));

CREATE OR REPLACE FUNCTION teste_ins() RETURNS varchar(20) AS $$
BEGIN
    INSERT INTO teste VALUES (1,'Teste Class.');
    RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

postgres=# SELECT teste_ins();
ERRO:  INSERT não é permitido em uma função não-volátil
CONTEXTO:  comando SQL "INSERT INTO teste VALUES (1,'Teste Clas.')"
PL/pgSQL function "teste_ins" line 2 at comando SQL
O exemplo 2 executa normalmente. ele utiliza o SELECT mas não consulta tabelas ou visões, então pode ser IMMUTABLE.

Exemplo 2:

CREATE OR REPLACE FUNCTION teste_soma_p1_p2(p1 integer, p2 integer) RETURNS integer AS $$
BEGIN
    RETURN (select $1 + $2);
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

postgres=# SELECT teste_soma_p1_p2 (1,1); 
teste_soma_p1_p2
------------------
                2
(1 registro)

postgres=# SELECT teste_soma_p1_p2 (5,4);
 teste_soma_p1_p2
------------------
                9
(1 registro)


* STABLE

O exemplo abaixo funciona como função STABLE, apresentando o comando SELECT.

Exemplo 3:

CREATE OR REPLACE FUNCTION teste_select() RETURNS varchar(10) AS $$
BEGIN
    RETURN (SELECT CAST(count(*) AS VARCHAR) FROM teste);
END;
$$ LANGUAGE PLPGSQL STABLE;

postgres=# SELECT teste_select();
 teste_select
--------------
 1
(1 registro)

O exemplo 4 apresenta função STABLE om utilização da função current_timestamp().

Exemplo 4:

CREATE OR REPLACE FUNCTION teste_timest() RETURNS varchar(30) AS $$
BEGIN
    RETURN (SELECT CAST(current_timestamp AS VARCHAR) );
END;
$$ LANGUAGE PLPGSQL STABLE;

postgres=# SELECT teste_timest();
         teste_timest         
-------------------------------
 2012-08-24 10:57:59.429209-03
(1 registro)

* VOLATILE

O exemplo abaixo só funciona se a função for VOLATILE.

Exemplo 5:

postgres=# CREATE OR REPLACE FUNCTION teste_ins() RETURNS varchar(20) AS $$
BEGIN
INSERT INTO teste VALUES (1,'Teste Class.');
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL VOLATILE;

postgres=# SELECT teste_ins();
 teste_ins
-----------
 OK
(1 registro)

Os exemplos apresentados são relativamente simples, mas quanto mais complexa a função, maior o ganho de utilização da classificação de funções. Empregue no seu dia a dia este recurso do postgres, mas não se esqueça de fazer testes criteriosos para cada função, pois os erros de execução causados pela classificação incorreta de uma função podem de difícil detecção.

Tratamento de Parâmetros de Funções com Pl/PgSQL

Existem várias formas de se processar erros em parâmetros fornecidos a funções. Existem casos em que valores diferentes do esperado e nulos são fornecidos, o que faz com que as entradas de parâmetros devam receber um tratamento meticuloso.

Neste post vamos apresentar alguns recursos simples que podem ser utilizados para tratar parâmetros em funções no Postgresql.

* Raise Notice

Utilize Raise Notice para disparar avisos ao usuário da função. Estes avisos podem funcionar como advertências, apresentar informações relevantes sobre os parâmetros fornecidos e sobre a execução da função em si.

Estes avisos não interrompem a execução da função nem são considerados erros pelos aplicativos.

Exemplo 1:

CREATE OR REPLACE FUNCTION teste_par_1(par_1 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_1) < 2 THEN
    RAISE NOTICE 'Valor não fornecido ou muito pequeno: %',$1;
    RETURN 'AVISO';
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL;


banco=# Select teste_par_1 ('T');
NOTA:  Valor não fornecido ou muito pequeno: T
 teste_par_1
-------------
 AVISO
(1 registro)


* Raise Exception

Utilize Raise Exception para disparar um erro ao usuário da função acompanhado de uma mensagem explicativa. A emissão de erro interrompe a execução da função.

Exemplo 2:

CREATE OR REPLACE FUNCTION teste_par_2(par_2 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_2) < 2 THEN
    RAISE EXCEPTION 'Formato inválido: %',$1;
    RETURN 'ERRO';
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL;

banco=# Select teste_par_2 ('T');

ERRO:  Formato inválido: T

* RETURNS NULL ON NULL INPUT ou STRICT

O uso da cláusula STRICT ou "RETURNS NULL ON NULL INPUT" faz com que seja retornado valor nulo caso um dos parâmetros fornecidos seja nulo. É um recurso interessante e que pode poupar tempo de processamento em funções mais elaboradas. Para que a função aceite valores nulos, existe a cláusula "CALLED ON NULL INPUT", mas a mesma é pouco utilizada por ser o comportamento default para as funções no Postgresql.


Observe no exemplo abaixo que o valor nulo (null) é diferente da string sem elementos.

Exemplo 3:

CREATE OR REPLACE FUNCTION teste_par_3(par_3 varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL RETURNS NULL ON NULL INPUT;

 
banco=# Select teste_par_3 (null);
 teste_par_3
-------------
 
(1 registro)

banco=# Select teste_par_3 ('');
 teste_par_3
-------------
 OK
(1 registro)
banco=# Select teste_par_3 ('T');
 teste_par_3
-------------
 OK
(1 registro)
A utilização de várias validações conjuntamente é a melhor forma de assegurar que a função receba valores processáveis. O exemplo abaixo é uma ilustração desta necessidade.

Exemplo 4:

CREATE OR REPLACE FUNCTION teste_par (par_todos varchar(10)) RETURNS varchar(10) AS
$$
BEGIN
IF char_length(par_todos) <=3  THEN
    RAISE EXCEPTION 'Valor muito pequeno não nulo: %',$1;
    RETURN 'ERRO';
ELSE
    IF char_length(par_todos) <=5  THEN
        RAISE NOTICE 'Valor muito pequeno: %',$1;
        RETURN 'AVISO';
    END IF;
END IF;
RETURN 'OK';
END;
$$ LANGUAGE PLPGSQL RETURNS NULL ON NULL INPUT;

pf=# Select teste_par (null);
 teste_par
-----------

(1 registro)

pf=# Select teste_par ('T');
ERRO:  Valor muito pequeno não nulo: T
 
pf=# Select teste_par ('Test');
NOTA:  Valor muito pequeno: Test
 teste_par
-----------
 AVISO
(1 registro)

Atualmente existem além de NOTICE e EXCEPTION vários outros qualificadores das mensagens: DEBUG, LOG, INFO, NOTICE, WARNING, e EXCEPTION, sendo que este último é o valor padrão. 

Utilize-os nas suas validações, tentando sempre manter o código o mais simples possível!

Expresso Livre: Mais de 500000 contas de correio eletrônico. Todas PostgreSQL!

Email, Agenda, Catálogo de Endereços, Workflow e Mensagens Instantâneas em um único ambiente. Essa é a promessa do Expresso Livre, software livre mantido por um consórcio de entidades que engloba:

• CAIXA ECONÔMICA FEDERAL
• CELEPAR - Empresa de TI do Governo do Paraná
• PROCERGS - Empresa de TI do Governo do Rio Grande do Sul
• PROGNUS - Empresa de Consultoria
• SERPRO - Empresa de TI do Governo Federal

A ferramenta está em desenvolvimento desde 2007 e é utilizada atualmente por mais de 500.000 usuários em 167 empresas ou instituições, e utiliza como banco de dados o PostgreSQL. Vale a pena conhecer!

Gere Automaticamente seus Comandos GRANT e REVOKE!

Os comandos GRANT e REVOKE concedem e retiram permissões de acesso dos usuários aos objetos do banco de dados relativas à inserção, exclusão e alteração de dados, entre outras possibilidades. Neste post, vamos gerar automaticamente comandos GRANT e REVOKE utilizando SQL. Este tipo de procedimento não é muito comum porque ambos os comandos apresentam sintaxes simples que permitem a concessão de acessos sem a necessidade de automação.

Para construir scripts para automatizar a concessão e revogação destes acessos, o primeiro passo é saber quais são os usuários cadastrados no servidor.

1. Quais são os usuários cadastrados?

Para conceder ou revogar privilégios aos usuários, é interessante saber quantos e quais usuários estão cadastrados no seu SGBD, e uma consulta a PG_USER .

select * from pg_user;

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig

----------+----------+-------------+----------+-----------+----------+----------+-----------

postgres |       10 | t           | t        | t         | ******** |          |

gisuser  |    17141 | f           | f        | f         | ******** |          |

A próxima etapa é identificar as tabelas para as quais será concedido acesso.


2. Quais são as tabelas criadas no banco?



Uma consulta aos metadados de PG_TABLES retorna o nome das tabelas utilizadas. Observe que na consulta, selecionamos apenas as  tabelas do schema public, ignorando as tabelas de sistema.

pf=# select tablename from pg_tables where schemaname = 'public';
 tablename
-----------
 pfdet2011
 pf2011
 ns2011
 nsdet2011
 cliente
(5 registros)
3. Concedendo Acessos em Massa

Com o comando GRANT, posso conceder permissões de inclusão, alteração e exclusão nas tabelas do banco para um determinado usuário. Basta executar este select e utilizar o resultado da consulta como entrada para o postgresql:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' TO 

postgres'  from pg_tables where schemaname = 'public';
                           ?column?                           
---------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres
(5 registros)

Uma pequena alteração no script faz o produto cartesiano entre tabelas e usuários, gerando todas as combinações:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' TO ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                             comando                             
------------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO hacker ;
(20 registros)
4. Revogando permissões de acesso


Com o comando REVOKE, as permissões  para todos os usuários podem ser revogadas instantaneamente:



pf=# select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' FROM ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                               comando                              
---------------------------------------------------------------------
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM hacker ;
(20 registros)

5. Considerações Práticas


Como já foi mencionado neste post, a concessão de acesssos com GRANT e REVOKE raramente demanda alguma automação. Sintaxes poderosas e simples resolvem o problema sem maiores problemas, geralmente sendo executadas diretamente pelo DBA:

GRANT ALL ON DATABASE postgres TO hacker;

REVOKE ALL ON DATABASE postgres FROM hacker;


Este post é mais um exercício do que um exemplo prático, mas pode ser útil em situações em que se deseje maior controle.

Consulte as especificações dos comandos GRANT e REVOKE para ver a grande diversidade de opções disponíveis!

Bom Material sobre Otimização de Desempenho de Bancos de Dados PostgreSQL

Ajustes de performance são uma parte importante do trabalho dos DBAs. Este trabalho de conclusão de curso de Qiang Wang mostra diversas opções que podem ser empregadas para melhorar o desempenho do Postgresql.

O texto está em um inglês de fácil compreensão e as soluções sugeridas são bastante simples, o que torna o material bastante prático.

Pesquisas sobre PostgreSQL: Ambientes Escaláveis para SGBD em Software Livre

O Serpro está investindo em convênios para pesquisas sobre ambientes escaláveis implementados com o Postgresql.

O artigo de Flávio Gomes Lisboa, publicado na edição de maio de 2012 da revista Tema (p. 12 e 13), é uma boa referência de como o Governo e Universidades podem estabelecer parcerias para pesquisas avançadas envolvendo teoria, prática e  tecnologias livres.

Desenvolva suas Aplicações de Bancos Postgres com Wavemaker

 

Tela 1: Servidor do Wavemaker Online

 

 

Cansado de ter de programar as interfaces em Java e PHP? Ferramentas de desenvolvimento são importantes para adquirir maior produtividade e para se explorar os vários recursos dos bancos de dados. O Wavemaker é uma ferramenta de desenvolvimento que oferece bons recursos para criar e gerir aplicações web, minimizando o esforço de programação, e que apresenta plena compatibilidade com bancos de dados PostgreSQL!

É uma ferramenta livre com código aberto através de licença Apache. Neste post a preocupação não é mostrar em profundidade os recursos da ferramenta, nem criar um tutorial, mas sim apresentar as funcionalidades básicas.

Tela 2: Interface do Wavemaker

A instalação é relativamente simples, e o programa pode ser baixado em http://www.wavemaker.com. O wavemaker é compatível com windows, linux e macintosh.

Tela 3: Criação de Projeto no Wavemaker

O Wavemaker apresenta uma interface bastante simplificada e ao mesmo tempo prática, e as operações são todas feitas dentro do navegador web. Basta se selecionar um objeto para suas propriedades estarem disponibilizadas para edição à direita da tela. A interface de programação é WYSIWYG. É uma ferramenta cliente-servidor, o que exige os devidos cuidados com a segurança em rede.

Abaixo, alguns recursos associados ao PostgreSQL:

* Importar Database
Por meio do menu "Services/ Import database" é possível recuperar todas as informações em um banco já existente. A interface é intuitiva para quem tem alguma experiência de desenvolvimento.

Tela 4: Importar Database

Entre com os dados do banco de dados, teste a conexão utilizando a opção "Test connection" e acione a importação do banco de dados com o botão "Import".

As tabelas importadas aparecem à esquerda da tela, na pasta "Database Widgets". É possível utilizar estas tabelas para criar formulários CRUD, consultas e relatórios, entre outras possibilidades.

* Projetar Database
Acione a opção "Services/ Design Database" para criar suas bases de dados, tabelas e para estabelecer os relacionamentos entre as mesmas.

Tela 5: Projetar Database

Ao disparar esta opção, você define o nome do banco a ser criado e confirma. O banco aparecerá no menu à esquerda da tela.

Selecione o banco e na parte central da tela aparecerão as opções de criação das tabelas do seu banco. A interface realmente é bem agradável. Clique no ícone do disquete para salvar as tabelas que for desenvolvendo.

Tela 6: Criação de Tabela

* Consultar
O menu "Services/ Query" permite que se realize e salve consultas às tabelas.

Tela 7: Construção de Consultas

A ferramenta apresenta ainda grids, treeviews, charts para apreentação dos dados, entre outras funcionalidades. É possível definir o dataset de uma grid e indicar as colunas a serem mostradas, o que facilita muito o desenvolvimento.

Tela 8: Dados de Uma Tabela

* Pontos fortes:
- Boa interface
- Visual WYSIWYG
- Facilidade de instalação (segui o tutorial e não houve qualquer incidente)
- Código aberto com licença Apache
- Tutoriais no sítio da ferramenta
- A desenvolvedora foi adquirida recentemente pela VMWare, o que pode garantir mais recursos para a evolução desta ferramenta

* Pontos fracos
- Compatibilidade boa com Postgresql, mas não excepcional. Recursos específicos como herança de tabelas e indexação avançada não são abordados na ferramenta e tem de ser codificados manualmente no banco.
- A desenvolvedora foi adquirida recentemente pela VMWare, e o impacto desta mudança no desenvolvimento da ferramenta não pode ser previsto de antemão

* Avaliação Pessoal
A primeira impressão que me causou foi bastante positiva, mas não recomendo a utilização em ambientes de produção sem vários testes com prototipação e simulações de carga.

Questões de concurso sobre o PostgreSQL!

Este link mostra um site com questões de vários concursos no Brasil sobre o postgres coletadas desde 2007.

É interessante ver que esta tecnologia já está sendo utilizada em diversos órgãos órgãos como MPU, MEC, TJ, INFRAERO, EMBASA, entre outros, a ponto de fazer parte dos processos seletivos.

Acesse: Revista Internacional de PostgreSQL

A revista PostgreSQL Magazine tem sua primeira edição lançada. Minha primeira impressão foi bastante positiva!

Abaixo, uma visão geral dos conteúdos:

  - PostgreSQL 9.1 : 10 incríveis novos recursos
  - NoSQL : Implementação com Postgres
  - Entrevista : Stefan Kaltenbrunner
  - Opinião: Financiamento de Funcionalidades do PostgreSQL
  - A espera pela versão 9.2 : Cascading Streaming Replication
  - Dicas e Truques: PostgreSQL no Mac OS X Lion

O acesso está disponível em três formas:
  * Leitura online gratuita: http://pgmag.org/01/read
  * Pela aquisição da edição impressa: http://pgmag.org/01/buy
  * Versão em PDF: http://pgmag.org/01/download



A revista apresenta abertura para escritores, tradutores e outros interessados.

Espero que haja fôlego para a produção de mais revistas com este nível. Confira!

Faça Você Mesmo: Mapas Mentais Gerados Via SQL!

A criação de diagramas é um processo que exige paciência e bastante tempo. No entanto, se estes diagramas são de natureza hierárquica, fica mais fácil se pensar em um certo grau de automatização. Mas o que isso tem a ver com o postgresql? Este post mostra a geração automatizada de um diagrama hierárquico estilo mind map, através de uma consulta ao postgres!

Basicamente, este post apresenta duas utilidades práticas:
- A criação de mapas mentais sobre o banco de dados, importante para DBAs e útil para o gerenciamento dos bancos de dados;
- A geração de mapas mentais baseados nas informações contidas nos bancos de dados, o que pode ser uma alternativa relevante aos tradicionais relatórios tabulares.

Mapas mentais são diagramas que mostram uma hierarquia de conceitos, ideias ou objetos quaisquer. O Freemind é uma das ferramentas livres mais conhecidas para a construção intuitiva de mapas mentais, e será utilizado neste texto. A ferramenta permite a exportação de mapas mentais para vários formatos, pesquisas e várias outras funcionalidades.


Baixe-a e instale a partir deste site.

O formato interno do freemind é uma linguagem de marcação similar ao HTML ou ao XML, então é razoavelmente fácil criar diagramas através de consultas sql.

Os passos são os seguintes:

- Criar uma consulta que retorne o valor no formato do Freemind;
- Salvar o resultado da consulta em um arquivo .mm (mm de Mind Map);
- Abrir o diagrama gerado no Freemind e fazer as eventuais customizações.

A consulta abaixo realiza uma consulta aos metadados do postgresql e apresenta o resultado ao usuário (tive de salvar como imagem por ter caracteres não aceitos pelo Blogger):

Veja abaixo o resultado final obtido:

Agora é a sua vez! Tente executar a consulta no seu banco de dados, salve o resultado em um arquivo .mm e aprimore o script! 


Alguns desafios para você: 

- Aprimore o script deste post e compartilhe fazendo um comentário. Podes organizar de forma distinta, aninhar informações, acrescentar mais dados ou ainda melhorar o aspecto visual;
- Utilize mapas mentais na gestão dos seus BDs;
- Tente gerar diagramas não hierárquicos utilizando as setas. Não é tão fácil, mas você consegue;
- Produza relatórios no mundo real utilizando o Freemind e os dados gravados no postgresql.

Webcast: Por que PostgreSQL?

A 4Linux anunciou um Webcast voltado para o PostgreSQL. A inscrição é gratuita.

A exibição será dia 23 de abril de 2012 às 16h (horário de Brasília).

A programação e o acesso ao webcast podem ser consultados aqui.

Criação de Crosstabs no PostgreSQL

Você já criou uma tabela cruzada, ou crosstab, utilizando SQL? Você sabia que era possível? Possivelmente você nunca precisou, mas criar crosstabs é um recurso à disposição que pode ser bastante útil!

Tabelas cruzadas apresentam mais de uma dimensão de forma integrada aos usuários, sendo uma forma importante para a melhor visualização de dados consolidados. Geralmente o cruzamento de dados neste tipo de tabela é feito na camada de apresentação das aplicações ou em componentes geradores de relatórios, mas isso não impede que você também possa implementar crosstabs via banco de dados.

Vamos exemplificar a criação de crosstabs no postgresql utilizando as tabelas abaixo:

* Tabela Loja - Basicamente apresenta a descrição da loja

-- Tabela Loja
CREATE TABLE loja (codigo integer, nomeloja varchar(20), observacao varchar(250));
INSERT INTO loja VALUES (1, 'Loja 1', 'Matriz');
INSERT INTO loja VALUES (2, 'Loja 2', 'Filial 1');
INSERT INTO loja VALUES (3, 'Loja 3', 'Filial 2');
INSERT INTO loja VALUES (4, 'Loja 4', 'Filial 3');

* Tabela Vendas - Apresenta as vendas realizadas para cada mês em cada loja.

-- Tabela Vendas

CREATE TABLE vendas (seqvenda integer, codloja integer, mesvenda integer, valor integer);
INSERT INTO vendas VALUES (1, 1, 1, 100);
INSERT INTO vendas VALUES (2, 1, 2, 100);
INSERT INTO vendas VALUES (3, 1, 3, 100);
INSERT INTO vendas VALUES (4, 1, 1, 100);
INSERT INTO vendas VALUES (5, 2, 2, 100);
INSERT INTO vendas VALUES (6, 2, 3, 100);
INSERT INTO vendas VALUES (7, 2, 1, 100);
INSERT INTO vendas VALUES (8, 2, 2, 100);
INSERT INTO vendas VALUES (9, 4, 3, 100);
INSERT INTO vendas VALUES (10, 4, 1, 100);
INSERT INTO vendas VALUES (11, 4, 2, 100);
INSERT INTO vendas VALUES (12, 4, 3, 100);

Vamos fazer consultas para mostrar as vendas com base em duas dimensões: a loja que fez a vendas e o período (mês) das vendas.

Em primeiro lugar, vamos colocar como colunas as lojas da tabela pai (loja), e como linhas as vendas da tabela filha, agrupando os dados por mês. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for da loja a ser apresentada na coluna.

-- CROSSTAB
-- COLUNAS - LOJAS
-- LINHAS - Vendas em cada Mês
SELECT
(CASE vendas.mesvenda WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEV' WHEN 3 THEN 'MAR' ELSE 'ERRO' END) AS MES,
SUM (CASE vendas.codloja WHEN 1 THEN vendas.valor ELSE 0 END) AS MATRIZ,
SUM (CASE vendas.codloja WHEN 2 THEN vendas.valor ELSE 0 END) AS FILIAL_1,
SUM (CASE vendas.codloja WHEN 3 THEN vendas.valor ELSE 0 END) AS FILIAL_2,
SUM (CASE vendas.codloja WHEN 4 THEN vendas.valor ELSE 0 END) AS FILIAL_3
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY vendas.mesvenda
ORDER BY vendas.mesvenda;

O resultado da consulta mostra inclusive a filial 2, que esteve fechada durante o período:

 mes | matriz | filial_1 | filial_2 | filial_3
-----+--------+----------+----------+----------
 JAN |    200 |      100 |        0 |      100
 FEV |    100 |      200 |        0 |      100
 MAR |    100 |      100 |        0 |      200
(3 registros)

Agora vamos fazer uma inversão: vamos colocar como colunas as lojas da tabela filha, vendas, e como linhas as informações da tabela pai, loja, com os dados agrupados por loja. A cláusula CASE é importante para atribuir o valor 0 (zero) quando a venda não for do mês a ser apresentado na coluna.

-- CROSSTAB 2
-- COLUNAS - Vendas em cada Mês
-- LINHAS - LOJAS
SELECT
(CASE loja.codigo WHEN 1 THEN 'MATRIZ' WHEN 2 THEN 'FILIAL 1' WHEN 3 THEN 'FILIAL 2' WHEN 4 THEN 'FILIAL 3' ELSE 'ERRO' END) AS LOJA,
SUM (CASE vendas.mesvenda WHEN 1 THEN vendas.valor ELSE 0 END) AS JAN,
SUM (CASE vendas.mesvenda WHEN 2 THEN vendas.valor ELSE 0 END) AS FEV,
SUM (CASE vendas.mesvenda WHEN 3 THEN vendas.valor ELSE 0 END) AS MAR
FROM loja
INNER JOIN vendas
ON loja.codigo = vendas.codloja
GROUP BY loja.codigo
ORDER BY LOJA;

Resultado da consulta:

   loja   | jan | fev | mar
----------+-----+-----+-----
 FILIAL 1 | 100 | 200 | 100
 FILIAL 3 | 100 | 100 | 200
 MATRIZ   | 200 | 100 | 100
(3 registros)

O exemplo acima pode ser incrementado e melhorado de várias formas (por exemplo, as tabelas não têm índices). Se você tem alguma sugestão ou forma alternativa de fazer crosstabs ou de melhorar os exemplos abaixo, não deixe de postar um comentário neste post!

Obs.: Existe um módulo do postgresql já bastante estável, chamado tablefunc que apresenta funções para a criação de tabelas cruzadas. É uma forma de criar crosstabs com menos trabalho, mas também com menos diversão e portabilidade! Mais informações sobre o tablefunc aqui.