Início / dba / Perguntas / 37230
Accepted
Chau
Asked: 2013-03-22 04:11:01 +0800 CST

Consulta recursiva usando plpgsql

  • 772

Estou tentando escrever uma plpgsqlfunção que retorna recursivamente um conjunto de colunas de registros em uma estrutura de árvore.

Eu tenho uma tabela de dados e uma tabela para vincular os dados:

DATATABLE
-----------
id integer
value text
info text

LINKTABLE
-----------
link integer
parent integer

Meu pensamento era fazer como na seguinte função:

CREATE OR REPLACE FUNCTION my_function(itemID integer)
  RETURNS TABLE(id integer, value text) AS
$BODY$
BEGIN    
    RETURN QUERY SELECT my_function(A.link) FROM linktable A, datatable B 
        WHERE A.parent = B.id AND B.id = itemID) C;

    RETURN QUERY SELECT id, value FROM datatable WHERE id = itemID;            
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Mas isso não funciona, recebo um erro na minha primeira consulta:

ERRO: a estrutura da consulta não corresponde ao tipo de resultado da função

Meu compilador Just-In-Brain não detecta problemas, então o que estou fazendo de errado aqui?

postgresql functions

2 respostas

  1. Best Answer

    Você não precisa de uma função, isso pode ser feito com uma única instrução SQL:

    with recursive tree as (id, parent) (
    select link as id, 
           parent
    from linktable
    where id = itemid

    union all

    select c.link as id,
           c.parent
    from linktable c
      join tree p on p.id = c.parent
) 
select dt.id, dt.value
from tree
  join datatable dt on dt.id = tree.id

    Consulte o manual para obter uma introdução às consultas recursivas: http://www.postgresql.org/docs/current/static/queries-with.html

    • 6

  2. Se você tem certeza de que deseja fazer isso em uma função plpgsql, algumas modificações o farão funcionar:

    CREATE OR REPLACE FUNCTION my_function(itemID integer)
  RETURNS TABLE(id integer, value text) AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT (my_function(A.link)).*
    FROM linktable A
    JOIN datatable B ON A.parent = B.id AND B.id = itemID;

    RETURN QUERY 
    SELECT d.id, d.value 
    FROM datatable d
    WHERE d.id = itemID;

END;
$BODY$
LANGUAGE plpgsql;
    • em relação ao erro que você indicou, coloque a chamada da função entre parênteses e adicione .*após
    • porque a tabela de retorno tem os mesmos nomes de coluna que o seu datatable, você deve qualificar os nomes de coluna na segunda consulta
    • remova ) Cpara ter uma sintaxe adequada

    De qualquer forma, concordo com a_horse_with_no_name sobre o uso de CTEs.

    • 1

relate perguntas