Postgres SQL Function converted to Snowflake SQL with a For Loop included-CodePudding

I'm having some difficulty converting a Postgres SQL function to Snowflake. I've done a few conversions so far, but this is my first one with a For Loop, which I'm not too familiar with as it is. I've only just started using Snowflake and I'm finding the documentation kind of sparse.

My biggest problem with this is getting the loop to work.

Here is the Postgres function that needs converting to snowflake -

create function mod_10_int(input character varying) returns integer
    language plpgsql
as
$$
DECLARE

    src_array char[];
    codepoints integer[];
    checksum integer = 0;
    i int = 1;
    ODD_FLAG INT = 1;

BEGIN

  -- Return NULL if either input isn't populated in SFDC
  CASE
    WHEN input IS NULL THEN RETURN NULL;
    ELSE

      src_array = string_to_array(input,NULL);

      --Convert source code to integer code points
      FOR i IN 1..(array_length(src_array,1)) LOOP
          CASE UPPER(src_array[i])
            WHEN '0' THEN codepoints[i] = 0;
            WHEN '1' THEN codepoints[i] = 1;
            WHEN '2' THEN codepoints[i] = 2;
            WHEN '3' THEN codepoints[i] = 3;
            WHEN '4' THEN codepoints[i] = 4;
            WHEN '5' THEN codepoints[i] = 5;
            WHEN '6' THEN codepoints[i] = 6;
            WHEN '7' THEN codepoints[i] = 7;
            WHEN '8' THEN codepoints[i] = 8;
            WHEN '9' THEN codepoints[i] = 9;
            WHEN 'A' THEN codepoints[i] = 0;
            WHEN 'B' THEN codepoints[i] = 1;
            WHEN 'C' THEN codepoints[i] = 2;
            WHEN 'D' THEN codepoints[i] = 3;
            WHEN 'E' THEN codepoints[i] = 4;
            WHEN 'F' THEN codepoints[i] = 5;
            WHEN 'G' THEN codepoints[i] = 6;
            WHEN 'H' THEN codepoints[i] = 7;
            WHEN 'I' THEN codepoints[i] = 8;
            WHEN 'J' THEN codepoints[i] = 9;
            WHEN 'K' THEN codepoints[i] = 0;
            WHEN 'L' THEN codepoints[i] = 1;
            WHEN 'M' THEN codepoints[i] = 2;
            WHEN 'N' THEN codepoints[i] = 3;
            WHEN 'O' THEN codepoints[i] = 4;
            WHEN 'P' THEN codepoints[i] = 5;
            WHEN 'Q' THEN codepoints[i] = 6;
            WHEN 'R' THEN codepoints[i] = 7;
            WHEN 'S' THEN codepoints[i] = 8;
            WHEN 'T' THEN codepoints[i] = 9;
            WHEN 'U' THEN codepoints[i] = 0;
            WHEN 'V' THEN codepoints[i] = 1;
            WHEN 'W' THEN codepoints[i] = 2;
            WHEN 'X' THEN codepoints[i] = 3;
            WHEN 'Y' THEN codepoints[i] = 4;
            WHEN 'Z' THEN codepoints[i] = 5;
            ELSE codepoints[i] = 0; ----Error case
          END CASE;
      END LOOP;

      --Mod 10 logic
      FOR i IN REVERSE (array_length(codepoints,1))..1 LOOP
        IF ODD_FLAG % 2 = 1
          THEN IF (2 * codepoints[i]) > 9
                 THEN checksum := checksum   (2 * codepoints[i]) - 9;
               ELSE checksum := checksum   (2 * codepoints[i]);
               END IF;
          ELSE checksum := checksum   codepoints[i];
        END IF;

        ODD_FLAG := ODD_FLAG   1;

      END LOOP;


      --Conversion to string for formatting, and output
      RETURN (checksum * 9) % 10;

  END CASE;
END;
$$;

I'm trying to just get the Loop working without the usage of variables so far - this is what I have so far

EXECUTE IMMEDIATE $$  
      --Convert source code to integer code points
      --FOR i IN 1 to (array_length(src_array,1)) LOOP
   BEGIN     FOR i IN 1 to ARRAY_SIZE(TO_ARRAY('A')) DO
        SELECT  CASE 
            WHEN (TO_ARRAY(UPPER('A'))) = '0' THEN '0'
            WHEN (TO_ARRAY(UPPER('A'))) = '1' THEN '1'
            WHEN (TO_ARRAY(UPPER('A'))) = '2' THEN '2'
            WHEN (TO_ARRAY(UPPER('A'))) = '3' THEN '3'
            WHEN (TO_ARRAY(UPPER('A'))) = '4' THEN '4'
            WHEN (TO_ARRAY(UPPER('A'))) = '5' THEN '5'
            WHEN (TO_ARRAY(UPPER('A'))) = '6' THEN '6'
            WHEN (TO_ARRAY(UPPER('A'))) = '7' THEN '7'
            WHEN (TO_ARRAY(UPPER('A'))) = '8' THEN '8'
            WHEN (TO_ARRAY(UPPER('A'))) = '9' THEN  '9'
            WHEN (TO_ARRAY(UPPER('A'))) = 'A' THEN  '0'
            WHEN (TO_ARRAY(UPPER('A'))) = 'B' THEN  '1'
            WHEN (TO_ARRAY(UPPER('A'))) = 'C' THEN  '2'
            WHEN (TO_ARRAY(UPPER('A'))) = 'D' THEN  '3'
            WHEN (TO_ARRAY(UPPER('A'))) = 'E' THEN '4'
            WHEN (TO_ARRAY(UPPER('A'))) = 'F' THEN  '5'
            WHEN (TO_ARRAY(UPPER('A'))) = 'G' THEN  '6'
            WHEN (TO_ARRAY(UPPER('A'))) = 'H' THEN  '7'
            WHEN (TO_ARRAY(UPPER('A'))) = 'I' THEN  '8'
            WHEN (TO_ARRAY(UPPER('A'))) = 'J' THEN  '9'
            WHEN (TO_ARRAY(UPPER('A'))) = 'K' THEN  '0'
        
            ELSE '0'
            --ELSE codepoints[i] = 0; ----Error case
          END 
      END FOR;
      $$;

CodePudding user response：

Converting to Python and using Python conventions it could look like this (without sample data and expected outputs, it is hard to verify):

create or replace function mod_10_int (input varchar)
returns int
language python
runtime_version = '3.8'
handler = 'mod_10_int'
as
$$
def mod_10_int(input):
    codepoints = []
    checksum = 0
    ODD_FLAG = 1

    if not input:
        return None
    else:
        src_array = list(input)
        for character in src_array:
            if character >= '0' and character <= '9':
                codepoints.append(int(character))
            elif character.upper() >= 'A' and character.upper() <= 'Z':
                codepoints.append((ord(character)-65) % 10)
            else:
                codepoints.append(0)

        # Mod 10 logic
        codepoints.reverse()
        for i in codepoints:
            if ODD_FLAG % 2 == 1:
                if (2 * i) > 9:
                    checksum  = (2 * i) - 9
                else:
                    checksum  = 2 * i
            else:
                checksum  = i

            ODD_FLAG  = 1

    return (checksum * 9) % 10
$$;