使用数值类型的数组进行SQL递归查询

Question

我尝试运行一个递归查询来获得最短路径，其中id最多在路径中出现一次。我使用的视图如下所示：

pkp_symmetric(
    personknows numeric(20,0),
    personisknown numeric(20,0),
    creation timestamp)

运行时

with recursive temp(persStart, persNext, pfad, tiefe, pcycle ) 
            as 
            (select pkp.personknows, pkp.personIsKnown, array[pkp.personKnows], 1, false 
            from pkp_symmetric pkp--pidstart, pidstart, pidstart
            union all 
            select p.personknows, p.personisknown, t.pfad|| t.persNext, t.tiefe + 1, p.personknows = ANY(t.pfad)  
            from pkp_symmetric p join  temp t 
            on p.personknows = t.persNext where not pcycle )

            select * from temp t 

我得到以下错误：

SQL Error [42804]: ERROR: recursive query "temp" column 3 has type numeric(20,0)[] in non-recursive term but type numeric[] overall
  Hinweis: Cast the output of the non-recursive term to the correct type.
  Position: 119
  SQL Error [42804]: ERROR: recursive query "temp" column 3 has type numeric(20,0)[] in non-recursive term but type numeric[] overall
  Hinweis: Cast the output of the non-recursive term to the correct type.
  Position: 119
    SQL Error [42804]: ERROR: recursive query "temp" column 3 has type numeric(20,0)[] in non-recursive term but type numeric[] overall
  Hinweis: Cast the output of the non-recursive term to the correct type.
  Position: 119
      SQL Error [42804]: ERROR: recursive query "temp" column 3 has type numeric(20,0)[] in non-recursive term but type numeric[] overall
  Hinweis: Cast the output of the non-recursive term to the correct type.
  Position: 119
        ERROR: recursive query "temp" column 3 has type numeric(20,0)[] in non-recursive term but type numeric[] overall
  Hinweis: Cast the output of the non-recursive term to the correct type.
  Position: 119
        ERROR: recursive query "temp" column 3 has type numeric(20,0)[] in non-recursive term but type numeric[] overall
  Hinweis: Cast the output of the non-recursive term to the correct type.
  Position: 119

如果能得到一些帮助我将不胜感激。诚挚的问候。

Answer 1

在数组中处理有界数据类型有点棘手。这似乎是让它工作的唯一方法是一个笨拙的：

select pkp.personknows, pkp.personisknown, array[]::numeric[] || pkp.personknows
...

在非递归部分。

我不知道为什么arraypersonknows::numeric[]不能实现同样的功能。