递归转循环实现ST_GeoHashAdjacent函数出错，求解决方案

原本有一个基于递归实现的test.ST_GeoHashAdjacent函数，用于计算GeoHash的相邻值。为提升性能，计划将其改写为WHILE循环实现，但测试发现改写后的输出结果与原递归函数不一致。虽通过保存初始ai0和lc0修复了部分测试用例，但扩展测试仍有大量失败。以下是各版本代码及正确的循环改写方案：

原递归实现代码 #

CREATE FUNCTION test.ST_GeoHashAdjacent(text,text,OUT neighbor TEXT) 
 ...
 BEGIN
  If STRPOS(_g_bd[__ai], _h_lc) > 0 AND _h_pf <> '' THEN
    _h_pf := ST_GeoHashAdjacent(_h_pf, $2); -- 递归调用
  END IF;
  neighbor := _h_pf || _b_32[STRPOS(_g_nb[__ai], _h_lc)];
 END;

第一次改写的WHILE循环代码 #

BEGIN
  WHILE STRPOS(_g_bd[__ai], _h_lc) > 0 AND _h_pf <> '' LOOP
    -- 试图模拟递归：_h_pf := ST_GeoHashAdjacent(_h_pf, $2)
    pf2:= _h_pf;
    _h_pf := LEFT(pf2, -1);
    _h_lc := RIGHT(pf2, 1);
    _h_tp := LENGTH(pf2) % 2;
    __ai  := STRPOS(_lu_d, $2) + _h_tp;
    _h_pf := _h_pf || _b_32[STRPOS(_g_nb[__ai], _h_lc)];
  END LOOP;
      
  neighbor := _h_pf || _b_32[STRPOS(_g_nb[__ai], _h_lc)];
END;

初步修复后的代码 #

CREATE FUNCTION test.ST_GeoHashAdjacent(
  IN  ref_hash  TEXT,
  IN  direction TEXT,
  OUT neighbor  TEXT
) LANGUAGE 'plpgsql' IMMUTABLE STRICT AS
  $FUNCTION$
    DECLARE
      _lu_d TEXT   := 'n s e w';
      _b_32 TEXT[] := ARRAY[
        '0','1','2','3','4','5','6','7','8','9','b','c','d','e','f','g','h','j','k','m','n','p','q','r','s','t','u','v','w','x','y','z'
      ];
      _g_nb TEXT[] := ARRAY[
        'p0r21436x8zb9dcf5h7kjnmqesgutwvy', 'bc01fg45238967deuvhjyznpkmstqrwx',
        '14365h7k9dcfesgujnmqp0r2twvyx8zb', '238967debc01fg45kmstqrwxuvhjyznp',
        'bc01fg45238967deuvhjyznpkmstqrwx', 'p0r21436x8zb9dcf5h7kjnmqesgutwvy',
        '238967debc01fg45kmstqrwxuvhjyznp', '14365h7k9dcfesgujnmqp0r2twvyx8zb'
      ];
      _g_bd TEXT[] := ARRAY[
        'prxz',     'bcfguvyz',
        '028b',     '0145hjnp',
        'bcfguvyz', 'prxz',
        '0145hjnp', '028b'
      ];
                     
      _h_pf TEXT   := LEFT($1, -1);
      _h_lc TEXT   := RIGHT($1, 1);
    
      _h_tp INT    := LENGTH($1) % 2;
      
      __ai  INT    := STRPOS(_lu_d, $2) + _h_tp;
      ai0  int;
      lc0 TEXT;
          pf2 TEXT;
    BEGIN
          ai0 := __ai;
          lc0 := _h_lc;
      WHILE STRPOS(_g_bd[__ai], _h_lc) > 0 AND _h_pf <> '' LOOP
        -- 试图模拟递归：_h_pf := ST_GeoHashAdjacent(_h_pf, $2)
        pf2:= _h_pf;
        _h_pf := LEFT(pf2, -1);
        _h_lc := RIGHT(pf2, 1);
        _h_tp := LENGTH(pf2) % 2;
        __ai  := STRPOS(_lu_d, $2) + _h_tp;
        _h_pf := _h_pf || _b_32[STRPOS(_g_nb[__ai], _h_lc)];
      END LOOP;
          
      neighbor := _h_pf || _b_32[STRPOS(_g_nb[ai0], lc0)];
    END;
  $FUNCTION$
;

提供的DO WHILE循环思路代码 #

BEGIN
  _h_pf := $1;
  LOOP
    _h_lc := RIGHT(_h_pf, 1);
    _h_tp := LENGTH(_h_pf) % 2;
    _h_pf := LEFT(_h_pf, -1);
    __ai  := STRPOS(_lu_d, $2) + _h_tp;
    neighbor := _h_pf || _b_32[STRPOS(_g_nb[__ai], _h_lc)];
    EXIT WHEN NOT(_h_pf <> '' AND STRPOS(_g_bd[__ai], _h_lc) > 0);
    _h_pf := neighbor;
  END LOOP;
END;

正确的循环改写实现 #

CREATE FUNCTION test.ST_GeoHashAdjacent(
  IN  ref_hash  TEXT,
  IN  direction TEXT,
  OUT neighbor  TEXT
) LANGUAGE 'plpgsql' IMMUTABLE STRICT AS
  $FUNCTION$
    DECLARE
      _lu_d TEXT   := 'n s e w';
      _b_32 TEXT[] := ARRAY[
        '0','1','2','3','4','5','6','7','8','9','b','c','d','e','f','g','h','j','k','m','n','p','q','r','s','t','u','v','w','x','y','z'
      ];
      _g_nb TEXT[] := ARRAY[
        'p0r21436x8zb9dcf5h7kjnmqesgutwvy', 'bc01fg45238967deuvhjyznpkmstqrwx',
        '14365h7k9dcfesgujnmqp0r2twvyx8zb', '238967debc01fg45kmstqrwxuvhjyznp',
        'bc01fg45238967deuvhjyznpkmstqrwx', 'p0r21436x8zb9dcf5h7kjnmqesgutwvy',
        '238967debc01fg45kmstqrwxuvhjyznp', '14365h7k9dcfesgujnmqp0r2twvyx8zb'
      ];
      _g_bd TEXT[] := ARRAY[
        'prxz',     'bcfguvyz',
        '028b',     '0145hjnp',
        'bcfguvyz', 'prxz',
        '0145hjnp', '028b'
      ];
      current_hash TEXT := ref_hash;
      current_prefix TEXT;
      current_last_char TEXT;
      type INT;
      ai INT;
    BEGIN
      LOOP
        current_last_char := RIGHT(current_hash, 1);
        type := LENGTH(current_hash) % 2;
        ai := STRPOS(_lu_d, direction) + type;
        
        -- 计算当前hash的相邻值
        current_prefix := LEFT(current_hash, -1);
        neighbor := current_prefix || _b_32[STRPOS(_g_nb[ai], current_last_char)];
        
        -- 判断是否需要继续进位：前缀不为空，且当前字符在边界数组中
        EXIT WHEN current_prefix = '' OR STRPOS(_g_bd[ai], current_last_char) = 0;
        
        -- 将新计算出的neighbor作为下一次循环的current_hash，继续处理前缀的进位
        current_hash := neighbor;
      END LOOP;
    END;
  $FUNCTION$
;

该实现完全模拟原递归逻辑：每次处理当前hash值，计算相邻值后判断是否需要继续处理前缀（当前字符在边界且前缀非空），若需要则将新相邻值作为当前hash继续循环，直到无需进位为止。既保证了与原递归函数输出一致，又消除了递归带来的性能开销。

内容的提问来源于stack exchange，提问作者Peter Krauss