主页 / dba / 问题 / 37230
Accepted
Chau
Asked: 2013-03-22 04:11:01 +0800 CST

使用 plpgsql 进行递归查询

  • 772

我正在尝试编写一个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编译器没有检测到任何问题，那么我在这里做错了什么？

postgresql functions

2 个回答

  1. Best Answer

    你根本不需要函数，这可以用一条 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

    • 6

  2. 如果您确定要在 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 的观点。

    • 1

相关问题