[subtotal] PostgreSQL implements the decode function of Oracle

Create or replace function decode (variadic p_decode_list text []) returns text as$$declare-- get the array length (that is, the number of input parameters) v_len integer: = array_length (p_decode_list, 1);-- declare the variable v_ret text that holds the return value Begin / * * function description: simulate the DECODE function in Oracle (string processing, other formats can convert the returned values by yourself) * Parameter description: the format is the same as Oracle, at least three parameters * implementation principle: 1. VARIADIC allows variable parameters. 2. The DECODE in Oracle compares the first number sequentially with the subsequent even digit value, and if the same is the same, take the even digit + 1 value, otherwise take the last digit value (the last digit is even, otherwise it is null) * /-the same as Oracle when less than three parameters throw exceptions if v_len > = 3 then-the DECODE in Oracle compares the first number in turn with the even digit value after it. For i in 2. (v_len-1) loop v_ret: = null if the number of even digits + 1 is the same. If mod (I, 2) = 0 then if p_decode_list [1] = paired decodeList [I] then v_ret: = paired decodeList [I + 1]; elsif p_decode_list [1] paired decodeList [I] then if v_len = I + 2 and v_len > 3 then v_ret: = paired decodeList [v _ len]; end if; exit when v_ret is not null; end loop Else raise exception 'UPG-00938: not enough args for function.'; end if; return vs. retentin: $language plpgsql -- Test 1select decode ('_ aa',','x') a3, decode ('_ aa',','s') a4, decode ('_ aa','s') a5, decode ('_ baked,'_ baked,'_ baked,') a5, decode ('_ a') '_ aa',' xbath,'_ baked, 'tt',' oily, 'xbath,' tt') a6 -- Test 2with xx as (select'M'sex union all select'F'sex union all select'X' sex union all select'Z 'sex union all select' 'sex) select sex, decode (sex,' Mouse, 'male', 'Fitch,' female', decode (sex,'X', 'denatured', 'other') from xx