Create or replace function decode (variadic p_decode_list text[]) returns text as$ $declare -- Get array length (that is, number of arguments) v_len integer := array_length (p_decode_ list, 1); -- declares the variable v_ret text;begin /* * function description that holds the return value: simulates decode functionality in Oracle ( String processing, Other formats can self-convert return values) * parameter description: The format is the same as Oracle, at least three parameters * implementation principle: 1, variadic allow the variable parameter; The decode in &NBSP;2 and Oracle are compared with the first number and then the even digit value, the same is the value of the even digit +1, otherwise take the last value (the last one is even, otherwise null) */ -- Same as Oracle when the parameter is less than three throws an exception if v_len >= 3 then -- The decode in Oracle is compared with the number of the first and subsequent even digits, and the same is the value of the even digit +1 for i in 2. (v_len - 1) loop v_ret := null; if mod (i, &NBSP;2) = 0 then if p_decode_list[1] = p_decode_list[i] then v_ret := p_decode_list[i+1]; elsif p_decode_list[1] <> p_decode_list[i] then if v_len = i + 2 and v_len > 3 then v_ret := p_decode_list[v_len]; End if; end if; end if; exit when v_ret is not null; end loop; else raise exception ' upg-00938: not enough args for function. '; end if; return v_ret;end;$$ language plpgsql;-- test 1select decode (' _a ' , ' _aa ', ' x ') a3, decode (' _a ', ' _aa ', ' x ', ' s ') a4, decode (' _a ', ' _aa ', ' x ', ' _b ', ' s ') a5, decode ('_b ', ' _aa ', ' x ', ' _b ', ' s ') a5, decode (' _a ', ' _aa ', ' x ', ' _b ', ' s ', ' o ', ' x ', ' 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, ' M ', ' male ', ' F ', ' female ', decode ( sex, ' X ', ' degeneration ', ' other ')) from xx;
This article is from the "accumulate Kuibu to thousands of Miles" blog, please be sure to keep this source http://chnjone.blog.51cto.com/4311366/1658845
"Subtotal" PostgreSQL implements Oracle's decode function function