quinta-feira, 10 de abril de 2008

RETURN QUERY - Novo recurso do PostgreSQL 8.3

A cláusula RETURN QUERY permite que o programador faça uma função que retorne um conjunto de linhas. Foi acrescentada no PostgreSQL 8.3, permitindo maior versatilidade nas implementações.

Para ilustrar esta funcionalidade, vamos criar e popular uma tabela:

CREATE TABLE FUNCTESTE (
cod serial primary key, nome varchar(50), aniversario date default now());
INSERT INTO FUNCTESTE VALUES (1, 'Cláudio', DEFAULT);
...
INSERT INTO FUNCTESTE VALUES (10, 'Ana Cláudia', '01/01/2008');


Vamos retornar um conjunto de linhas utilizando a cláusula RETURN QUERY dentro de uma função. Observe que o código da função abaixo retorna um conjunto de linhas (SETOF) que tem de ser iguais aos campos da tabela FUNCTESTE:

--Retornando consulta de várias linhas com FOR
CREATE OR REPLACE FUNCTION ret_rows () RETURNS SETOF FUNCTESTE AS $$
BEGIN
RETURN QUERY SELECT * FROM FUNCTESTE; -- Acrescenta um conjunto de linhas ao retorno da função
RETURN ; -- Retorna as linhas
END;
$$ LANGUAGE plpgsql;

Para testar a função:

select * from ret_rows();


Em uma função, o RETURN QUERY pode ser utilizado mais de uma vez, mas o retorno feito com RETURN; ´faz a descarga dos valores de uma vez só.

--Retornando a mesma consulta várias vezes com WHILE
CREATE OR REPLACE FUNCTION ret_rows_while () RETURNS SETOF FUNCTESTE AS $$
DECLARE
i INTEGER :=1;
BEGIN
WHILE i <= 5 LOOP
RETURN query SELECT * FROM FUNCTESTE LIMIT 1; --Consulta a ser repetida
i:= i + 1;
END LOOP;
RETURN ;
END;
$$ LANGUAGE plpgsql;

Pode ser retornado um conjunto de elementos de qualquer valor.

--Retornando consulta de vários resultados do tipo inteiro
CREATE OR REPLACE FUNCTION ret_rows_int () RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY SELECT cod FROM FUNCTESTE; --Consulta
RETURN ; -- Retorno de dados
END;
$$ LANGUAGE plpgsql;

select * from ret_rows_int();

A cláusula complementa a função do RETURN NEXT, que era a única possibilidade implementada nas versões anteriores. A sintaxe em geral é mais simples.

6 comentários:

Anônimo disse...

Olá, Cláudio.

Deixa ver se entendi direito este novo recurso.

Eu não vou precisar mais ficar utilizando SP's para preencher uma tabela fixa ou usar Temp Tables para retornar algumas linhas de uma tabela e não terei conflitos com o uso simultâneo. Correto?

Poderei executar a RETURN QUERY na minha máquina e na máquina do colega do lado ao mesmo tempo que não teremos problemas de travamento de tabelas?

Abraço e continue com seu excelente blog!

Cláudio Leopoldino disse...

Na verdade, Zé, nada impede que você continue a usar os métodos anteriores :)

Este recurso está relativamente bem testado, então a não ser que você descubra um novo bug ou limitação podemos ficar tranquilos. Bom, por via das dúvidas, vou fazer mais alguns testes por aqui.

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.
Abraço.

Anônimo disse...

tb gostava de saber

Cláudio Leopoldino disse...

A resposta das dúvidas está neste post. Espero que tenha suprido a necessidade.

http://postgresqlbr.blogspot.com/2008/12/retornando-registros-de-consultas.html

Yuri Jerônimo Brilhante disse...

Olá,
tentei criar essas funções mas dá sempre esse erro a seguir:



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

alguém tem alguma dica?

obrigado desde já!