このページの内容
親子関係のあるレコードをもつテーブル
例えば、下記のようなテーブル(テーブル名=message_threads)を想定します。メールや掲示板のスレッドのようなものをイメージすればわかりやすいと思います。
メッセージID(serial型:mes_id) | 返信先メッセージID(serial型:parent_mes_id) | メッセージタイトル(text型:mes_title) |
---|---|---|
1 | 0 | ほげほげ |
2 | 1 | Re:ほげほげ その1 |
3 | 1 | Re:ほげほげ その2 |
4 | 2 | Re:Re:ほげほげ その1 |
5 | 0 | ふがふが |
※ 0 はルートメッセージを指すものとする。
このメッセージIDと返信先メッセージIDを、serial型からuuid型に変換したいと思います。
考え方
- メッセージID=1のレコードに対して uuid が決まらないと、返信先メッセージID=1 のレコードの uuid が決まらないため、まずメッセージIDに対して uuid を決める。
- メッセージIDに対して振られた uuid で、返信先メッセージIDに対して振るべき uuid が決まる。
- 返信先メッセージID=0 は、ルートメッセージであるため、uuid は振らなくてよい。
SQL(Insert-Select)
uuid値は uuid_generate_v4() 関数を使って採番することができます。この関数がエラーになった場合は、補足の項を参考にしてください。
with message_threads_uuid as ( -- すべてのレコードに uuid を付与 select * , uuid_generate_v4() as message_uuid from message_threads ), message_threads_with_parent_uuid as ( -- メッセージIDと返信先メッセージIDを紐づけて、返信先メッセージの uuid を決定 select child.mes_id , child.message_uuid as my_uuid , parent.message_uuid as parent_uuid from message_threads_uuid as child , message_threads_uuid as parent where child.parent_mes_id = parent.mes_id ), message_threads_temp as ( -- 返信先メッセージIDが紐づかないレコードには、uuid を振らない(= uuid_nil() とする) select main.message_uuid , coalesce(sub.parent_uuid, uuid_nil()) parent_message_uuid , main.mes_title from message_threads_uuid as main left join message_threads_with_parent_uuid as sub on main.mes_id = sub.mes_id ) insert into message_threads_conv select message_uuid , parent_message_uuid , mes_title from message_thread_temp
補足:function uuid_generate_v4() does not exist
「function uuid_generate_v4() does not exist」というエラーが発生した場合、下記のSQLを実行することで UUID関連の関数を使用できるようにしてください。
create extension if not exists "uuid-ossp"