Recentemente, recebi uma dúvida sobre o RETURN QUERY, funcionalidade implementada na versão 8.3 do PostgreSQL, que permite o retorno de conjuntos de linhas e colunas de uma função. A dúvida do Patrick é bastante comum e até pouco tempo eu não teria uma resposta adequada:
"Patrick Espake disse...
Eu estou tentando usar isso, para retorna uma linha que vem de um inner join de diversas tabelas.
O que eu coloco para o RETURNS SETOF? Visto que os meus dados vem de uma junção de diversas tabelas.
Obrigado."
A tabela indivíduo será utilizada para o teste do RETURN QUERY retornando uma consulta qualquer.
CREATE TABLE individuo (
cod integer PRIMARY KEY,
nome varchar(50));
INSERT INTO individuo VALUES (1,'Teste');
INSERT INTO individuo VALUES (2,'Teste 2');
Agora, a função RET_ROWS mostra como utilizar o retorno de tipos RECORD para funções:
--Retornando consulta de várias linhas
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT * FROM individuo; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
A grande diferença desta forma de programar está na chamada da função. Ao utilizar valor de retorno do tipo RECORD, deve ser indicada a lista de campos a serem retornados e seus tipos. Esta pode ser vista como uma limitação, embora na prática também seja uma forma de se garantir a confiabilidade dos resultados retornados na execução de comandos SQL dentro de funções pela validação dos valores retornados.
SELECT * from ret_rows() as (c1 integer, c2 varchar(50)) ; --Declaração dos campos do record
Falta considerar a execução de uma junção dentro da função, que é nossa questão original. Para tanto, será criada uma tabela com os filhos dos indivíduos para sofrer junção com a de indivíduos. O exemplo abaixo executou sem qualquer problema na versão 8.3:
CREATE TABLE filhos (
cod integer PRIMARY KEY,
filhos integer);
INSERT INTO filhos VALUES (1,1);
INSERT INTO filhos VALUES (2,3);
--Retornando Resultado de Junção
CREATE OR REPLACE FUNCTION ret_filhos (psql integer) RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT individuo.nome, filhos.filhos FROM individuo, filhos WHERE individuo.cod = filhos.cod AND individuo.cod = $1; -- Consulta
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;
SELECT * from ret_filhos(1) as (c1 varchar(50), c2 integer) ; --Declaração dos campos de retorno
SELECT * from ret_filhos(2) as (c1 varchar(50), c2 integer) ;
Outras Soluções
Outras soluções para o retorno de registros em funções são a consolidação e retorno dos dados dentro de um campo texto ou XML.
Resolveu, Patrick?
