我想把下面这个基于循环的查询（来自Oracle）转换为基于集合的查询（PostgreSQL），不使用循环/游标。

以下代码用于为给定字符串计算/生成数值。它先对字符串中的每个字母使用循环计算ASCII值，然后根据条件对其他值进行加减求和。

现有的基于循环的代码，我需要将其转换为基于集合的查询。

DECLARE
   str              VARCHAR2(500) := 'STACK101'; --INPUT STRING
   l                    PLS_INTEGER; 
   Cnt                PLS_INTEGER; 
   BALANCE_1   PLS_INTEGER := 1000001;
   BALANCE_2   PLS_INTEGER := 2000002;
   BALANCE_4   PLS_INTEGER := 4000004;   
   VAL_1       PLS_INTEGER := 0;
   VAL_2       PLS_INTEGER := 0;
   VAL_3       PLS_INTEGER := 0;
   R_1         PLS_INTEGER;
   R_2         PLS_INTEGER;
   BASE        PLS_INTEGER;
   OUT         NUMBER;
BEGIN

    Cnt := LENGTHC(str);

   FOR l IN 1 .. Cnt LOOP
      BASE := ASCII(SUBSTRC(str, l, 1));
      R_1  := VAL_1;
      R_2  := VAL_2;
      VAL_1 := 7 * VAL_3 + BASE;
      VAL_2 := 7 * R_1;
      VAL_3 := 7 * R_2;

      IF VAL_1 >= BALANCE_4 THEN
         VAL_1 := VAL_1 - BALANCE_4;
         VAL_2 := VAL_2 + 4;
      END IF;

      IF VAL_1 >= BALANCE_2 THEN
         VAL_1 := VAL_1 - BALANCE_2;
         VAL_2 := VAL_2 + 2;
      END IF;

      IF VAL_1 >= BALANCE_1 THEN
         VAL_1 := VAL_1 - BALANCE_1;
         VAL_2 := VAL_2 + 1;
      END IF;

      IF VAL_2 >= BALANCE_4 THEN
         VAL_2 := VAL_2 - BALANCE_4;
         VAL_3 := VAL_3 + 4;
      END IF;

      IF VAL_2 >= BALANCE_2 THEN
         VAL_2 := VAL_2 - BALANCE_2;
         VAL_3 := VAL_3 + 2;
      END IF;

      IF VAL_2 >= BALANCE_1 THEN
         VAL_2 := VAL_2 - BALANCE_1;
         VAL_3 := VAL_3 + 1;
      END IF;

      IF VAL_3 >= BALANCE_4 THEN
         VAL_3 := VAL_3 - BALANCE_4;
      END IF;

      IF VAL_3 >= BALANCE_2 THEN
         VAL_3 := VAL_3 - BALANCE_2;
      END IF;

      IF VAL_3 >= BALANCE_1 THEN
         VAL_3 := VAL_3 - BALANCE_1;
      END IF;
   END LOOP;

   OUT := VAL_3;
   OUT := BALANCE_1 * OUT + VAL_2;
   OUT := BALANCE_1 * OUT + VAL_1;
   DBMS_OUTPUT.PUT_LINE(OUT);
END;

期望输出：94874704955518361

解决方案

这里有一个Oracle的递归版本，你可以尝试把它转换为PostgreSQL：

with data(id, str) as (
    values
    ( 1, 'STACK101' ),
    ( 2, 'STACK102' )
),
rcte(id, idx, str, base, val_1, val_2, val_3, r_1, r_2, step) as (

    select id, 1 as idx, str, ASCII(SUBSTRC(str, 1, 1)) AS base,
        ASCII(SUBSTRC(str, 1, 1)) AS val_1, 0 AS val_2, 0 AS val_3,
        0 as r_1, 0 as r_2, 0 as step
    from data 

    union all

    select r.id, r.idx + decode(step, 10, 1, 0), r.str, 
        --base
        ASCII(SUBSTRC(r.str, r.idx+decode(step, 10, 1, 0), 1)), 
        -- val_1
        CASE WHEN r.step = 1 
            THEN 7 * r.val_3 + ASCII(SUBSTRC(r.str, r.idx+decode(step, 10, 1, 0), 1))
        WHEN r.step = 2 AND r.val_1 >= 4000004 
            THEN r.val_1 - 4000004
        WHEN r.step = 3 AND r.val_1 >= 2000002 
            THEN r.val_1 - 2000002
        WHEN r.step = 4 AND r.val_1 >= 1000001
            THEN r.val_1 - 1000001
        ELSE 
            r.val_1 
        END, 

        -- val_2
        CASE WHEN r.step = 1
            THEN 7 * r.r_1
        WHEN r.step = 2 AND r.val_1 >= 4000004 
            THEN r.val_2 + 4
        WHEN r.step = 3 AND r.val_1 >= 2000002 
            THEN r.val_2 + 2
        WHEN r.step = 4 AND r.val_1 >= 1000001
            THEN r.val_2 + 1
        WHEN r.step = 5 AND r.val_2 >= 4000004
            THEN r.val_2 - 4000004
        WHEN r.step = 6 AND r.val_2 >= 2000002
            THEN r.val_2 - 2000002
        WHEN r.step = 7 AND r.val_2 >= 1000001
            THEN r.val_2 - 1000001
        ELSE 
            r.val_2
        END, 

        -- val_3
        CASE 
        WHEN r.step = 1
            THEN 7 * r.r_2
        WHEN r.step = 5 AND r.val_2 >= 4000004
            THEN r.val_3 + 4
        WHEN r.step = 6 AND r.val_2 >= 2000002
            THEN r.val_3 + 2
        WHEN r.step = 7 AND r.val_2 >= 1000001
            THEN r.val_3 + 1
        WHEN r.step = 8 AND r.val_3 >= 4000004
            THEN r.val_3 - 4000004
        WHEN r.step = 9 AND r.val_3 >= 2000002
            THEN r.val_3 - 2000002
        WHEN r.step = 10 AND r.val_3 >= 1000001
            THEN r.val_3 - 1000001
        ELSE
            r.val_3
        END, 

        CASE 
        WHEN r.step = 10  
            THEN r.val_1
            ELSE r.r_1
        END, 
        CASE WHEN r.step = 10  
            THEN r.val_2
            ELSE r.r_2
        END,
        case when r.step = 10 then 0 else r.step+1 end
    from rcte r
    join data d on d.id = r.id and 
        case when r.step = 10 then r.idx < lengthc(r.str) else true end
)
select r.id, r.str,
    1000001 * (1000001 * (case when val_3 >= 1000001 then val_3 - 1000001 else val_3 end) + val_2) + val_1 
    as result
from rcte r
where r.idx = lengthc(r.str) and r.step = 10
order by id
;

1   STACK101    94874704955518361
2   STACK102    94874704955518362