从数组创建字符串

您可以结合使用诸如generate_subscripts和array之类的多项 *** 作来获得结果：

with mtab as (      SELECt id, name, array_append(arrayofparents,id) as arrayofparents,      generate_subscripts(array_append(arrayofparents, id), 1) AS p_id FROM tab where id=2)select distinct array_to_string(  array(    select tab.name from tab join mtab t on tab.id=t.arrayofparents[t.p_id]  ), '->') ;

实时示例Sqlfiddle

或将外部联接与以下任何一项结合使用：

SELECt coalesce(string_agg(p.name, '->') || '->' || t.name, t.name) AS parentnamesFROM tab AS t  LEFT JOIN tab AS p ON p.id = ANY(t.arrayofparents) where t.id =7 GROUP BY t.id, t.name

实时示例Sqlfiddle