使用 plpgsql 進行遞歸查詢

我正在嘗試編寫一個plpgsql函式，該函式從樹結構的記錄中遞歸返回一組列。

我有一個數據表和一個將數據連結在一起的表：

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

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

我的想法是在以下功能中做類似的事情：

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;

但這不起作用，我在第一個查詢中收到錯誤：

錯誤：查詢結構與函式結果類型不匹配

我的Just-In-Brain編譯器沒有檢測到任何問題，那麼我在這裡做錯了什麼？

你根本不需要函式，這可以用一條 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

請參閱手冊了解遞歸查詢的介紹：http ://www.postgresql.org/docs/current/static/queries-with.html

如果您確定要在 plpgsql 函式中執行此操作，則只需進行一些修改即可：

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;

• 關於您指出的錯誤，將函式呼叫括在括號中並在後面.*添加
• 因為返回表的列名與您的相同datatable，所以您必須在第二個查詢中限定列名
• 刪除) C以獲得正確的語法

無論如何，我同意 a_horse_with_no_name 關於使用 CTE 的觀點。

引用自：https://dba.stackexchange.com/questions/37230