我在將 Postgres SQL 函式轉換為 Snowflake 時遇到了一些困難。到目前為止，我已經完成了一些轉換，但這是我第一次使用 For 回圈，我對它不太熟悉。我才剛剛開始使用 Snowflake，我發現檔案有點稀疏。
我最大的問題是讓回圈作業。
這是需要轉換為雪花的 Postgres 函式 -
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;
$$;
到目前為止，我正試圖讓回圈在不使用變數的情況下作業——這就是我到目前為止所擁有的
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;
$$;
uj5u.com熱心網友回復：
轉換為 Python 并使用 Python 約定，它可能看起來像這樣（沒有樣本資料和預期輸出，很難驗證）：
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
$$;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/532035.html
上一篇：物件方法參考變數不是物件方法