Eu uso Postgresql 9.1 com Ubuntu 12.04.
Em uma plpgsqlfunção tento concatenar o setoftipo retornado de outra função.
o type pair_id_valueem questão é criado comcreate type pair_id_value as (id bigint, value integer);
a função que retorna elementar setof pair_id_value(aquelas que serão concatenadas posteriormente) é esta:
create or replace function compute_pair_id_value(id bigint, value integer)
returns setof pair_id_value
as $$
listResults = []
for x in range(0,value+1):
listResults.append({ "id": id, "value": x})
return listResults
$$
language plpython3u;
este código plpython direto deve ser bom, por exemplo, a consulta: select * from compute_pair_id_value(1712437,2);retorna bem:
id | value
---------------+-----------
1712437 | 0
1712437 | 1
1712437 | 2
(3 rows)
esta função python é bastante simples por enquanto, para este exemplo, mas acima de tudo para minha prova de conceito. Será mais complexo no futuro próximo.
O problema surge quando tento concatenar todas as tabelas de resultados de múltiplos id.
create or replace function compute_all_pair_id_value(id_obj bigint)
returns setof pair_id_value as $$
declare
pair pair_id_value;
begin
for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
loop
return next pair;
end loop;
return;
end; $$ language plpgsql;
Recebo o erro: invalid input syntax for integer "(1712437,0)"como se não fosse mais visto como pair_id_value com duas colunas, mas como uma tupla (1712437,0).
Então mudei o tipo de saída da função de setof pair_id_value para setof record... e se eu executar esta função de concatenação semelhante:
create or replace function compute_all_pair_id_value(id_obj bigint)
returns setof record as $$
declare
pair record;
begin
for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
loop
return next pair;
end loop;
return;
end; $$ language plpgsql;
Eu recebo o erro:a column definition list is required for functions returning "record"
Tentando seguir a resposta para esta pergunta do SO : tentei definir a definição da coluna no select desta forma select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer), o código completo está aqui:
create or replace function compute_all_pair_id_value(id_obj bigint)
returns setof record as $$
declare
pair record;
begin
for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
loop
return next pair;
end loop;
return;
end; $$ language plpgsql;
Mas ao lançar o script sql, o psql não aceita criar a função:
syntax error at or near "(" select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer)... apontando o dedo para o f(
Alguma idéia de como fazê-lo corretamente?
Devo considerar a criação de uma tabela temporária para fazer o trabalho?
A abordagem que você está usando é desnecessariamente complexa - e muito ineficiente. Em vez da primeira função, use:
ou melhor, elimine-o completamente e escreva toda a operação assim:
Resultando em:
Isso explora o comportamento do PostgreSQL com funções de retorno de conjunto chamadas na
SELECTlista.LATERALAssim que o PostgreSQL 9.3 for lançado, ele poderá ser substituído por uma consulta compatível com os padrões .Como sua pergunta era uma versão simplificada do problema real, vamos resolver isso. Vou trabalhar com o simplificado
compute_pair_id_valueacima para evitar o incômodo de plpython3. Veja como fazer o que você deseja:Resultado:
mas, novamente, esteja avisado que
compute_pair_id_valueserá chamado mais de uma vez. Esta é uma limitação do executor de consultas do PostgreSQL que pode ser evitada no 9.3 comLATERALsuporte, mas, pelo que sei, você está preso a ele no 9.2 e abaixo. Observar:resultado:
Veja como
compute_pair_id_valueé chamado uma vez por coluna de saída?Existe uma solução alternativa: outra camada de subconsulta para descompactar o resultado do tipo composto. Ver:
Você pode usar a mesma técnica em seu código se realmente precisar
LOOPdos resultados (é lento fazer isso, então evite se puder).