SQL recursive querying using array with numeric type

Question

I try to run a recursive query to get shortest paths where the id should occure once in a path at most. The view I am using looks like:

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

When running

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

I get the following error:

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

I would appreciate to get some help. Best regards.

score 1 · Accepted Answer · 2018-08-24 11:24:44Z

1

Dealing with bounded data types in arrays is a bit tricky. It seems the only way to get this to work is a clumsy:

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

in the non-recursive part.

I have no idea why array[personknows]::numeric[] doesn't achieve the same thing.

edited Aug 24, 2018 at 11:24

answered Aug 24, 2018 at 11:16

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Karsten Schreiblehner Over a year ago

I tried, but this does not work for me. I still get the same error.

Collectives™ on Stack Overflow

SQL recursive querying using array with numeric type

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related