I am new to postgresql and need a little help.I have a table named products
ID Product Parent_ID
1 laptop Null
2 Camera 1
3 Iphone 1
4 Mouse 2
5 Printer 2
6 Scanner 3
7 HardDisk 3
I want to create a function in postgres to get the hierarchy of the parent chain of any value i pass like if i pass 4 then my output should be
id parent_id
1 Null
2 1
4 2
I would suggest to use "recursive with" clause. Kindly check below query.
WITH RECURSIVE recursiveTable AS (
SELECT id, parent_id
FROM table_name
WHERE id = 4 -- you can pass an id here to get the output
UNION ALL
SELECT t.id, t.parent_id
FROM table_name t
JOIN recursiveTable rt ON t.id = rt.parent_id
)
SELECT * FROM recursiveTable
You can read more about recursive with clause on it official websites. Or you can check couple of examples.
Here is one link http://technobytz.com/recursive-query-evaluation-postgresql.html
Function: Try this
CREATE OR REPLACE FUNCTION function_name(param_id INT)
RETURNS TABLE (
id INT,
parent_id INT
)
AS $$
BEGIN
RETURN QUERY WITH RECURSIVE recursiveTable AS (
SELECT t.id, t.parent_id
FROM table_name t
WHERE t.id = param_id -- you can pass an id here to get the output
UNION ALL
SELECT t.id, t.parent_id
FROM table_name t
JOIN recursiveTable rt ON t.id = rt.parent_id
)
SELECT * FROM recursiveTable ;
END; $$
LANGUAGE 'plpgsql';
Function Execution: select * from function_name(4)