Utilização de Functions no PostgreSQL

O PostgreSQL é um banco de dados com uma série de instruções sendo apresentada como Functions. Estas funções adicionam a capacidade de controlar a execução das instruções SQL através da utilização de uma linguagem procedural (PL). No PostgreSQL podemos informar para qual linguagem estamos escrevendo nossas funções, como é o caso de utilizarmos PL/R (para a linguagem R – linguagem voltado para gráficos), PL/Java, e assim por diante.

Relacionado: Conceitos Fundamentais de Banco de Dados

Para um melhor entendimento, vejamos de acordo com a Listagem 1 a apresentação da sintaxe básica de criação de uma Function.

CREATE [ OR REPLACE ] FUNCTION
   nome ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } 
   default_expr ] [, ...] ] )
   [ RETURNS rettype
     | RETURNS TABLE ( column_nome column_type [, ...] ) ]
 { LANGUAGE lang_nome
   | WINDOW
   | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | COST execution_cost
   | ROWS result_rows
   | SET configuration_parameter { TO value | = value | FROM CURRENT }
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
 } ...
[ WITH ( attribute [, ...] ) ]

A instrução CREATE FUNCTION define uma nova função, enquanto a instrução CREATE OR REPLACE FUNCTION criar uma nova função ou substitui uma função já existente. Além disso, temos a definição do nome do esquema, onde caso este seja incluído, a função será criada no esquema que foi especificado. Devemos tomar cuidado na hora de atribuir o nome da nossa função, pois ela não deve corresponder a nenhuma função já existente com os mesmos tipos de argumentos de entrada presentes no mesmo esquema. O que difere as funções são os argumentos de tipos diferentes, pois estes podem compartilhar um mesmo nome, o que o torna uma função de sobrecarga. Quando o CREATE OR REPLACE FUNCTION é utilizado para substituir uma função existente, a propriedade e as permissões da função não mudam. Todas as outras propriedades de função são atribuídas aos valores especificados ou implícitos no comando.

Alguns dos atributos que temos na nossa sintaxe são:

• o “nome”, que seria o nome do esquema a ser criado;
• o “argmode”, que é o modo de um argumento, sendo este dos tipos IN ou OUT. Caso este argumento seja omitido, por padrão, teremos definido o IN;
• o “argname”, que é o nome de um argumento de sua importância, que define o nome do tipo de coluna na linha do resultado.
• o tipo_retornado, que pode ser uma base, um tipo de domínio, ou mesmo fazer referência ao tipo de uma coluna da tabela.

Quando temos especificado um parâmetro OUT ou INOUT, a cláusula RETURNS pode ser omitida. A cláusula WINDOW indica que a função é uma função de “janela”, ao invés de ser uma função simples.

Com relação aos atributos Immutable, Stable, Volatile, estes informam a otimização de consultas sobre o comportamento da função. Por padrão temos já definido o VOLATILE.

O atributo IMMUTABLE indica que a função não pode modificar o banco de dados e sempre retorna o mesmo resultado quando recebe os mesmos valores de argumentos.

O atributo STABLE indica que a função não pode modificar o banco de dados, e dentro de uma única varredura da tabela irá consistentemente retornar o mesmo resultado para os mesmos valores dos argumentos, mas o resultado pode mudar entre comandos SQL. Esta é a seleção apropriada para as funções cujos resultados dependem de consultas de banco de dados, variáveis ​​de parâmetros.

Por fim, temos o atributo VOLATILE indica que o valor da função pode mudar mesmo dentro de uma única varredura da tabela, por isso não há otimizações que possam ser feitas. Relativamente poucas funções de banco de dados são voláteis neste sentido, como por exemplo, random() e o currval().

Com base no exposto, veremos alguns exemplos simples de utilização das Functions predefinidas pelo PostgreSQL. Começaremos com um exemplo presente na Listagem 2, onde faremos a soma de dois números inteiros e retornaremos também um valor de mesmo tipo.

CREATE FUNCTION somar(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Dentre as funções pré-definidas que temos para o banco de dados PostgreSQL podemos citar as categorias de Strings, matemáticas, numéricas, além das funções referentes a datas. Dessa forma, trabalharemos com algumas dessas funções, que podem ser feitas tanto por instruções SQL ou por consultas no PostgreSQL.

Tipos de Functions

A função Position, por definição, retorna a localização de uma substring presente na string, como podemos ver no exemplo da Listagem 3.

SELECT position('e' in 'Devmedia');
SELECT position('n' in 'Edson Dionisio');
SELECT position('s' in 'testes');

A sintaxe para este tipo de função é definida da seguinte forma:

position( substring in string )

A declaração do Position contendo uma string de caracteres realiza uma pesquisa por uma determinada substring. Além dessa função, também podemos utilizar a substring, to_char, dentre outras, presentes para a seção de Strings.

Vejamos agora um exemplo com funções numéricas, onde apresentaremos um exemplo da função power (exponenciação):

SELECT power(6, 2);

A sintaxe para esta função é definida de acordo com a seguinte expressão:

power( m, n )

Onde temos dois parâmetros sendo apresentados, de forma que o m é a base que será utilizada pela função, e o n é o expoente que será utilizado pela função.

Como nos demais bancos de dados, temos também as funções sum, min, max, mod, dentre outras que podem ser utilizadas pelo PostgreSQL. Já para tabalharmos com datas precisamos utilizar funções que possam obter informações diferenciadas. Uma dessas funções para data é a função To_Timestamp, que é apresentada com a seguinte sintaxe:

to_timestamp( string1, format_mask )

Veja que temos a adição da string a ser convertida para um tipo timestamp como sendo o primeiro parâmetro. O segundo parâmetro refere-se ao formato utilizado para a conversão, onde temos vários formatos, como por exemplo, MM (que mostra o mês no formato 01-12). Dito isso, vejamos a seguir um simples exemplo de sua utilização:

SELECT to_timestamp('2015/07/22 10:13:10, 'YYYY/MM/DD HH:MI:SS');

Tipo de dados JSON

Com o lançamento do Postgres 9.4 veio o tipo de dados jsonb. Este binário é um tipo JSON e, de igual forma ao MongoDB, ele usa armazenamento interno. Com esse novo tipo introduzido podemos realizar operações de indexação de documentos além de consultas especializadas.

JSON é bom para trabalhar com matrizes de tipos de dados de primeira classe, permitindo realizar alguns mapeamentos bastante úteis no servidor, não permitindo potenciais gargalos de desempenho no lado do cliente.

No exemplo da Listagem 4 utilizaremos três dos tipos JSON disponíveis: o row_to_json, json_agg e o array_to_json. Veremos como estes três podem moldar os dados relacionais do lado do banco de dados. A nossa base de dados terá duas tabelas: álbuns e artistas.

CREATE TABLE albuns
(
 id int DEFAULT nextval('albuns_id_seq'::regclass) NOT NULL,
 titulo_album VARCHAR(160) NOT NULL,
 artista_id INT NOT NULL,
 CONSTRAINT pk_albuns PRIMARY KEY  (id)
);
CREATE TABLE artistas
(
 id int DEFAULT nextval('artista_id_seq'::regclass) NOT NULL,
 nome_artista VARCHAR(120),
 CONSTRAINT pk_artista PRIMARY KEY  (id)
);

Podemos utilizar a função row_to_json() para adicionar uma linha completa de dados a um objeto JSON antes que este conjunto de resultados seja retornado. Como por exemplo, podemos pegar todos os registros referentes a um artista em questão e em seguida, retornar as linhas de JSON, como podemos ver na Listagem 5.

select row_to_json(artista)
from (
 select * from artista
) as artista

Veja que transformamos um conjunto de registros do artista em um JSON. Ao executarmos a nossa consulta temos o retorno também no mesmo formato, como mostrado na Listagem 6.

{"id":1,"nome":"AC/DC"}
{"id":2,"nome":"Evanescence"}
{"id":3,"nome":"Aerosmith"}
{"id":4,"nome":"Foo Fighters"}
{"id":5,"nome":"Ramstein"}

Além do row_to_json(), o banco de dados PostgreSQL também nos oferece a função conhecida por json_agg(), a qual recebe valores de entrada e, em seguida, agrega esses valores como sendo uma matriz JSON, de acordo com o que apresentamos na Listagem 7. Veja que queremos agregar ao registro do álbum de um artista em específico numa matriz JSON.

select json_agg(albuns)
from (
 select * from albuns where artista_id = 4
) as albuns;

Dessa forma, temos uma série de álbuns sendo apresentados por artistas específicos. Agora podemos expandir esses e retornar o registro artista como um objeto JSON, incluindo uma propriedade de álbuns representados por um array, no qual estão contidos todos os álbuns de cada artista.

No próximo exemplo podemos obter novamente cada linha de registro como um objeto JSON completo, de forma que a cada registro de um artista seja representado como um JSON e que, além disso, contenha uma propriedade de álbuns, que por sua vez contém uma matriz de objetos de álbuns. Nesse ponto podemos agregar os registros de álbuns de cada artista usando a função json_agg() em uma subconsulta correlacionada com a tabela de artistas, e depois podemos passar cada linha para a função row_to_json() com o intuito de transformarmos o resultado em um objeto JSON completo, pronto para uso em nossa aplicação. Na Listagem 8 temos a transformação e a agregação dos registros de álbuns e artistas em objetos JSON.

select row_to_json(art) as artista
 from(
   select a.id, a.nome, 
   (select json_agg(album)
   from (
     select * from albuns where artista_id = a.id
   ) album
 ) as albuns
 from artista as a) art;

Como resultado dessa consulta temos o código da Listagem 9.

{"id":1,"nome":"AC/DC","albuns":[
  {"id":1,"titulo_album":"Powerage","artista_id":1},
  {"id":4,"titulo_album":"Let There Be Rock","artista_id":1},
  {"id":6,"Back in black","artista_id":1},
  {"id":8,"titulo_album":"Highway to hell","artista_id":1}
]}
{"id":2,"nome":"Evanescence","albuns":[
 {"id":2,"titulo_album":"The open door","artista_id":2}, 
 {"id":3,"titulo_album":"Fallen","artista_id":2},
 {"id":9,"titulo_album":"Origin","artista_id":2}
]}
{"id":3,"nome":"Aerosmith","albuns":[
 {"id":5,"titulo_album":"Big Ones","artista_id":3},
 {"id":10,"titulo_album":"Get a grip","artista_id":3}
]}

Com isso temos o JSON pronto para retornar informações para a nossa aplicação, além de objetos completamente montados referentes aos artistas com seus álbuns disponíveis como uma matriz, de acordo com as propriedades dos álbuns presente em cada um dos artistas.

Utilizando cláusula Within Group

Criaremos neste momento um outro exemplo com base em filtros e agrupamento de informações utilizando a cláusula WITHIN GROUP, que é bastante útil quando precisamos realizar agregações em subconjuntos de dados de forma ordenada.

Desde a versão 9.0 do PostgreSQL foram introduzidas as Windows Function, a fim de trabalhar melhor em subconjuntos de dados que podem ser correlacionados a cada registro atual de tabelas, definindo uma espécie de "agregados" centrados em qualquer registro específico. Uma consulta pode ser executada gradualmente através das cláusulas SQL OVER (PARTITION BY/ORDER BY) usando as funções que podem ser executadas nessas agregações.

Já com a versão 9.4, a cláusula SQL WITHIN GROUP foi introduzida para simplificar muitas das operações que só tinham sido possíveis anteriormente com o uso das windows funtions, definindo assim as agregações de subconjuntos ordenados de dados. Além disso, novas funções foram introduzidas para estes subconjuntos, expandindo a coleção das Windows functions disponíveis, como podemos citar as funções percentile_cont() e PERCENTILE_DISC(), utilizadas para realização do cálculo de porcentagens.

A função mode(), que é uma função estatística para subconjuntos, além de rank(), percent_rank(), que são funções executadas em subconjuntos, obtidas com a utilização da cláusula OVER (PARTITION BY/ORDER BY). Esta é capaz de tomar como parâmetro subconjuntos ordenados produzidos pela cláusula WITHIN GROUP.

Vejamos na Listagem 10um exemplo em que essas funções são utilizadas.

$ CREATE TABLE tab AS SELECT generate_series(1, 20) AS valor;
 $ WITH subgrupo AS (
     SELECT valor,
        ntile(4) OVER (ORDER BY valor) AS grupo
     FROM tab
   )
   SELECT max(valor)
   FROM subgrupo GROUP BY grupo ORDER BY grupo;

As funções generate_series, ntile e a max são usadas com o intuito de calcular grupos de valores com base nos 20 primeiros valores inteiros armazenados. Isso foi possível através da divisão dos números em quatro grupos com a utilização da cláusula OVER (PARTITION BY/ORDER BY). Em seguida, foram ordenandos em quatro subconjuntos, para no final pegar o valor máximo de cada grupo. Com o PostgreSQL 9.4 podemos reduzir toda essa operação em um único comando SQL, de forma que podemos obter vantagens significativas referentes a legibilidade dos scripts e a execução dos comandos necessários, como podemos ver na Listagem 11.

$ SELECT unnest(percentile_disc(array[0.25,0.5,0.75,1])
     WITHIN GROUP (ORDER BY valor))
   FROM tab;

Cláusula Filter

Essa cláusula é bastante útil quando queremos realizar uma filtragem de informações para um determinado conjunto de dados sem a necessidade de uma agregação de performance. Por exemplo, podemos ver agora que é possível realizarmos uma contagem total dos registros presentes em uma tabela, além de termos também uma contagem parcial (code>count) para um dos subconjuntos que venha a satisfazer uma determinada condição presente em uma única consulta sem termos que usar outras operações em agregações, como podemos ver na Listagem 12.

SELECT count(*) contagemTotal,
    count(*) FILTER(WHERE produto = 1) vendaProduto1,
    count(*) FILTER(WHERE produto = 2) vendaProduto2
FROM historico_vendas;

Neste pequeno trecho de código temos também uma maior legibilidade e simplificação do código, além de uma melhoria na performance de execução da query.

Temos na versão 9.4 do PostgreSQL uma extensão do padrão SQL através da introdução dessas novas cláusulas que facilitam ainda mais o nosso trabalho como desenvolvedores, de forma que elas são cada vez mais capazes de delegar a manipulação e agregação de subconjuntos de dados para o banco de dados.

Ao utilizarmos a cláusula WITHIN GROUP, temos que as gestões dos subconjuntos de dados podem ser ordenadas de forma mais simples e fácil com as Windows Functions.

A cláusula FILTER nos ajuda a gerenciar os subconjuntos de dados de forma a cumprir determinadas condições, evitando assim a utilização de agregações.