ROUND_VAR ​

Round a number (up or down) to the nearest integer. Optionally, specify the number of decimal places for the rounding. This function is equivalent to ROUND, except that it allows the second argument to be a column name or an expression, while ROUND enforces the use of a constant.

ROUND_VAR(number [, places])

The return type of the ROUND_VAR function is the DECIMAL type of the first argument. For example:

ROUND_VAR(1.234, 1*2) returns 1.230

Examples ​

Determine the precision and scale of a given DECIMAL value by using the integer values found in the seasonid column of the season table:

premdb=# select round_var(1.111567, seasonid), seasonid from season;
 round_var | seasonid 
-----------+----------
  1.100000 |        1
  1.110000 |        2
  1.112000 |        3
  1.111600 |        4
  1.111570 |        5
  1.111567 |        6
  1.111567 |        7
  1.111567 |        8
  1.111567 |        9
...
(25 rows)

Note that the scale of the result is 6 decimal places, so the results for seasonid 6 and higher are the same.

Enabling ROUND_VAR Behavior for ROUND ​

You can set a configuration parameter that translates the ROUND function to the ROUND_VAR function:

set enable_alternative_round to on;

This parameter defaults to OFF. By turning it on, you will be able to use ROUND with non-constant values for the second argument. For example:

premdb=# set enable_alternative_round to on;
SET
premdb=# select round(1.111567, seasonid), seasonid from season order by 2 limit 6;
  round   | seasonid 
----------+----------
 1.100000 |        1
 1.110000 |        2
 1.112000 |        3
 1.111600 |        4
 1.111570 |        5
 1.111567 |        6
(6 rows)

Note that when you do this, ROUND will produce the same return type that ROUND_VAR produces.

Parent topic:Mathematical Functions