DuckDB supports the dot syntax for function chaining. This allows the function call fn(arg1, arg2, arg3, ...) to be rewritten as arg1.fn(arg2, arg3, ...). For example, take the following use of the replace function:
SELECT replace(goose_name, 'goose', 'duck') AS duck_name
FROM unnest(['African goose', 'Faroese goose', 'Hungarian goose', 'Pomeranian goose']) breed(goose_name);
This can be rewritten as follows:
SELECT goose_name.replace('goose', 'duck') AS duck_name
FROM unnest(['African goose', 'Faroese goose', 'Hungarian goose', 'Pomeranian goose']) breed(goose_name);
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)