Você REALMENTE Sabe Lidar com Valores Nulos no PostgreSQL?

O título desta postagem é uma pergunta que me fiz. Eu achava que sim, mas ao fazer uns testes e pesquisas, vi que o assunto é mais amplo do que eu pensava inicialmente. Convido o leitor a explorar todas as principais variações  do tratamento de valores nulos no postgresql.

* Primeiro ponto: Nulo não é zero, nem '', nem espaço.

Nulo significa nulo, que o valor não foi preenchido. Vejamos o exemplo abaixo:

teste=# SELECT (null = '') IS TRUE AS COMPARA1, (null = ' ') IS TRUE AS COMPARA2, (null = 0) IS TRUE AS COMPARA3;
 compara1 | compara2 | compara3
----------+----------+----------
 f        | f        | f
(1 registro)

* Saiba testar se um valor é nulo ou não (IN NULL e IS NOT NULL)

O exemplo abaixo utiliza IS NULL e IS NOT NULL:

teste=# SELECT (1 IS NULL) AS COMPARA1, (1 IS NOT NULL) AS COMPARA2;
 compara1 | compara2
----------+----------
 f        | t
(1 registro)

* Saiba que valores nulos retornam nulo em comparações (IN, NOT IN, =, !=, etc.)

Esta é uma decorrência do padrão SQL.

teste=# SELECT null NOT IN (1,2,3), null IN (1,2,3);
 ?column? | ?column?
----------+----------
          |
(1 registro)

teste=# SELECT 1 NOT IN (null), 1 IN (null);
 ?column? | ?column?
----------+----------
          |
(1 registro)

teste=# SELECT 1 != null, 1 = null;
 ?column? | ?column?
----------+----------
          |
(1 registro)

* Criar tabelas que aceitem e rejeitem campos nulos é importante!

teste=# CREATE TABLE TSTNULL (campo1 INTEGER NOT NULL, campo2 INTEGER NULL, campo3 INTEGER);
CREATE TABLE

* Como manipular valores nulos e não nulos nas tabelas criadas?

Use o valor null para deixar o campo desejado sem valor algum. Caso se utilize DEFAULT, o campo assume valor nulo se não há um valor default definido.

teste=# INSERT INTO TSTNULL VALUES (1, null, DEFAULT); --O DEFAULT é NULO, CASO NÃO FORNECIDO
INSERT 0 1
teste=# INSERT INTO TSTNULL VALUES (null, 1, 1);
ERRO:  valor nulo na coluna "campo1" viola a restrição não-nula
 

teste=#
teste=# SELECT * FROM TSTNULL;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

* Entenda que campos UNIQUE aceitam vários valores nulos.

teste=# CREATE TABLE TSTNULL2 (campo1 INTEGER UNIQUE);
NOTA:  CREATE TABLE / UNIQUE criará índice implícito "tstnull2_campo1_key" na tabela "tstnull2"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL2 VALUES (1);
INSERT 0 1
teste=# INSERT INTO TSTNULL2 VALUES (NULL);
INSERT 0 1
teste=# INSERT INTO TSTNULL2 VALUES (NULL); --NAO GERA ERRO
INSERT 0 1
teste=#
teste=# SELECT * FROM TSTNULL2;
 campo1
--------
      1
      
      
(3 registros)

* Campos PRIMARY KEY não aceitam nulos por definição. Você sabia?

teste=# --CAMPOS PRIMARY KEY NÃO ACEITAM NULOS
teste=# CREATE TABLE TSTNULL3 (campo1 INTEGER PRIMARY KEY);
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "tstnull3_pkey" na tabela "tstnull3"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL3 VALUES (1);
INSERT 0 1
teste=# INSERT INTO TSTNULL3 VALUES (NULL); --ERRO
ERRO:  valor nulo na coluna "campo1" viola a restrição não-nula
teste=#  

* Campos PRIMARY KEY com mais de um campo também não aceitam nulos.

teste=# CREATE TABLE TSTNULL4 (campo1 INTEGER, campo2 INTEGER,
teste(# CONSTRAINT PK_TESTNULL4 PRIMARY KEY (campo1, campo2));
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "pk_testnull4" na tabela "tstnull4"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL4 VALUES (1, 1);
INSERT 0 1
teste=# INSERT INTO TSTNULL4 VALUES (1, NULL); --ERRO
ERRO:  valor nulo na coluna "campo2" viola a restrição não-nula
teste=#

* Consultas podem ordenar os valores nulos, colocando-os na frente ou atrás do resultado da consulta.

Para obter este efeito, empregue as cláusulas NULLS FIRST e NULLS LAST. O padrão é colocar os valores nulos por último no retorno da consulta.

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO2 NULLS FIRST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO2 NULLS LAST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO1 NULLS FIRST, CAMPO2 NULLS LAST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

* A indexação leva em conta campos nulos. Você sabia? 

Para influenciar  este mecanismo, utilize as cláusulas NULLS FIRST e NULLS LAST.

teste=# CREATE INDEX idx_campo1_first ON TSTNULL (campo1 NULLS FIRST);
CREATE INDEX

teste=# CREATE INDEX idx_campo1_last  ON TSTNULL (campo1 NULLS LAST);
CREATE INDEX

teste=# CREATE INDEX idx_campo1_2 ON TSTNULL (campo1 NULLS LAST, campo2 NULLS FIRST);
CREATE INDEX

* Troque os nulos por um valor padrão usando o comando COPY.

Tanto no COPY FROM quanto no COPY TO, você pode substituir os nulos por um valor mais palatável, utilizando a cláusula NULL. Não deixe de indicar o valor que substituirá os nulos.


teste=# COPY TSTNULL TO 'tstnulos.txt' NULL 'NULO' CSV;
teste=# COPY TSTNULL FROM 'tstnulos.txt' NULL 'NULO' CSV;

* É possível "Forçar" valores importados para valores not null, no comando COPY FROM de arquivos CSV. 

Basta utilizar a cláusula cláusula FORCE_NOT_NULL, a partir da versão 9.0 do postgresql.Esta cláusula transforma valores nulos em strings de tamanho zero.

teste=# COPY TSTNULL FROM 'tstnulos.txt' (format csv, FORCE_NOT_NULL(campo1)); -- VALORES NULOS DE CAMPO1 SÃO LIDOS COMO STRINGS DE TAMANHO ZERO, NÃO COMO NULOS

* Você pode tratar parâmetros nulos nas funções com as cláusulas "CALLED ON NULL INPUT", "RETURNS NULL ON NULL INPUT" ou "STRICT".

Quando desejar aceitar tratar parâmetros com valor nulo em uma função, utilize a cláusula "CALLED ON NULL INPUT". O postgresql não vai retornar erro ou fazer qualquer tratamento sobre o parâmetro, passando esta responsabilidade ao programador da função.

O uso de "RETURNS NULL ON NULL INPUT" ou "STRICT" faz com que o retorno de uma função com algum parâmetro nulo, seja sempre null.

O teste é bem simples. Abaixo, coloco alguns exemplos:

CREATE OR REPLACE FUNCTION soma_nulo_1(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql CALLED ON NULL INPUT;
SELECT soma_nulo_1(1,2);
SELECT soma_nulo_1(null,2);

CREATE OR REPLACE FUNCTION soma_nulo_2(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql RETURNS NULL ON NULL INPUT;
SELECT soma_nulo_2(1,2);
SELECT soma_nulo_2(null,2);

CREATE OR REPLACE FUNCTION soma_nulo_3(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql STRICT;
SELECT soma_nulo_3(1,2);
SELECT soma_nulo_3(null,2);

* Considerações Finais

Tratar valores nulos é de vital importância para evitar inconsistências. O postgresql oferece várias opções para lidar com este tipo de ocorrência.

Reaproveite Senhas de Atendimento com o PostgreSQL

Voltei a ensinar a disciplina de banco de dados e uma dúvida inteligente de um aluno aplicado ajudou a animar o final da última aula. Respondi à dúvida e aparentemente ele ficou satisfeito, mas aproveitei a ideia dele para compor este post. O problema a ser resolvido é manter uma lista de códigos com valores entre 1 e N para serem utilizados por um sistema ou aplicação. Os códigos são reaproveitados, isto é, caso um código não esteja mais disponível, pode ser reutilizado, como por exemplo, no caso de uma senha para atendimento em uma fila. Como manter e tratar este problema com os recursos do banco de dados postgres?

Em primeiro lugar, deixo para os comentários dos interessados maiores discussões sobre as possíveis alternativas. Apresentarei uma solução simples que utiliza triggers, mas espero que a mesma possa ser melhorada e questionada por você, caro leitor.

* Solução de lista de códigos

Permite o controle da utilização e reutilização de códigos através de uma lista com dois campos, um sendo o primeiro o valor do código e outro a indicação de sua utilização ou não.
A lista é armazenada em uma tabela e inicializada na sua criação. Esta lista é atualizada por uma trigger na tabela "usuária" dos códigos. O gatilho atualiza a tabela de código em dois momentos: na utilização de um código e na liberação do código para reuso.

* Tabela de Lista de Códigos

Abaixo, coloco o script da nossa lista de códigos. Coloquei a cláusula UNIQUE para o campo de código para evitar repetições que violassem a integridade da lista e ao tempo gerar um índice implícido para o campo de código, a ser utilizado nas consultas. Vamos inicializar a nossa lista com 10 códigos, inteiros com valor entre 1 e 10, com o valor false, equivalente a "desalocado".

CREATE TABLE lista_codigos (codigo integer UNIQUE, flag_utilizado boolean);
INSERT INTO lista_codigos (codigo, flag_utilizado) VALUES (1,false), (2,false), (3,false), (4,false), (5,false), (6,false), (7,false), (8,false), (9,false), (10,false); --INICIALIZACAO
* Tabela Usuária de Códigos

A tabela em que serão armazenados os códigos utilizados apresentará dois gatilhos, sendo um em caso de inserção de código, e outra para exclusão de código. Adicionei também uma restrição UNIQUE no campo código para não ter de validar tentativas de utilizar o código mais de uma vez.

CREATE TABLE usa_codigos (codigo integer UNIQUE);
Abaixo, os códigos das trigers e trigger functions associadas.

CREATE OR REPLACE FUNCTION pegar_codigo() RETURNS trigger AS $pegar_codigo$
BEGIN
    UPDATE lista_codigos SET flag_utilizado = true WHERE codigo = new.codigo;
    RETURN new;
END;
$pegar_codigo$ LANGUAGE plpgsql;

CREATE TRIGGER pega_codigo BEFORE INSERT ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE pegar_codigo ();

CREATE OR REPLACE FUNCTION liberar_codigo() RETURNS trigger AS $liberar_codigo$
BEGIN
    UPDATE lista_codigos SET flag_utilizado = false WHERE lista_codigos.codigo = old.codigo;
    RETURN old;
END;
$liberar_codigo$ LANGUAGE plpgsql;

CREATE TRIGGER libera_codigo AFTER DELETE ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE liberar_codigo ();

* Testes da Lista

CENÁRIO 1: Reservando códigos. Neste cenário, basta inserir um registro na tabela usa_codigos e consultar a tabela lista_codigos para verificar se foram reservados os códigos.

INSERT INTO usa_codigos (codigo) VALUES (1), (3), (4), (9), (10);

O resultado obtido está correto.

SELECT codigo, CASE
     WHEN flag_utilizado = true THEN 'SIM'
     ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | SIM
      4 | SIM
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | SIM
     10 | SIM
(10 registros)
CENÁRIO 2: Liberando códigos. Inicialmente, libero o código 3. A liberação é a exclusão da tabela de códigos.

DELETE FROM usa_codigos WHERE codigo = 3;
O resultado obtido está de acordo com o desejado.

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | SIM
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | SIM
     10 | SIM
(10 registros)

CENÁRIO 3: Liberando todos os códigos de uma só vez. O resultado corresponde à expectativa.

DELETE FROM usa_codigos;
 codigo | reservado
--------+-----------
      1 | NÃO
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)

CENÁRIO 4: Testando reservar código já reservado. A restrição CHECK garante a integridade e o código é reservado, como esperado.

postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
INSERT 0 1
postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
ERRO:  duplicar valor da chave viola a restrição de unicidade "usa_codigos_codigo_key"
DETALHE:  Chave (codigo)=(1) já existe.

 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)
CENÁRIO 5: Testando liberar código já liberado. Não ocorre erro e a alocação dos códigos permanece inalterada. Ao menos neste teste inicial, as operações foram realizadas a contento!

postgres=# DELETE FROM usa_codigos WHERE codigo = 3;
DELETE 0
postgres=# SELECT codigo, CASE
     WHEN flag_utilizado = true THEN 'SIM'
     ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;
 codigo | reservado
--------+-----------
      1 | SIM
      2 | NÃO
      3 | NÃO
      4 | NÃO
      5 | NÃO
      6 | NÃO
      7 | NÃO
      8 | NÃO
      9 | NÃO
     10 | NÃO
(10 registros)

* Adicionando Funcionalidades

Podem ser implementadas consultas ou funções que ajudem a emitir as senhas e liberar as senhas para reaproveitamento, além de consultas básicas para:

-  Consultar o primeiro código disponível

-  Consultar o último código disponível
-  Consultar a quantidade de códigos disponíveis
-  Consultar a quantidade de códigos não disponíveis

Mas deixo aos leitores estas tarefas como exercício.

* Notas conclusivas

- Uma trigger ou restrição de acesso pode evitar que se utilize a cláusula UPDATE para a tabela de códigos, ou tratar o update, o que eu acho mais trabalhoso!
- Deixo para os leitores a tarefa de implementar esta solução utilizando arrays, e empregando outras formas alternativas e talvez mais eficientes.

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!

Geração de CPFs Fictícios com Pl/ PgSQL

A geração de massas de dados para teste é um processo muito importante, para verificar o comportamento de um banco de dados em relação à carga de processamento que o mesmo tem de desempenhar. A geração de CPFs fictícios é uma atividade relativamente comum em empresas brasileiras que armazenam este tipo de identificador. No entanto muitas vezes os testes são feitos com valores incorretos, isto é, que não respeitam as regras para os dois dígitos verificadores.

Abaixo está um código que se propõe a retornar CPFs aleatórios com dígitos verificadores corretos, implementado em Pl/ PgSQL.

Os comandos mais importantes deste código são:
- Random() - Geração de números aleatórios
- substring() - Extração de parte de uma string com base nso parâmetros fornecidos
- CAST () - Conversão de tipos no PostgreSQL
- trim() - Eliminação de espaços em branco de strings

Os testes foram muito positivos. Coloco para você, leitor, as seguintes perguntas:
- Este código segue um algoritmo correto?
- Ele pode ser melhorado? De que forma?

CREATE OR REPLACE FUNCTION gerar_CPF() RETURNS varchar AS $$
-- ROTINA DE GERAÇÃO DE CPF SEM LOOP
-- Retorna string com CPF aletório correto.
DECLARE
vet_cpf integer [11]; --Recebe o CPF
soma integer; -- Soma utilizada para o cálculo do DV
rest integer; -- Resto da divisão
BEGIN
-- Atribuição dos valores do Vetor
vet_cpf[0] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[1] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[2] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[3] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[4] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[5] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[6] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[7] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);
vet_cpf[8] := cast(substring (CAST (random() AS VARCHAR), 3,1) as integer);

-- CÁLCULO DO PRIMEIRO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 10, 9, 8 e assim por diante...
soma:=(vet_cpf[0]*10)+(vet_cpf[1]*9)+(vet_cpf[2]*8)+(vet_cpf[3]*7)+(vet_cpf[4]*6)+(vet_cpf[5]*5)+(vet_cpf[6]*4)+(vet_cpf[7]*3)+(vet_cpf[8]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
vet_cpf[9]:=0;
ELSE vet_cpf[9]:=(11-rest); END IF;
-- CÁLCULO DO SEGUNDO NÚMERO DO DV
-- Soma dos nove primeiros multiplicados por 11, 10, 9 e assim por diante...
soma:= (vet_cpf[0]*11) + (vet_cpf[1]*10) + (vet_cpf[2]*9) + (vet_cpf[3]*8) + (vet_cpf[4]*7) + (vet_cpf[5]*6) + (vet_cpf[6]*5) + (vet_cpf[7]*4) + (vet_cpf[8]*3) + (vet_cpf[9]*2);
rest:=soma % 11;
if (rest = 0) or (rest = 1) THEN
vet_cpf[10] := 0;
ELSE
vet_cpf[10] := (11-rest);
END IF;
--Retorno do CPF
RETURN trim(trim(to_char(vet_cpf[0],'9')) || trim(to_char(vet_cpf[1],'9')) || trim(to_char(vet_cpf[2],'9')) || trim(to_char(vet_cpf[3],'9')) || trim(to_char(vet_cpf[4],'9')) || trim(to_char(vet_cpf[5],'9')) || trim(to_char(vet_cpf[6],'9')) || trim(to_char(vet_cpf[7],'9'))|| trim(to_char(vet_cpf[8],'9')) || trim(to_char(vet_cpf[9],'9')) || trim(to_char(vet_cpf[10],'9')));
END;
$$ LANGUAGE PLPGSQL;

Chamada da função, retornando um CPF aleatório.

select gerar_CPF() ;

A função para fazer o teste está neste post. O resultado para um CPF correto é 1.

select CPF_Validar_Sem_Loop(gerar_CPF());
select CPF_Validar_Sem_Loop('66067526557'); --Gerado pelo programa

Exemplos de Código e SQL e PgSQL

O site abaixo apresenta uma série de bons exemplos (e simples de reproduzir) de código SQL e PlPgSQL. São abordados assuntos como funções matemáticas, de datas, índices, tabelas, consultas, cursores, tipos de dados, entre outros recursos do banco. Boa sugestão para a lista de favoritos de quem trabalha com o PostgreSQL:

http://www.java2s.com/Code/PostgreSQL/CatalogPostgreSQL.htm

DBLINK: Consultas, Comandos SQL, Transações e Funções

A realização de consultas e a execução de comandos SQL são as principais atividades realizadas com o DBLINK. Continuando os comentários iniciados no post anterior, este tópico apresenta exemplos de aplicação desta tecnologia do PostgreSQL.

As rotinas dblink e dblink_exec permitem a realização de consultas, inserção, alteração e exclusão de dados em bancos distintos. Podem ser utilizadas dentro de uma transação ou de uma função.

* Consultas

As consultas são feitas com a rotina dblink e seguem uma sintaxe diferente do tradicional. Pode ser criada uma conexão para a execução da consulta ou pode ser usada uma que já tenha sido criada anbteriormente.

A rotina dblink tem dois parâmetros, sendo o primeiro a conexão e o segundo o comando a ser executado. Além dos parâmetros, o dblink pede que se liste os campo e seus tipos de dados.

O exemplo abaixo mostra uma consulta que utiliza a conexão "myconn", criada pela função dblink_connect:

--Consulta em banco de dados de estoque de dentro do banco de dados de venda usando conexão criada
SELECT estoque_prod.* FROM dblink('myconn', 'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);

O exemplo abaixo mostra uma consulta que apresenta como parâmetro uma string para conexão:

--Consulta em banco de dados de estoque de dentro do banco de dados de venda com conexão dentro do comando
SELECT * FROM dblink('hostaddr=10.200.239.55 port=5432 dbname=estoque user=postgres password=post',
'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);


* Inserção, Alteração e Exclusão de Dados

Estas operações são implementadas por meio da rotina dblink_exec. Como parâmetros são fornecidos a conexão e o comando a ser executado:

--Inserção de registros com DBLINK_EXEC
--Detalhe: uso das aspas simples. As mesmas devem estar duplicadas.
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (6, ''Relogio'', 400, 110.90);');

--Atualização de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - 1 WHERE cod_prod = 6;');

--Exclusão de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'DELETE FROM ESTOQUE_ITEM WHERE cod_prod = 6;');

* Dblink Dentro de uma Transação

Comandos do dblink funcionam normalmente dentro de transações.

Abaixo, um exemplo de transação de venda, sensibilidando dois bancos de dados distintos:

BEGIN;
INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (1, 1000 , DEFAULT);
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (5, ''Relogio'', 400, 110.90);');
END;

* Uso do Dblink Dentro de Funções

O dblink pode ser livremente utilizado dentro de funções. Pode ainda apresentar parametrização e a chamada à função pode estar inscrita dentro de uma transação maior.

Abaixo, um exemplo com dois parâmetros, simulando uma transação de compra e venda:

-- Uso de dblink DENTRO de uma função
CREATE OR REPLACE FUNCTION func_venda(cod_prd integer, quant integer) RETURNS INTEGER AS $$
DECLARE
comando_venda TEXT;
comando_estoque TEXT;
BEGIN
comando_venda := 'INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (' || $1 || ',' || $2 || ', DEFAULT);';
EXECUTE comando_venda;
comando_estoque := 'SELECT dblink_exec(''myconn'', ''UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - ' || $2 || ' WHERE cod_prod = ' || $1 || ''');';
EXECUTE comando_estoque;
RETURN 1; --Retorno com sucesso
END;
$$ LANGUAGE plpgsql;

SELECT func_venda(1,1);

Formatação de CPF - Uma função simples

O CPF, número do Cadastro de Pessoa Física, apresenta o formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador. Uma vez que o mesmo é bastante utilizado, sua validação e apresentação são opções que podem ser implementadas dentro do banco de dados , com reuso do mesmo código para uso em vários sistemas.

Neste post vamos fazer uma função simplificada para formatação e apresentação de CPF.

A consulta abaixo faz a formatação de um CPF. Apresenta a desvantagem de se ter de repetir várias vezes o valor do CPF a ser apresentado.

SELECT substring('12345678912' FROM 1 FOR 3) || '.' || substring('12345678912' FROM 4 FOR 3) || '.' || substring('12345678912' FROM 7 FOR 3) || '-' || substring('12345678912' FROM 10 FOR 2);

O uso de uma função aumenta a simplicidade e facilita o reuso:

CREATE OR REPLACE FUNCTION CPF_formatar(par_cpf varchar(11)) RETURNS varchar(14) AS $$
-- ROTINA DE FORMATAÇÃO DE CPF
-- Código PL/ PGSQL: Cláudio Leopoldino - http://postgresqlbr.blogspot.com/
-- Retorna o CPF formatado no formato NNN.NNN.NNN-DD, onde N é número do CPF e D é dígito verificador
-- em caso de erro retorna 'ERRO'
BEGIN
IF char_length(par_cpf) != 11 THEN
RAISE NOTICE 'Formato inválido: %',$1;
RETURN 'ERRO';
END IF;
RETURN substring(par_cpf FROM 1 FOR 3) || '.' || substring(par_cpf FROM 4 FOR 3) || '.' || substring(par_cpf FROM 7 FOR 3) || '-' || substring(par_cpf FROM 10 FOR 2);
END;
$$ LANGUAGE PLPGSQL;

SELECT CPF_formatar('12345678912');

Uso de SET em Funções Pl/PgSQL

Na versão 8.3 do PostgreSQL foi implementado um recurso interessante, que permite a parametrização da execução de uma função utilizando o comando SET no momento da sua criação. Desta forma, a configuração é alterada apenas durante a sua execução, retornando aos valores prévios após o seu encerramento.

Valores de tablespace padrão (default_tablespace), encoding do cliente (client_encoding) entre outros são definidos de forma explícita, de modo que se force o comportamento esperado da função não importando que programa faça a sua chamada de execução.

Existe também a opção de utilização da cláusula FROM CURRENT para fazer com que a alteração de configuração seja mantida após a execução da função.

Nem todos os parâmetros de execução podem ser alterados com o comando SET dentro de uma função. Os erros são revelados no momento de criação da função.

O comando SHOW mostra a lista de parâmetros e seus valores correntes.

Criação de Função com Parâmetros de Configuração

O código abaixo mostra a criação de uma função que atualiza a tabela salário. Após o corpo da função são definidos os valores dos itens de configuração array_nulls, default_tablespace, commit_delay e client_encoding.

CREATE TABLE SALARIO (cod integer PRIMARY KEY, provento real);
INSERT INTO SALARIO VALUES (1, 1000.00);
INSERT INTO SALARIO VALUES (2, 100.00);

CREATE OR REPLACE FUNCTION salario_aumento() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET array_nulls = OFF
SET default_tablespace = 'pg_default'
SET commit_delay TO 10
SET client_encoding TO UNICODE;

Mantendo Alterações na Configuração

O código abaixo mostra a criação de uma função na qual é alterado e mantido o valor do client_encoding. A cláusula FROM CURRENT indica que parâmetro terá o valor de configuração que foi alterado mantido.

CREATE OR REPLACE FUNCTION salario_parametros() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET client_encoding TO UNICODE
SET client_encoding FROM CURRENT;

Ressalva no Uso de SET em Funções

Alguns dos itens de configuração não são alteráveis pelo comando SET dentro de uma função. Neste caso, será retornado erro no ato da criação da função. No exemplo abaixo, tanto o log_checkpoints quanto o transaction_isolation não puderam ser alterados no escopo de uma função.

-- Não funcionaram
CREATE OR REPLACE FUNCTION salario_parametros_teste() RETURNS BOOLEAN AS $
BEGIN
UPDATE SALARIO SET provento = provento * 1.1;
RETURN true;
END;
$ LANGUAGE plpgsql
SET log_checkpoints = ON
SET transaction_isolation = SERIALIZABLE;

ERROR: parameter "log_checkpoints" cannot be changed now

********** Error **********

ERROR: parameter "log_checkpoints" cannot be changed now
SQL state: 55P02

RETURN QUERY - Novo recurso do PostgreSQL 8.3

A cláusula RETURN QUERY permite que o programador faça uma função que retorne um conjunto de linhas. Foi acrescentada no PostgreSQL 8.3, permitindo maior versatilidade nas implementações.

Para ilustrar esta funcionalidade, vamos criar e popular uma tabela:

CREATE TABLE FUNCTESTE (
cod serial primary key, nome varchar(50), aniversario date default now());
INSERT INTO FUNCTESTE VALUES (1, 'Cláudio', DEFAULT);
...
INSERT INTO FUNCTESTE VALUES (10, 'Ana Cláudia', '01/01/2008');


Vamos retornar um conjunto de linhas utilizando a cláusula RETURN QUERY dentro de uma função. Observe que o código da função abaixo retorna um conjunto de linhas (SETOF) que tem de ser iguais aos campos da tabela FUNCTESTE:

--Retornando consulta de várias linhas com FOR
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF FUNCTESTE AS $$
BEGIN
RETURN QUERY SELECT * FROM FUNCTESTE; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;

Para testar a função:

select * from ret_rows();

Em uma função, o RETURN QUERY pode ser utilizado mais de uma vez, mas o retorno feito com RETURN; ´faz a descarga dos valores de uma vez só.

--Retornando a mesma consulta várias vezes com WHILE
CREATE OR REPLACE FUNCTION ret_rows_while () RETURNS SETOF FUNCTESTE AS $$
DECLARE
i INTEGER :=1;
BEGIN
WHILE i <= 5 LOOP
RETURN query SELECT * FROM FUNCTESTE LIMIT 1; --Consulta a ser repetida
i:= i + 1;
END LOOP;
RETURN ;
END;
$$ LANGUAGE plpgsql;

Pode ser retornado um conjunto de elementos de qualquer valor.

--Retornando consulta de vários resultados do tipo inteiro
CREATE OR REPLACE FUNCTION ret_rows_int () RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY SELECT cod FROM FUNCTESTE; --Consulta
RETURN ; -- Retorno de dados
END;
$$ LANGUAGE plpgsql;

select * from ret_rows_int();

A cláusula complementa a função do RETURN NEXT, que era a única possibilidade implementada nas versões anteriores. A sintaxe em geral é mais simples.

Criação de Índices com Procedures no PostgreSQL...

Vamos ver se consigo ajudar. Esta é uma dúvida do Juliano Fischer:

"Ola, estou com um problema em uma implementação e creio que pode me ajudar.
Preciso criar um subprograma armazenado que crie um indice,se já existir, informar o usuario, cado contratio, crie um indice.

criaIndice(campo,tabela);"

Não é tão difícil! A solução está abaixo, mas cuidado pois o meu código pode e deve ser melhorado. Nomes de parâmetros, métodos e variáveis e bons comentários podem ser adicionados.

Além disso, o exemplo só verifica um campo e exige que se tenha cuidado na nomenclatura dos campos, pois utiliza a cláusula LIKE.

1 - Recuperar informações dos índices.

A dica é utilizar a pg_indexes, visão de sistema mais amigável para saber se o índice existe:

select * from pg_indexes;

2 - Criar uma função parametrizada que indica se os índices existem. Observem que utilizei a linguagem SQL, e não Pl/ PgSql. Poderia utilizar qualquer outra liguagem, mas a lógica seria similar.

-- Verifica se existe o índice. Retorna ZERO se o índice não for encontrado
CREATE OR REPLACE FUNCTION retindex(in tabela varchar, in campo varchar) RETURNS bigint AS $$
select count(*) from pg_indexes where tablename = $1 and indexdef like '%' || $2 || '%'
$$
LANGUAGE SQL;


3 - Criar função parametrizada que cria o índice caso o mesmo não exista, e que utilize a função anterior. Linguagem Pl/ PgSQL.

-- Verifica e cria novo índice se for o caso
CREATE OR REPLACE FUNCTION criaindex (tabela varchar, campo varchar) RETURNS VARCHAR AS $$
DECLARE
func_cmd VARCHAR;
BEGIN
if retindex($1,$2) > 0 then
RETURN 'OK';
else
func_cmd := 'CREATE INDEX ' || $1 || '_IDX ON ' || $1 || ' (' || $2 || ')';
EXECUTE func_cmd;
RETURN func_cmd;
end if;
END;
$$ LANGUAGE plpgsql;

4 - Testando tudo

select retindex ('teste','c1');
select criaindex ('teste', 'c1');

Companheiros, quem pode dar um código que melhore ainda mais esta criação de índices com função armazenada? Juliano, resolvi seu problema?