2012年3月 1日

[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@xxxxx
Tel : 03-5951-1219
Fax : 03-5951-2929


投稿者 xml-rpc : 2012年3月 1日 15:50
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/108962
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。