sql - 在 Redshift 中创建 SQL UDF

Question

我想在 redshift 中创建一个 UDF 来替换下面的 case 语句

SELECT CASE
    WHEN column1= 'value' then coalesce(replace(column2,',', '')::decimal, 0) /52
    WHEN column1= 'value2' then coalesce(replace(column2,',', '')::decimal, 0) /76.0
    WHEN column1= 'value3' then coalesce(replace(column2,',', '')::decimal, 0) * 89.3
WHEN column1= 'value4' then coalesce(replace(column2,',', '')::decimal, 0) * 17.85
WHEN column1= 'value5' then coalesce(replace(column2,',', '')::decimal, 0)
ELSE NULL END

标签： sqlamazon-redshift

Answer 1

CREATE OR REPLACE FUNCTION f_convert_string_to_decimal(VARCHAR, VARCHAR) 
RETURNS DECIMAL 
STABLE AS $$
-- Input: 'value2', '999,999,999'
-- Output: 13157894.723684210526
SELECT CASE
            WHEN $1 = 'value'  THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) /52
            WHEN $1 = 'value2' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) /76.0
            WHEN $1 = 'value3' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) * 89.3
            WHEN $1 = 'value4' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) * 17.85
            WHEN $1 = 'value5' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0)
      ELSE NULL END
$$ LANGUAGE SQL
;