Question 89689 in PostgreSQL Question posted in
The official documentation can be found here.

PL/PGSQL function set upper and lower bound to an integer value – Postgresql

I am working on a pgsql function that calculates a score based on some logic. But one of the requirements is that a parameter after calculation should be in the range [100000, 9900000].

I can’t figure out how to do this with existing functions, obviously possible with if conditions, any help?

v_running_sum += (30 - v_calcuated_value)* 100000;

I want v_running_sum to be in the range mentioned above. Is there any way to bound the value of the variable if lower than the lower bound (100,000) to 100,000 and vice versa for the upper bound?

Tags:

2

Answers


  1. 0

    This is how you can easily do this check, using a range:

    SELECT  1 <@ int4range(100000, 9900000,'[]');

    There are many options how to implement this in your logic.

    —-edit—-

    When the outcome of a calculation should always be something between 100000 and 9900000, you can use this:

    SELECT  LEAST(GREATEST(var, 100000), 9900000);

    Whatever you stick into "var", the result will always be between these boundaries

    Login or Signup to reply.
  2. 0

    If you want a verbose solution use a CASE statement

     case when val <= 100000 then 100000
 when val >= 9900000 then 9900000
 else val end as val
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search