/*
sql for creating fts for region table
*/

ALTER TABLE region ADD COLUMN name_vector tsvector;
UPDATE region SET name_vector = to_tsvector(
coalesce(name_ka, '') || ' ' || coalesce(name_sq, '') || ' ' || coalesce(name_ur, '') || ' ' || coalesce(name_sw, '') || ' ' || coalesce(name_ta, '') || ' ' || coalesce(name_sl, '') || ' ' || coalesce(name_tl, '') || ' ' || coalesce(name_fa, '') || ' ' || coalesce(name_he, '') || ' ' || coalesce(name_uk, '') || ' ' || coalesce(name_el, '') || ' ' || coalesce(name_sr, '') || ' ' || coalesce(name_hr, '') || ' ' || coalesce(name_vi, '') || ' ' || coalesce(name_lt, '') || ' ' || coalesce(name_lv, '') || ' ' || coalesce(name_et, '') || ' ' || coalesce(name_ca, '') || ' ' || coalesce(name_ro, '') || ' ' || coalesce(name_hu, '') || ' ' || coalesce(name_sv, '') || ' ' || coalesce(name_bg, '') || ' ' || coalesce(name_th, '') || ' ' || coalesce(name_tr, '') || ' ' || coalesce(name_id, '') || ' ' || coalesce(name_hi, '') || ' ' || coalesce(name_ko, '') || ' ' || coalesce(name_ar, '') || ' ' || coalesce(name_ct, '') || ' ' || coalesce(name_zh, '') || ' ' || coalesce(name_cs, '') || ' ' || coalesce(short_name, '') || ' ' || coalesce(name_da, '') || ' ' || coalesce(name_pl, '') || ' ' || coalesce(name_it, '') || ' ' || coalesce(name_ru, '') || ' ' || coalesce(name_ja, '') || ' ' || coalesce(name_nl, '') || ' ' || coalesce(name_fi, '') || ' ' || coalesce(name_pt, '') || ' ' || coalesce(name_es, '') || ' ' || coalesce(name_fr, '') || ' ' || coalesce(name_de, '') || ' ' || coalesce(name_en, ''));
CREATE INDEX region_name_vector_idx ON region USING gin(name_vector);
/*
missing: the trigger
*/

/*

these are just helpers to get the relevant column names is specific format

-- for the update sql
SELECT string_agg('coalesce(' || column_name || ', '''')', ' || '' '' || ')
FROM information_schema.columns
WHERE table_name='region'
AND column_name LIKE '%name%'
AND NOT column_name = 'name_vector'

-- for the trigger
SELECT string_agg(column_name, ',')
FROM information_schema.columns
WHERE table_name='region'
AND column_name LIKE '%name%'
AND NOT column_name = 'name_vector'
*/