sql - 在存储过程中使用 WITH，postgres

Question

我正在处理带有项目列表的表格。对于每个项目，都会有一个父项目。树结构也是如此。

我需要获取项目的完整详细信息，例如 项目 1 >> 项目 1.1 >> 项目 1.1.1 >> 项目 1.1.1.1 >> 项目 1.1.1.1.1 所以我决定创建一个函数，当我们传递项目的 id 时，它会返回详细信息，在 Item 1.1.1.1.1 的示例 id 中

CREATE TABLE item (
    item_id bigint,
    item_name text,
    item_code text,
    item_parentid bigint
);

INSERT INTO item VALUES (1, 'Item 1', 'Item 1', NULL);
INSERT INTO item VALUES (2, 'Item 1.1', 'Item 1.1', 1);
INSERT INTO item VALUES (3, 'Item 1.1.1', 'Item 1.1.1', 2);
INSERT INTO item VALUES (4, 'Item 1.1.1.1', 'Item 1.1.1.1', 3);
INSERT INTO item VALUES (5, 'Item 1.1.1.1.1', 'Item 1.1.1.1.1', 4);

到目前为止，我必须使用“WITH RECURSIVE”编写一个查询来检索详细信息。但是不知道如何在函数中编写相同的内容并返回项目名称。

WITH RECURSIVE itemtree (item_id, item_name, item_code,item_parentid,depth) AS (
            SELECT item_id,item_name, item_code,item_parentid,1 FROM item WHERE item_id = 5
            UNION
            SELECT child.item_id,child.item_name, child.item_code,child.item_parentid,depth+1 FROM item child
            INNER JOIN itemtree parent ON child.item_id = parent.item_parentid 
            )

SELECT array_to_string(array_agg(T.item_name), '>>>')  FROM (SELECT * FROM itemtree ORDER BY depth DESC) T;

我正在使用 PostgreSQL 8.4.22。

Answer 1

解决方案相当简单。起初我的想法是使用 SELECT INTO 或 EXECUTE 语句执行查询并返回结果。但这完全没有必要。感谢另一个 SO 帖子，How to write WITH(CTE) within function in PostgreSQL .现在我已经更正了查询并将其发布在下面

--Have to add a new procedure to find the parent
CREATE OR REPLACE FUNCTION getitemname(itemid bigint) RETURNS text
    LANGUAGE plpgsql STRICT
    AS $$
DECLARE
    item RECORD;
BEGIN  

    WITH RECURSIVE itemtree (item_id, item_name, item_code,item_parentid,depth) AS (
            SELECT item_id,item_name, item_code,item_parentid,1 FROM om_item WHERE item_id = $1 AND deleted = 0
            UNION
            SELECT child.item_id,child.item_name, child.item_code,child.item_parentid,depth+1 FROM om_item child
            INNER JOIN itemtree parent ON child.item_id = parent.item_parentid 
            )

    SELECT array_to_string(array_agg(T.item_name), '>>>') INTO item FROM (SELECT * FROM itemtree ORDER BY depth DESC) T;
    return item;

END;
$$;


SELECT getitemname(5);