[pgsql-jp: 41061] Re: 再起的なSQLで配列の型について

[花田 茂]

花田です。

(2012/03/01 13:41), apostleofwhom wrote:
> id が text 型や varchar だとエラーにならないのですが、
> id を varchar(12) や varchar(64) にすると、
> 
> ERROR:  recursive query "rec" column 4 has type character varying(12)[]
> in non-recursive term but type character varying[] overall
> LINE 2:     select t.id, t.parentid, 1, array[t.id]
> 
> とエラーになります。SQL は以下となります。

エラーメッセージによると、UNION ALL の上下で path 列の型が精度情報のみ異
なっているので、エラーになっています。精度なしの varchar と varchar(12)
は、別の型という扱いです。|| 演算子で連結すると、精度情報が落ちてしまう
ようですね。

なお、文字列型の使い分けは Let's postgres にわかりやすい記事[1]がありま
すので、未読でしたらぜひ。
[1] http://lets.postgresql.jp/documents/technical/text-processing/1/

> with recursive rec(id, parentid, lv, path) as (
>      select t.id, t.parentid, 1, array[t.id]
>      from tree t
>      where t.parentid is null
>      union all
>      select t.id, t.parentid, rec.lv + 1, rec.path || t.id
>      from tree t, rec
>      where t.parentid = rec.id
> )
> select id, parentid, lv, path
> from rec order by path;
> 
> id を varchar(12) にしてもエラーにならないようにするには
> どうすればよろしいでしょうか。

path 列の型が varying[] 型でよければ、ARRAY[] や || を array_append() に
統一すれば OK のようです。

with recursive rec(id, parentid, lv, path) as (
    select t.id, t.parentid, 1, array_append(NULL, t.id)　path
    from tree t
    where t.parentid is null
    union all
    select t.id, t.parentid, rec.lv + 1, array_append(rec.path, t.id)
    from tree t, rec
    where t.parentid = rec.id
)
select id, parentid, lv, path
from rec order by path;

【結果】
 id  | parentid | lv |       path
-----+----------+----+-------------------
 001 |          |  1 | {001}
 002 | 001      |  2 | {001,002}
 004 | 002      |  3 | {001,002,004}
 005 | 002      |  3 | {001,002,005}
 003 | 001      |  2 | {001,003}
 006 | 003      |  3 | {001,003,006}
 007 | 006      |  4 | {001,003,006,007}
 008 | 006      |  4 | {001,003,006,008}
 050 |          |  1 | {050}
 055 | 050      |  2 | {050,055}
 056 | 050      |  2 | {050,056}
 058 | 056      |  3 | {050,056,058}
(12 rows)

もし path 列の型を varying(12)[] にしたい場合は、
    array_append(rec.path, t.id)::varchar(12)[]
のように array_append() の結果を UNION ALL の上下の両方キャストしてくだ
さい。

-- 
株式会社メトロシステムズ
  花田 茂
Mail : hanada ＠ metrosystems.co.jp
 Tel : 03-5951-1219
 Fax : 03-5951-2929