sql - 从数组创建字符串

Question

我在 PostgreSQL 中有一个表，其中包含:

id   name   arrayofparents
1     First
2     Second      {1}
3     Second_Sec  {1,2}
4     Third       {1,2,3}
5     Second_A    {1}
6     Other
7     Trash       {6}

arrayofparents 是 integer[] 类型，它包含该行的 parent 记录列表，顺序正确。

id=4 parent 是:First 然后是 Second 然后是 Second_sec

我如何编写一个查询，为任何给定的 ID 生成一个包含其 parent 姓名的字符串？

例如:

id=3:First->Second。

id=4:First->Second->Second_sec。

id=7:其他。

编辑:如果可能的话，我希望请求的 id name 总是出现。id=3: First->Second->Second_sec.

id=4:First->Second->Second_sec->Third。

id=7:Other->Trash。

id=6:其他。

Answer 1

您可以结合 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]
  ), '->'
) ;

live example Sqlfiddle

或使用外连接结合任何:

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

live example Sqlfiddle