PL/pgSQLで偶数丸めを実装

偶数丸めとは

端数が0.5より小さい場合切り捨て、
端数が0.5より大きい場合切り上げ、
端数が0.5の場合切り捨てと切り上げのうち結果が偶数となる方へ丸める

round_even.sql

CREATE OR REPLACE FUNCTION test.round_even(
    in_round_number numeric,
    in_exponent int)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

declare
base_number numeric := 10;
even_number numeric := 2;
middle_number numeric := 0.5;
begin

  IF in_round_number IS NULL OR in_exponent IS NULL THEN
    RETURN NULL;
  END IF;

  IF MOD(ABS(in_round_number) * POWER(base_number, in_exponent) ,even_number ) = middle_number THEN
      RETURN TRUNC(in_round_number, in_exponent);
  ELSE
      RETURN ROUND(in_round_number, in_exponent);
  END IF;

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'ERROR : % : %', SQLSTATE, SQLERRM;
end;

$BODY$;

結果

test=> select test.round_even(1.4,0);
 round_even
------------
          1
test=> select test.round_even(1.5,0);
 round_even
------------
          2
test=> select test.round_even(1.6,0);
 round_even
------------
          2
test=> select test.round_even(2.5,0);
 round_even
------------
          2
test=> select test.round_even(3.5,0);
 round_even
------------
          4

追記

そもそも、偶数丸めを実装せずとも、realやdouble precision型ではround関数に渡すことにより偶数丸めになります。

round_even.sql

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4
(8 rows)

参考：postgres標準ドキュメント