"Subtotal" PostgreSQL implements Oracle's decode function function

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