postgresql邻接表查询

Question

在postgresql中，我有下表

c_relation_investor_main_pfk; c_relation_investor_sub_pfk
"inv_ho";"inv_ho_r"
"inv_ho";"inv_ho_sf"
"inv_priv";"inv_com"
"inv_priv";"inv_encor"
"inv_priv";"inv_ho"
"inv_prof";"inv_b4"
"inv_prof";"inv_com"
"inv_prof";"inv_encor"
"inv_prof";"inv_ho"
"inv_prof";"inv_pd"
"inv_prof";"inv_ut"

给定一个孩子的名字，我怎么能列出所有的上升者呢？给定一个父名称，我怎么能列出它的所有后代呢？

Answer 1

我发布了解决方案，以便它可以帮助面临类似问题的人。

WITH RECURSIVE source(main ,sub ) AS (
SELECT character varying 'inv_ho', character varying 'inv_ho_r'
UNION ALL
SELECT tb_relation_investor.c_relation_investor_main_pfk, tb_relation_investor.c_relation_investor_sub_pfk
FROM source JOIN tb_relation_investor ON (source.main=tb_relation_investor.c_relation_investor_sub_pfk)
 )
 SELECT * from source