CREATE OR REPLACE FUNCTION "gis"."get_hzpycap"("v_str" varchar, "needhz" int4=0)
RETURNS "pg_catalog"."varchar" AS $BODY$
DECLARE
pos INT4;
hzlen INT4;
hz VARCHAR (10);
hzbm INT4;
hzpy VARCHAR (10);
py VARCHAR (4000);
hasspace INT4;
hzpybm_0 = 'xxx';
hzpybm_1 = 'xxx';
hzpybm_2 = 'xxx';
... 具体见附件
BEGIN
hzlen = NVL (LENGTH (v_str), 0);
hasspace = 0;
FOR pos IN 1 .. hzlen
LOOP
hz = SUBSTR (v_str, pos, 1);
hzbm = gis.bytea_to_int(convert_to(hz,'gbk'));
IF hzbm BETWEEN 0 AND 127
THEN
hz = UPPER(hz); --大写, 并只允许 0-9 和 A-Z 字符
IF (hz >= '0' AND hz <= '9') OR (hz >='A' AND hz <= 'Z') THEN
py = CONCAT(py, hz);
IF needHZ=1 THEN py = CONCAT(py, hz); END IF;
END IF;
ELSE
BEGIN
hzbm = hzbm - 32767;
--少减1, 因为下面使用字符串定位, 起始基址为1
CASE
WHEN hzbm < 0
THEN
hzpy = '';
WHEN hzbm < 3500
THEN
hzpy = SUBSTR (hzpybm_0, hzbm, 1);
WHEN hzbm < 7000
THEN
hzpy = SUBSTR (hzpybm_1, hzbm - 3500, 1);
WHEN hzbm < 10500
THEN
hzpy = SUBSTR (hzpybm_2, hzbm - 7000, 1);
WHEN hzbm < 14000
THEN
hzpy = SUBSTR (hzpybm_3, hzbm - 10500, 1);
WHEN hzbm < 17500
THEN
hzpy = SUBSTR (hzpybm_4, hzbm - 14000, 1);
WHEN hzbm < 21000
THEN
hzpy = SUBSTR (hzpybm_5, hzbm - 17500, 1);
WHEN hzbm < 24500
THEN
hzpy = SUBSTR (hzpybm_6, hzbm - 21000, 1);
WHEN hzbm < 28000
THEN
hzpy = SUBSTR (hzpybm_7, hzbm - 24500, 1);
WHEN hzbm < 31500
THEN
hzpy = SUBSTR (hzpybm_8, hzbm - 28000, 1);
WHEN hzbm < 35000
THEN
hzpy = SUBSTR (hzpybm_9, hzbm - 31500, 1);
ELSE
hzpy = '';
END CASE;
IF hzpy <> ' ' THEN
py = CONCAT(py, hzpy);
IF needHZ=1 THEN py = CONCAT(py, hz); END IF;
END IF;
END;
END IF;
END LOOP;
RETURN py;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE COST 100
;
ALTER FUNCTION "gis"."get_hzpycap"("v_str" varchar, "needhz" int4) OWNER TO "meter";
