やりたいこと

select * from my_table where id = ?で取得しつつ、値を一部書き換えたい
select my_column1, my_column2...(以下略) from my_table where id = ?のような列挙形式ではない

ORMを使っていたり、副問い合わせのネストが深い場所で一部だけ値を上書きしたけどいちいちカラム名を列挙していくのが面倒、といったケースが偶にある。

もちろん、SQLはそんな言語じゃないとか、バグの温床だとか、速度やメモリが云々の指摘自体は全く以って正しいとは思うが、面倒なものは面倒だった。それの解消方法をメモ的に残しておく。

実装

テーブル状況

select * from item

"code","price","tax_code"
"A","100","1"
"B","200","2"

シンプルSQL

select
  new_item.*
from
  item, jsonb_populate_record(item.*, '{"code":"hoge"}'::jsonb) new_item
where
  item.code = 'A'

"code","price","tax_code"
"hoge","100","1"

当然、カラムの数は3つになるのでSQLの先頭行にinsert into itemと記述すればInsertにも使える。

SQLさえ弄れば応用も効くはず。速度は知らないが、簡単な要素であればtemporary tableも不要だろう。

select
  new_item.*
from
  item
cross join
  (select generate_series(1, 5, 1) as no) gs
cross join lateral
  (select 'code_' || gs.no as code) json_material
cross join lateral
  (select (jsonb_populate_record(item.*, to_jsonb(json_material.*))).*) new_item
where item.code = 'A'

"code","price","tax_code"
"code_1","100","1"
"code_2","100","1"
"code_3","100","1"
"code_4","100","1"
"code_5","100","1"

雑感

今触っているプロジェクトが、複数ファイルに分割されたSQLの断片があり、それを動的に文字列結合して実行…みたいなイメージで実装されている。もう少しちゃんとしているが。 left join itemみたいになっているもののレコードセットのうち、一部の値を書き換えたりしたい。だが、普通に書くとカラム名を列挙せざるを得ない。列が増えたらどうする？みたいな葛藤を解決してくれる関数だった。素晴らしい。

公式ページを読んでこの使い方が想像できず、json_to_recordの方を使って四苦八苦していた。結局型情報が必要なのかと諦めてinformation_schema.columnsを使って動的SQLを実行してplpgsqlでreturns itemみたいなことをしようかとすら考えていた。二つ目の参考記事でこれらの使い方が書いてあって助かった。