postgres汉字转换为拼音

数据

--汉字和拼音以及拼音首字母的对照表
create table pinyin (hz varchar(1),py varchar(6),zm varchar(1));
--未收录汉字插入以下表
create table new_discover (hz varchar(1) primary key,py varchar(6),zm varchar(1));

函数

--创建输出type和函数,函数返回是数组
CREATE TYPE t_py_zm as (c1 text[],c2 text[]);
CREATE OR REPLACE FUNCTION get_py_zm(i_hz text)
  RETURNS SETOF t_py_zm AS
$BODY$
DECLARE
v_hz text;
i int;
v_sql1 text;
v_sql2 text;
v_sql3 text;
v_sql4 text;
v_sql text;
v_max_id int;
v_id int;
BEGIN
--创建临时表用来存储每个汉字和字母
set client_min_messages = warning;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_get_py_zm (id int,py varchar(6),zm varchar(1)) ON COMMIT DELETE ROWS;
truncate table tmp_get_py_zm;
i := 0;

--拆分输入参数为每个字符并插入到临时表里
for v_hz in select regexp_split_to_table(i_hz,'') loop
  if ascii(v_hz) > 255 then
    insert into tmp_get_py_zm select i,py,zm from pinyin where hz=v_hz;
  else
    insert into tmp_get_py_zm values(i,v_hz,v_hz);
  end if;
  if not found then
    perform 1 from new_discover where hz = v_hz;
    if not found then
      insert into new_discover(hz) values(v_hz);
    end if;
    insert into tmp_get_py_zm values(i,'?','?');
  end if;
  i := i+1;
end loop;

select max(id) into v_max_id from tmp_get_py_zm;
if v_max_id > 0 then
  v_sql1 := '';
  v_sql3 := '';
  v_sql4 := '';
  v_id := 0;
  for v_id in select generate_series(0,v_max_id) loop
    if v_id <> v_max_id then
      v_sql1 := v_sql1||'(select py,zm from tmp_get_py_zm where id='||v_id||') as t'||v_id||',';
      v_sql3 := v_sql3||'t'||v_id||'.py::text||';
      v_sql4 := v_sql4||'t'||v_id||'.zm::text||';
    else
      v_sql1 := v_sql1||'(select py,zm from tmp_get_py_zm where id='||v_id||') as t'||v_id;
      v_sql3 := v_sql3||'t'||v_id||'.py::text';
      v_sql4 := v_sql4||'t'||v_id||'.zm::text';
      v_sql := 'select array_agg('||v_sql3||'),array_agg('||v_sql4||') from '||v_sql1;
    end if;
  end loop;
else
  v_sql := 'select array_agg(py::text),array_agg(zm::text) from tmp_get_py_zm';
end if;

return query execute v_sql;
return;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION get_py_zm(text)
  OWNER TO postgres;

导入数据

pinyin词库表整理在了下面的云盘地址里，目前收录了将近7000个常用汉字，地址在： http://pan.baidu.com/s/1pJ6spSn 导入方式
[postgres@db1 ~]$ psql
psql (9.2.4)
Type "help" for help.
postgres=# \i /home/postgres/py.sql

测试

postgres=# select * from get_py_zm('行为');
        c1         |   c2    
-------------------+---------
 {HANGWEI,XINGWEI} | {HW,XW}
(1 row)

postgres=# select * from get_py_zm('你好');
   c1    |  c2  
---------+------
 {NIHAO} | {NH}
(1 row)

postgres=# select * from get_py_zm('你hao');
   c1    |   c2   
---------+--------
 {NIhao} | {Nhao}
(1 row)

参考:
https://my.oschina.net/Kenyon/blog/284161
https://github.com/digoal/blog/blob/master/201205/20120515_01.md