{"id":1281,"date":"2014-11-18T09:42:30","date_gmt":"2014-11-18T01:42:30","guid":{"rendered":"http:\/\/blog.qdac.cc\/?p=1281"},"modified":"2014-11-19T09:18:26","modified_gmt":"2014-11-19T01:18:26","slug":"postgresql%e8%8e%b7%e5%8f%96%e6%b1%89%e5%ad%97%e6%8b%bc%e9%9f%b3%e9%a6%96%e5%ad%97%e6%af%8d%e5%87%bd%e6%95%b0","status":"publish","type":"post","link":"https:\/\/blog.qdac.cc\/?p=1281","title":{"rendered":"[PostgreSQL]\u83b7\u53d6\u6c49\u5b57\u62fc\u97f3\u9996\u5b57\u6bcd\u51fd\u6570"},"content":{"rendered":"<p>\u5728\u5ea6\u5a18\u65e0\u8bed\uff0c\u8c37\u54e5\u5931\u806a\u7684\u60c5\u51b5\u4e0b\uff0c\u4e0d\u5f97\u5df2\u5c06\u81ea\u5df1\u539f\u6765\u5199\u7684\u4e00\u4e2a\u83b7\u53d6\u6c49\u5b57\u9996\u5b57\u6bcd\u7684C++\u6e90\u7801\u8f6c\u6362\u4e3a\u4e86PostgreSQL\uff08\u4e3a\u4ec0\u4e48\u8981\u83b7\u53d6\u6c49\u5b57\u9996\u5b57\u6bcd\uff1f\u82cd\u5929\u5440\u5927\u5730\uff0c\u81ea\u5df1\u60f3\u60f3\u53bb\u5427\uff09\uff0c\u5728PostgreSQL 9.3\u4e2d\u6d4b\u8bd5\u6ca1\u6709\u95ee\u9898\uff0c\u6570\u636e\u5e93\u4f7f\u7528\u7684\u662fUTF-8\u7f16\u7801\u3002\u5728\u8fd9\u91cc\u5206\u4eab\u51fa\u6765\uff0c\u4f9b\u5927\u5bb6\u53c2\u8003\uff1a<\/p>\n<pre class=\"lang:pgsql decode:true\">--\u00a9 2014,swish\uff0c\u539f\u7248\u9996\u53d1\uff1ahttps:\/\/blog.qdac.cc\/?p=1281\uff0c\u81ea\u7531\u4f7f\u7528\uff0c\u4fdd\u7559\u7248\u6743\r\nCREATE OR REPLACE FUNCTION CnFirstChar(s character varying)\r\n  RETURNS character varying AS\r\n$BODY$\r\ndeclare\r\n  retval character varying;\r\n  c character varying;\r\n  l integer;\r\n  b bytea;  \r\n  w integer;\r\nbegin\r\nl=length(s);\r\nretval='';\r\nwhile l&gt;0 loop\r\n  c=left(s,1);\r\n  b=convert_to(c,'GB18030')::bytea;\r\n  if get_byte(b,0)&lt;127 then\r\n    retval=retval || upper(c);\r\n  elsif length(b)=2 then\r\n    begin\r\n    w=get_byte(b,0)*256+get_byte(b,1);\r\n    --\u6c49\u5b57GBK\u7f16\u7801\u6309\u62fc\u97f3\u6392\u5e8f\uff0c\u6309\u5b57\u7b26\u6570\u6765\u67e5\u627e\uff0c\u57fa\u4e8e\u6982\u7387\u6765\u8bf4\uff0c\u6548\u7387\u5e94\u8be5\u6bd4\u6328\u4e2a\u5f3a:)\r\n    if w between 48119 and 49061 then --\"J\";48119;49061;942\r\n      retval=retval || 'J';\r\n    elsif w between 54481 and 55289 then --\"Z\";54481;55289;808\r\n      retval=retval || 'Z';\r\n    elsif w between 53689 and 54480 then --\"Y\";53689;54480;791\r\n      retval=retval || 'Y';\r\n    elsif w between 51446 and 52208 then --\"S\";51446;52208;762\r\n      retval=retval || 'S';\r\n    elsif w between 52980 and 53640 then --\"X\";52980;53640;660\r\n      retval=retval || 'X';\r\n    elsif w between 49324 and 49895 then --\"L\";49324;49895;571\r\n      retval=retval || 'L';\r\n    elsif w between 45761 and 46317 then --\"C\";45761;46317;556\r\n      retval=retval || 'C';\r\n    elsif w between 45253 and 45760 then --\"B\";45253;45760;507\r\n      retval=retval || 'B';\r\n    elsif w between 46318 and 46825 then --\"D\";46318;46825;507\r\n      retval=retval || 'D';\r\n    elsif w between 47614 and 48118 then --\"H\";47614;48118;504\r\n      retval=retval || 'H';\r\n    elsif w between 50906 and 51386 then --\"Q\";50906;51386;480\r\n      retval=retval || 'Q';\r\n    elsif w between 52218 and 52697 then --\"T\";52218;52697;479\r\n      retval=retval || 'T';\r\n    elsif w between 49896 and 50370 then --\"M\";49896;50370;474\r\n      retval=retval || 'M';\r\n    elsif w between 47297 and 47613 then --\"G\";47297;47613;316\r\n      retval=retval || 'G';\r\n    elsif w between 47010 and 47296 then--\"F\";47010;47296;286\r\n      retval=retval || 'F';\r\n    elsif w between 50622 and 50905 then--\"P\";50622;50905;283\r\n      retval=retval || 'P';\r\n    elsif w between 52698 and 52979 then--\"W\";52698;52979;281\r\n      retval=retval || 'W';\r\n    elsif w between 49062 and 49323 then--\"K\";49062;49323;261\r\n      retval=retval || 'K';\r\n    elsif w between 50371 and 50613 then --\"N\";50371;50613;242\r\n      retval=retval || 'N';\r\n    elsif w between 46826 and 47009 then--\"E\";46826;47009;183\r\n      retval=retval || 'E';\r\n    elsif w between 51387 and 51445 then--\"R\";51387;51445;58\r\n      retval=retval || 'R';\r\n    elsif w between 45217 and 45252 then --\"A\";45217;45252;35\r\n      retval=retval || 'A';\r\n    elsif w between 50614 and 50621 then --\"O\";50614;50621;7\r\n      retval=retval || 'O';\r\n    end if;\r\n    end;\r\n  end if;\r\n  s=substring(s,2,l-1);\r\n  l=l-1;\r\nend loop;\r\nreturn retval;\r\nend;\r\n$BODY$\r\n  LANGUAGE plpgsql IMMUTABLE;<\/pre>\n<p>\u8c03\u7528\u7684\u8bdd\uff0c\u5f88\u7b80\u5355\uff0c\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<pre class=\"lang:pgsql decode:true \">select CnFirstChar('\u4e0d\u77e5\u9053\u662f\u4ec0\u4e48\u539f\u56e0');<\/pre>\n<p>\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n<p><a href=\"https:\/\/blog.qdac.cc\/wp-content\/uploads\/2014\/11\/PgCnFirstChar.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1282\" src=\"https:\/\/blog.qdac.cc\/wp-content\/uploads\/2014\/11\/PgCnFirstChar.jpg\" alt=\"PgCnFirstChar\" width=\"221\" height=\"104\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5728\u5ea6\u5a18\u65e0\u8bed\uff0c\u8c37\u54e5\u5931\u806a\u7684\u60c5\u51b5\u4e0b\uff0c\u4e0d\u5f97\u5df2\u5c06\u81ea [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[4],"tags":[16,178,179],"class_list":["post-1281","post","type-post","status-publish","format-standard","hentry","category-pgsql","tag-16","tag-178","tag-179"],"views":7912,"_links":{"self":[{"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=\/wp\/v2\/posts\/1281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1281"}],"version-history":[{"count":7,"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=\/wp\/v2\/posts\/1281\/revisions"}],"predecessor-version":[{"id":1293,"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=\/wp\/v2\/posts\/1281\/revisions\/1293"}],"wp:attachment":[{"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.qdac.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}