Como fazer funções que retornam conjuntos de registros no PostgreSQL

Algumas vezes eu me deparei com o problema de fazer funções que retornam conjuntos de registros. 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.

Então, vejamos como implementar funções que retornam um conjunto de registros:

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) Funções que Retornam Conjuntos de Registros: 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) Funções que Retornam Conjuntos de Registros: 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:

• PostgreSQL Documentation – Create Function
• WikiBooks – Funções em PL/pgSQL
• PostgreSQL Brasil – Retornando registros de consultas genéricas com RETURN QUERY

Abraços,

Tiago.