Using WITH in a stored procedure, postgres

Question

I am working on table with a list of items. For every item there will be a parent item. So is a tree structure.

I need to get the full details of an item like Item 1 >> Item 1.1 >> Item 1.1.1 >> Item 1.1.1.1 >> Item 1.1.1.1.1 So I decided to create a function which will return the details when we pass the id of the item, in the example id of Item 1.1.1.1.1

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

So far i have got to write a query using 'WITH RECURSIVE' to retrieve the details. But didn't know how to write the same inside a function and return the item name.

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;

I am using PostgreSQL 8.4.22.

Answer 1

The solution was rather simple. On first thought my idea was to execute the query and return the result using SELECT INTO or EXECUTE statements. But it was totally unnecessary. Thanks to another SO post, How to write WITH(CTE) within function in PostgreSQL. Now i have corrected the query and posted it below

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