PostgreSQL: Funções que retornam conjuntos de registros

Olá mentes ávidas do amanhã!

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.

 

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, por exemplo, enquanto eu estava escrevendo a primeira versão minha doce namorada Bruna me deu várias luzes. 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:

CREATE TABLE pessoa_fisica (
	id_pessoa SERIAL,
	nome VARCHAR(80),
	sobrenome VARCHAR(200),
	sexo CHAR(1),
	cpf CHAR(11),
	PRIMARY KEY(id_pessoa)
);

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:

CREATE FUNCTION blog_get_pessoas() RETURNS SETOF RECORD AS $$
BEGIN
	RETURN QUERY SELECT id_pessoa, nome, sobrenome, sexo, cpf FROM pessoa_fisica;
	RETURN;
END;
$$ LANGUAGE 'plpgsql';

SELECT * FROM blog_get_pessoas() AS (id_pessoa INTEGER, nome VARCHAR, sobrenome VARCHAR, sexo CHAR, cpf CHAR)

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:

CREATE FUNCTION blog_get_pessoas2() RETURNS SETOF pessoa_fisica AS $$
BEGIN
	RETURN QUERY SELECT * FROM pessoa_fisica
	RETURN;
END;
$$ LANGUAGE 'plpgsql'

SELECT * FROM blog_get_pessoas2();

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:

CREATE FUNCTION blog_get_pessoas3() RETURNS TABLE (id_pessoa INT,
						   nome VARCHAR,
						   sobrenome VARCHAR,
						   sexo CHAR,
						   cpf CHAR) AS $$
BEGIN
	RETURN QUERY SELECT * FROM pessoa_fisica;
	RETURN;
END;
$$ LANGUAGE 'plpgsql'

SELECT * FROM blog_get_pessoas3();

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:

CREATE TYPE type_pessoa_fisica AS (
	id_pessoa INT,
	nome VARCHAR,
	sobrenome VARCHAR,
	sexo CHAR,
	cpf CHAR
);

Repare que a instrução CREATE TYPE é bem semelhante a instrução para criação de tabelas, com ela é possivel 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:

CREATE FUNCTION blog_get_pessoas4() RETURNS SETOF type_pessoa_fisica AS $$
DECLARE
	dados_pessoa type_pessoa_fisica;
BEGIN
	FOR dados_pessoa IN SELECT id_pessoa, nome, sobrenome, sexo, cpf  FROM pessoa_fisica LOOP
		RETURN NEXT dados_pessoa;
	END LOOP;
	RETURN;
END;
$$ LANGUAGE 'plpgsql'

SELECT * FROM blog_get_pessoas4();

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.

Espero que tenham gostado! Façam os testes e não deixem de comentar, e conheça também a fã page do blog no Facebook.

Referências:

Abraços,

Tiago.

Add a Comment

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *