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.

Look [here][postgresql.org/docs/8.4/static/plpgsql-control-structures.html] at starting point, specially with RETURN QUERY — Houari
– Houari, Commented Dec 11, 2014 at 11:13
@Houari The difficulty is not to return the results. I can only find SELECT INTO keyword for executing the query and storing the result to a variable. But i was not been able to write the above query to SELECT INTO format. — Nandakumar V
– Nandakumar V, Commented Dec 11, 2014 at 11:32

Community · Accepted Answer · 2017-05-23 10:26:08Z

6

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Dec 12, 2014 at 11:05

Nandakumar V

4,7255 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Filipe Roxo Over a year ago

So your question was how to do a function in Postgres

Filipe Roxo · Accepted Answer · 2014-12-11 20:24:53Z

0

You can write the query to an INSERT INTO format as:

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

answered Dec 11, 2014 at 20:24

Filipe Roxo

7424 silver badges10 bronze badges

2 Comments

Nandakumar V Over a year ago

How can i execute this query and return the result from the function?

Filipe Roxo Over a year ago

Thought your question was about INSERT INTO, saw your onw answer now

Collectives™ on Stack Overflow

Using WITH in a stored procedure, postgres

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related