PostgreSQL: Funções que retornam conjuntos de registros

Algumas vezes eu me deparei com o problema de retornar mais de um resultado com uma função, um conjunto de resultados, e o Postgres nos favorece com alguns artifícios para tal. De fato não são exatamente muito simples de se manejar, mas também não é um bicho de sete cabeças.

Tal como fizemos quando manipulamos triggers, aqui iremos utilizar a linguagem PL/pgSQL para construirmos nosso trabalho, que consistirá em quatro maneiras. Outra informação importante é que enquanto eu escrevia esse post estava usando a versão 8.3 para realizar os testes nos códigos.

Primeiro Passo

Eu encontrei duas  maneiras de executar esse trabalho, e após compartilhar o post no grupo PostgreSQL  Brasil no LinkedIn, o Fabrízio que orientou mais duas maneiras que veremos a seguir, e se você souber de mais alguma sinta-se livre para contribuir.

Bom mas abordando o nosso tema, inicialmente tomemos vejamos as seguintes linhas de código que representam a tabela que será a base das nossas consultas:

Agora vamos aos métodos de retorno dos dados que estão dentro desta tabela (presumo que você tenha abastecido ela), como mencionei vou abordar quatro maneiras.

1) Tipo retorno RECORD

Um tipo “genérico”, bem mais comum nas buscas na internet, este é o tipo mais comentado, mas existe um empecilho, o qual é necessário especificar as colunas que iremos resgatar quando chamarmos a função, veja o código abaixo na linha 8:

Veja que quando defini o tipo de retorno da função eu determinei dois parâmetros, SETOF que indica que a função irá retornar um conjunto de itens, ao invés de um único item e RECORD que está me dizendo que o retorno será um conjunto de resultados. Perceba que utilizei a instrução RETURN QUERY e logo após um SELECT tradicional para “capturar” os dados, o RETURN QUERY foi introduzido no Postgres na versão 8.3.

Observe que a instrução RETURN (sozinha, na linha 4) é que irá realizar o retorno do nosso conjunto. Agora note a forma estranha que chamamos a função, como se fosse uma tabela realizamos o select e em seguida empreendemos um “alias” especificando quais campos vieram na query realizada (estranho, não?), enfim se você não colocar todos os campos, conforme a query que foi realizada na função, haverá problemas.

2) Retornando os campos de uma dada Tabela

Agora se os dados de retorno da sua função forem oriundos de apenas uma tabela, você pode setar o tipo de retorno como sendo exatamente o da tabela trabalhada, veja que no nosso exemplo que coloquei pessoa_fisica, como o tipo de retorno:

Aqui temos como retorno possível todos os campos da nossa tabela de teste, no caso pessoa_fisica, claro que ao chamar a função você poderia selecionar os campos que vão lhe interessar. Repare que ao contrário do primeiro exemplo não precisamos especificar quais campos eventualmente viriam na query, essa na minha opinião é uma das maneiras mais práticas, mas ai vai da sua necessidade.

3) Tipo de retorno RETUNS TABLE

Apensar de eu não conseguir testar o RETURNS TABLE (porque a versão 8.3 ainda não suportava esse recurso) ela existe, veja aqui na documentação oficial do Postgres. Dê uma olhada de como ficaria o código da nossa função:

Repare que declaramos os atributos, que serão os “nomes de coluna” quando chamarmos nossa função. É muito útil quando vamos retornar dados de mais de uma tabela, ao contrário de tudo que já vimos anteriormente, dessa maneira podemos expressar no corpo da função, explicitamente, quais serão as tuplas no retorno. Uma observação importante: repare que na linha 7 eu estou selecionando todos os campos, você deve atentar-se para que todos os campos coincidam com os determinados no tipo de retorno.

4) Criando um tipo específico de retorno, o CREATE TYPE

Já a quarta opção, é que criamos o tipo de retorno da função “personalizado”, exatamente como nesta instrução:

Repare que a instrução CREATE TYPE é bem semelhante a instrução para criação de tabelas, com ela é possível criar um “mix” de duas tabelas por exemplo, ai vai da sua imaginação, aqui no meu exemplo ficou bem simples, mas agora que vem a parte interessante, veja como ficou a nossa função:

A primeira coisa que notamos a ler é o tipo de retorno da função, o type_pessoa_fisica que já especifica quais os campos que serão retornados, posteriormente temos o DECLARE onde construimos uma variável chamada dados_pessoa que irá conter o conteudo da nossa query, note que tem o mesmo tipo de retorno da função, exatamente porque é esta variável que vamos retornar. Em seguida, para abastecer a nossa variável com os registros da tabela temos a instrução FOR-IN-SELECT que é m looping que a cada iteração “coloca uma linha na variável”, repare que a instrução RETURN NEXT é que “abastece” nossa variável, e apesar de ser um “RETURN” não encerra a execução do código naquele instante, note que há a instrução RETURN após o fim do loop (END LOOP), esta sim irá terminar a função.

E na linha 12 temos a chamada da função, bem mais amigável que em comparação com o experimento 1, e é (quase) exatamente como a chamada de uma tabela, lá você pode introduzir as clausulas WHERE e LIMIT por exemplo.

Referências:

Abraços,

Tiago.

Vídeo Aulas: Redes, IP, Máscara de Rede e Gateway

Olá Amigos!

Ainda estudando frenecamente para o mestrado na UFSCar, hoje eu trago para vocês uma dica que vi no Facebook do meu ex-Prof Avelino Pimenta, ele postou o link de algumas vídeo aulas do Kretcheu, eu dei uma conferida nelas e digo que me ajudaram muito com a minha matéria de redes.

As vídeo aulas são bem explicativas e vão falar sobre protocolos das redes, ip, máscara de rede e gateway, apesar de serem muito boas são bem curtinhas, totalizando quase uma hora de aula, e mais, aconselho você a dar uma olhada no canal do YouTube que está repleto de material sobre software livre e derivados, vale a pena 😉

Abaixo estão dos vídeos, na ordem que eu vi, espero que gostem:

Em seguida, veja este:

Abraços,

Tiago.

Conhecendo o operador SimilarTo do PostgreSQL

Olá Amigos!

Prazer em revê-los novamente aqui no blog, e inicialmente eu gostaria de agradecer a audiência que vem crescendo a cada mês! Já estamos indo para 7 meses de blog e meus posts sobre o PostgreSQL estão sendo bem recebidos pela comunidade! Meu muito obrigado a vocês que fazem o blog acontecer!

Tratando agora do nosso assunto, em um post anterior sobre a utilização do operador iLIKE eu mencionei a particularidade do Postgres em realizar buscas discernindo os caracteres upper-case de low-case, no entanto há também a possibilidade de inserir expressões regulares a fim de refinar a busca, e para tal utilizamos o operador Similar To.

Para ilustrar o funcionamento desse operador, tome como base este problema: “Recuperar o nome das pessoas que terminam em “Silva” ou “Souza”. Aqui vocês irão me dizer que poderíamos utilizar os operados que já conhecemos como o LIKE e o iLIKE, e claro que sim, poderíamos e inclusive eu fiz algumas comparações com o desempenho, veja só:

SELECT * FROM pessoas WHERE sobrenome LIKE '%SILVA' OR sobrenome LIKE '%SOUZA' -- Gastou 150 ms com 1961 resultados
SELECT * FROM pessoas WHERE sobrenome iLIKE '%silva' OR sobrenome iLIKE '%souza' -- Gastou 250 ms com 1961 resultados
SELECT * FROM pessoas WHERE sobrenome SIMILAR TO '(%SILVA|%SOUZA)' -- Gastou 350 ms com 1961 resultados
SELECT * FROM pessoas WHERE sobrenome SIMILAR TO '%(SILVA|SOUZA)' -- Gastou 300 ms com 1961 resultados

Pelo menos aqui nos meus testes o Similar To se mostrou o operador mais lento, apesar de não existir nenhuma referência sobre o desempenho dele no manual. Por natureza isso se deve acontecer porque ele mescla o funcionamento dos outros operadores testados com a implementação e expressões regulares POSIX. Eu não encontrei uma maneira de fazer o Similar To funcionar como o iLIKE, então se alguém tiver alguma sugestão sinta-se livre para comentar =)

Listei aqui abaixo os artivicios que você utlilizar para realizar junto com o Similar To como está no manual do Postgres:

  • | representa alternância (uma das duas alternativas).
  • * representa a repetição do item anterior zero ou mais vezes.
  • + representa a repetição do item anterior uma ou mais vezes.
  • Os parênteses () podem ser utilizados para agrupar itens em um único item lógico.
  • A expressão de colchetes […] especifica uma classe de caracteres, do mesmo modo que na expressão regular POSIX.

Seria muito interessante você dar uma lida no Manual do Postgres, lá você irá encontrar material mais completo, nas referências abaixo você tem o link que está em inglês, se preferir aqui está a versão em português, só que da versão 8.0 (apensar da versão em inglês não mudar muita coisa).

Referências:

Abraços,

Tiago.