在存储过程中使用WITH，PostgreSQL

Question

我正在处理一张包含物品列表的表格。对于每个物品，都会有一个父级物品。因此，这是一个树形结构。

我需要获取一个物品的全部细节，例如物品1 >> 物品1.1 >> 物品1.1.1 >> 物品1.1.1.1 >> 物品1.1.1.1.1。因此，我决定创建一个函数，当我们传入物品的id时，将返回该物品的详细信息，在此示例中为物品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版本。

- Nandakumar V

---

看看[这里][http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html]的起点，特别是`RETURN QUERY`。 - Houari

@Houari 难点不在于返回结果。我只能找到 SELECT INTO 关键字来执行查询并将结果存储到变量中。但是我无法将上述查询编写为 SELECT INTO 格式。 - Nandakumar V

Answer 1

解决方案很简单。初步想法是使用SELECT INTO或EXECUTE语句执行查询并返回结果。但完全没有必要。多亏了另一篇SO帖子：如何在PostgreSQL中编写带有CTE的函数。现在我已经纠正了查询并在下面发布了它。

--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);

- Nandakumar V

---

所以你的问题是如何在Postgres中编写一个函数。 - Filipe Roxo

Answer 2

您可以将查询编写为INSERT INTO格式，如下：

INSERT INTO item_names
SELECT name
FROM
(
    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), ' >>> ') AS name FROM (SELECT * FROM itemtree ORDER BY depth DESC) T
) res

- Filipe Roxo

---

我该如何执行这个查询并从函数中返回结果？ - Nandakumar V

我以为你的问题是关于INSERT INTO的，现在看到了你自己的答案。 - Filipe Roxo