Na jednom z menších (ale o to zaujímavejších) projektov sme riešili zaujímavú požiadavku. Zákazníkom bola firma z Ruska a požiadavkou bolo vyhľadávať v číselníku obcí tak, aby vyhľadávanie vrátilo správne výsledky pri zadaní názvu obce v cyrilike aj v latinke. Ak by sme teda hľadali medzi obcami napr. ‘Mосква’ musíme dostať rovnaké výsledky, ako keď hľadáme ‘Moskva’. Inými slovami, musíme vyhľadávať aj v názve v cyrilike aj v názve, ktorý získame transliteráciou z cyriliky.
Najjednoduchším riešením by skutočne bolo pridať ku každému názvu obce ešte jeho názov v latinke a pri vyhľadávaní spraviť dopyt tak, aby sa vyhľadávalo v oboch databázových stĺpcoch. Optimálnejším riešením by ale bolo vyhľadávať iba v názve v latinke s tým, že dopyt od klienta najskôr programaticky transliterujeme do latinky a následne vyhľadávame už len v jednom stĺpci. Ako však na samotnú transliteráciu? PostgreSQL natívne transliteráciu neobsahuje, ale nič nám nebráni funkciu na transliteráciu z cyriliky do latinky napísať. Pravidlá transliterácie sú jednoduché. Ide v princípe len o nahradenie znakov na iné (n-tice 1-3) znaky. Samotná funkcia na transliteráciu je naozaj jednoduchá:
CREATE OR REPLACE FUNCTION cyrillic_transliterate(p_string text)
RETURNS character varying AS $BODY$
SELECT replace( replace( replace( replace(
replace( replace( replace( replace( translate(lower($1),
'абвгдеёзийклмнопрстуфхцэы', 'abvgdeezijklmnoprstufхcey'), 'ж', 'zh'),
'ч', 'ch'), 'ш', 'sh'), 'щ', 'shh'), 'ъ', ''), 'ю', 'yu'), 'я', 'ya'), 'ь', '');
$BODY$
LANGUAGE SQL IMMUTABLE COST 100;
ALTER FUNCTION cyrillic_transliterate(p_string text) OWNER TO blog;Použitie funkcie je potom jednoduché. V našom prípade (keďže ide o statický číselník) sme si vytvorili nasledovný materialized view:
CREATE MATERIALIZED VIEW MV_CITYREGION AS
SELECT
city.id AS cityId,
city.name AS cityName,
city.population AS population,
region.id AS regionId,
region.name AS regionName,
lower(cyrillic_transliterate(city.name)) AS cityTransliterated,
city.name || ' (' || region.name || ')' AS cityRegion
FROM city
INNER JOIN region ON city.regionid = region.id
ORDER BY city.name, region.name, city.id;V ktorom potom vyhľadávame nasledovne:
select
cr.cityName,
cr.cityTransliterated,
cr.cityregion
from MV_CITYREGION cr
where
cityTransliterated = 'moskva'
order by
cr.population desc,
cr.cityname asc; cityname | citytransliterated | cityregion
----------+--------------------+----------------------------
Москва | moskva | Москва (Москва)
Москва | moskva | Москва (Кировская область)
Москва | moskva | Москва (Псковская область)
Москва | moskva | Москва (Тверская область)
(4 rows)Ďalšou možnosťou je napr. vytvorenie expression index-u s použitím našej funkcie transliterácie z cyriliky.