用于查找父链的PostgreSQL过程 [英] postgresql procedure for finding hie parent chain

问题描述

我是Postgresql的新手，需要一点帮助。我有一个名为products的表

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

我想在postgres中创建一个函数，以获取我传递的任何值的父链的层次结构，就像我传递4然后我的输出应该是

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

推荐答案

我建议使用 recursive with子句。请在下面的查询中进行检查。

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

您可以在其官方网站上阅读有关递归with子句的更多信息。或者您可以查看几个示例。

You can read more about recursive with clause on it official websites. Or you can check couple of examples.

这里是一个链接
http://technobytz.com/recursive-query-evaluation-postgresql.html

功能：试试看

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

函数执行：
select * from function_name（4）

Function Execution: select * from function_name(4)

这篇关于用于查找父链的PostgreSQL过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！