Using WITH in a stored procedure, postgres
I am working on a table with a list of items. There will be a parent for each element. There is also a tree structure.
I need to get complete information about a product 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 that will return details when passing the item ID, in the example id 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've had to write a query using "WITH RECURSIVE" to get the details. But didn't know how to write the same thing inside the function and return the element 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
.
source to share
The solution was pretty simple. At first, I thought I needed to execute the query and return the result using the SELECT INTO
or operators EXECUTE
. But that was completely unnecessary. Thanks to another SO post, How to write WITH (CTE) inside a function in PostgreSQL . Now I have corrected the request 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);
source to share
You can write a request to the format INSERT INTO
like this:
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
source to share