CODEWITHSUNDEEP
postgresqlplpgsql
Claudio Scabbia Sepúlveda
Claudio Scabbia Sepúlveda

Reputation: 97

Convert stored formula to work into PL/pgSQL

I have an stored formula into my database, that value is defined by some people in my organization and is used to calculate an average. The trouble is that I need to use that formula in a PL/pgSQL procedure (postgres), but the syntax is different, some formulas are like this:

if (x >= 0 && x <= 51.999) then 0 
elsif (x > 51.999 && x <= 64.999) then 70 
elsif (x > 64.999 && x <= 90.999) then 100 
elsif (x > 90.999 && x <= 103.999) then 115 
elsif (x > 103.999 && x <= 130) then 130 end

x = my value

I've been thinking about a replace, but I don't know if that would be the correct solution... So, I need your help. Thanks!

Upvotes: 0

Views: 72

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

In PL/pgSQL syntax:

y := case
 when x between 0 and 51.999 then 0 
 when x between 52 and 64.999 then 70 
 when x between 65 and 90.999 then 100 
 when x between 91 and 103.999 then 115 
 when x between 104 and 130 then 130 
end;

Please note that between is inclusive. This is why I have changed the lower boundaries.
As a reusable function:

create or replace function custom_function(x numeric) returns numeric language sql as
$$
select case
 when x between 0 and 51.999 then 0 
 when x between 52 and 64.999 then 70 
 when x between 65 and 90.999 then 100 
 when x between 91 and 103.999 then 115 
 when x between 104 and 130 then 130 
end::numeric;
$$;

Edit
Multiple formulae. You can define/change many of them in a clean declarative way.

create or replace function pg_temp.custom_function(function_name text, x numeric)
returns numeric language sql as
$$
with function_data(fname, range_lo, range_hi, result) as
(
  values
  -- function_a parameter values
  ('function_a', 0,   51.999,  0),
  ('function_a', 52,  64.999,  70),
  ('function_a', 65,  90.999,  100),
  ('function_a', 91,  103.999, 115),
  ('function_a', 104, 130.999, 130),
  -- function_b parameter values 
  ('function_b', 0,   51.999,  10),
  ('function_b', 52,  64.999,  170),
  ('function_b', 65,  90.999,  200),
  ('function_b', 91,  103.999, 215),
  ('function_b', 104, 130.999, 230)
  -- other function names and values
)
select result::numeric 
  from function_data
  where fname = function_name 
  and x between range_lo and range_hi;
$$;

Call the function like this:

select custom_function('function_a', 100)

Upvotes: 1

Related Questions