WITH hira_to_kata as MATERIALIZED (
SELECT hira, kata
FROM (
SELECT 'あ' as hira, 'ア' as kata
UNION SELECT 'い' as hira, 'イ' as kata
UNION SELECT 'う' as hira, 'ウ' as kata
UNION SELECT 'え' as hira, 'エ' as kata
UNION SELECT 'お' as hira, 'オ' as kata
UNION SELECT 'か' as hira, 'カ' as kata
UNION SELECT 'き' as hira, 'キ' as kata
UNION SELECT 'く' as hira, 'ク' as kata
UNION SELECT 'け' as hira, 'ケ' as kata
UNION SELECT 'こ' as hira, 'コ' as kata
UNION SELECT 'さ' as hira, 'サ' as kata
UNION SELECT 'し' as hira, 'シ' as kata
UNION SELECT 'す' as hira, 'ス' as kata
UNION SELECT 'せ' as hira, 'セ' as kata
UNION SELECT 'そ' as hira, 'ソ' as kata
UNION SELECT 'た' as hira, 'タ' as kata
UNION SELECT 'ち' as hira, 'チ' as kata
UNION SELECT 'つ' as hira, 'ツ' as kata
UNION SELECT 'て' as hira, 'テ' as kata
UNION SELECT 'と' as hira, 'ト' as kata
UNION SELECT 'な' as hira, 'ナ' as kata
UNION SELECT 'に' as hira, 'ニ' as kata
UNION SELECT 'ぬ' as hira, 'ヌ' as kata
UNION SELECT 'ね' as hira, 'ネ' as kata
UNION SELECT 'の' as hira, 'ノ' as kata
UNION SELECT 'は' as hira, 'ハ' as kata
UNION SELECT 'ひ' as hira, 'ヒ' as kata
UNION SELECT 'ふ' as hira, 'フ' as kata
UNION SELECT 'へ' as hira, 'ヘ' as kata
UNION SELECT 'ほ' as hira, 'ホ' as kata
UNION SELECT 'ま' as hira, 'マ' as kata
UNION SELECT 'み' as hira, 'ミ' as kata
UNION SELECT 'む' as hira, 'ム' as kata
UNION SELECT 'め' as hira, 'メ' as kata
UNION SELECT 'も' as hira, 'モ' as kata
UNION SELECT 'や' as hira, 'ヤ' as kata
UNION SELECT 'ゆ' as hira, 'ユ' as kata
UNION SELECT 'よ' as hira, 'ヨ' as kata
UNION SELECT 'ら' as hira, 'ラ' as kata
UNION SELECT 'り' as hira, 'リ' as kata
UNION SELECT 'る' as hira, 'ル' as kata
UNION SELECT 'れ' as hira, 'レ' as kata
UNION SELECT 'ろ' as hira, 'ロ' as kata
UNION SELECT 'わ' as hira, 'ワ' as kata
UNION SELECT 'を' as hira, 'ヲ' as kata
UNION SELECT 'ん' as hira, 'ン' as kata
UNION SELECT 'ー' as hira, 'ー' as kata
UNION SELECT 'が' as hira, 'ガ' as kata
UNION SELECT 'ぎ' as hira, 'ギ' as kata
UNION SELECT 'ぐ' as hira, 'グ' as kata
UNION SELECT 'げ' as hira, 'ゲ' as kata
UNION SELECT 'ご' as hira, 'ゴ' as kata
UNION SELECT 'ざ' as hira, 'ザ' as kata
UNION SELECT 'じ' as hira, 'ジ' as kata
UNION SELECT 'ず' as hira, 'ズ' as kata
UNION SELECT 'ぜ' as hira, 'ゼ' as kata
UNION SELECT 'ぞ' as hira, 'ゾ' as kata
UNION SELECT 'だ' as hira, 'ダ' as kata
UNION SELECT 'ぢ' as hira, 'ヂ' as kata
UNION SELECT 'づ' as hira, 'ヅ' as kata
UNION SELECT 'で' as hira, 'デ' as kata
UNION SELECT 'ど' as hira, 'ド' as kata
UNION SELECT 'ば' as hira, 'バ' as kata
UNION SELECT 'び' as hira, 'ビ' as kata
UNION SELECT 'ぶ' as hira, 'ブ' as kata
UNION SELECT 'べ' as hira, 'ベ' as kata
UNION SELECT 'ぼ' as hira, 'ボ' as kata
UNION SELECT 'ぱ' as hira, 'パ' as kata
UNION SELECT 'ぴ' as hira, 'ピ' as kata
UNION SELECT 'ぷ' as hira, 'プ' as kata
UNION SELECT 'ぺ' as hira, 'ペ' as kata
UNION SELECT 'ぽ' as hira, 'ポ' as kata
)
),
使い方はこうでござる。
いや、まあ、うん、普通にプログラム書けってかんじだ。
WITH RECURSIVE kata as (
SELECT
'' as kata,
0 as index
UNION
SELECT
CONCAT(kata.kata, hira_to_kata.kata) as kata,
kata.index + 1 as index
FROM given, kata, hira_to_kata
WHERE true
AND given.text[kata.index + 1] = hira_to_kata.hira
)
SELECT kata
FROM kata, given
WHERE true
AND kata.index = LENGTH(given.text)