sexta-feira, 5 de dezembro de 2008

Retornando registros de consultas genéricas com RETURN QUERY

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 solução do problema do Patrick é relativamente simples. O Return Query aceita o tipo RECORD como valor de retorno. Portanto, fica fácil retornar resultados de consultas genéricas. Para ilustrar, segue um exemplo de código Pl/ PgSQL abaixo.

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?

9 comentários:

yuri disse...

olá estou tentando criar e executar
esses exemplos de funções,
mas no momento que tento criar as funções dá o seguinte erro:

ERROR: RETURN cannot have a parameter in function
returning set; use RETURN NEXT at or near "QUERY"

será que alguém tem alguma sugestão?
obrigado des de já!!

Cláudio disse...

Yuri, o erro é causado quando se esquece de colocar a cláusula QUERY.

Você deve colocar RETURN QUERY CONSULTA, mas colocou RETURN CONSULTA.

Tente de novo que seu código vai funcionar.

Glenio disse...

cara estou tentando fazer uma consulta no banco e estou recebendo o seguinte erro :

ERROR: structure of query does not match function result type.


Estou postando a função aqui caso alguem souber e puder me ajudar.




CREATE OR REPLACE FUNCTION listar_produtos("IDPRODUTO" integer) RETURNS SETOF record AS
&&
BEGIN
RETURN query SELECT * FROM produto where idcategoria in (select idcategoria from categoria where idservico="IDSERVICO");
RETURN;

END;
$$LANGUAGE 'plpgsql'VOLATILE;
ALTER FUNCTION listar_produtos("IDSERVICO" integer) OWNER TO mungubausuer;


ai faço a busca da seguinte forma:


select * from listar_produtos(1) as (idproduto integer, idcategoria integer, nome character(40), descricao character varying(100), valor numeric(18,2));


e ai me retorna este erro.... alguem saberia o porque esta dando este erro ?

Emilio disse...

Não funciona. Mesmo problema do Yuri. Vlw.

Emilio disse...

Retiro o que eu disse ^^
Funcionou certinho.

O que eu estava errando era a chamada da função.

E tirei o SETOF pois eu precisa de apenas um RECORD fazendo as alterações necessárias posteriormente.

Obrigado e parabéns pelo Post o/

cbleopoldino disse...

Glênio, creio que pequenos erros de digitação causaram erro no seu programa:
- Você colocou && e $$ para iniciar e terminar a função, que sequer compila.
- Use de aspas simples para campos caracteres nas consultas.

O código abaixo funcionou nos meus testes.

create table produto (
idproduto integer, idcategoria integer, nome character(40), descricao character varying(100), valor numeric(18,2)
);

create table categoria (
idcategoria integer ,idservico varchar(50));

CREATE OR REPLACE FUNCTION listar_produtos("IDPRODUTO" integer) RETURNS SETOF record AS $$
BEGIN
RETURN query SELECT * FROM produto where idcategoria in (select idcategoria from categoria where idservico = 'IDSERVICO');
RETURN;
END; $$ LANGUAGE 'plpgsql' VOLATILE;

select * from listar_produtos(1) as (idproduto integer, idcategoria integer, nome character(40), descricao character varying(100), valor numeric(18,2));

Vagner Fernandes disse...

Parabens pelo Post. Resolvi um grande dilema para a substituicao do Firebird para PostGree, utilizo muito o recurso de proc selecionavel no firebird.

Mas ainda me surgiu uma duvida, no Firebird temos o recurso do FOR Select xx From xx INTO variaveis do
e assim cria-se uma estrutura de repeticao onde podemos tratar os dados e retornar somente dados que nos interessa.

Entao vem a pergunta como fazer o mesmo tratamento na funcao?
Podemos antes do Return utilizar IFs e so executar o comando RETURN quando determinado registro atender a demanda?

cbleopoldino disse...

Você também pode utilizar estruturas de repetição no Postgres e selecionar os dados que deseja, movendo-os por exemplo para uma string.

Mas a melhor prática é fazer uma consulta que só retorne os dados estritamente necessários, evitando ao máximo a construção de loops dentro de funções.

Thiago disse...

Você sabe se é possível retornar uma Function como "return" "type".

Exemplo:

CREATE TYPE sl.typ_tbl_return AS (
id int
,descr varchar(100)
,erro_cod varchar(20)
);


CREATE OR REPLACE FUNCTION
...

RETURNS sl.typ_tbl_return AS

...
DECLARE
pk_id integer;
...

INSERT INTO sl.clientes
(
"tipo","nome",
"endereco")

VALUES
(
"p_tipo","p_nome",
"p_endereco")

RETURNING "id" INTO "pk_id";

return ?????;


"No caso, vou posteriormente implementar na mesma function os outros 2 campos para compor o retorno.