Zapisz formułę w tabeli i użyj formuły w funkcji

Mam bazę danych PostgreSQL 9.1, w której część obsługuje prowizje agentów. Każdy agent ma swoją własną formułę obliczania wysokości prowizji. Mam funkcję generowania kwoty prowizji, którą powinien otrzymać każdy agent, ale korzystanie z niej staje się niemożliwe, ponieważ liczba agentów rośnie. Jestem zmuszony zrobić kilka bardzo długich instrukcji i powtarzać kod, co sprawiło, że moja funkcja jest bardzo duża.

Wszystkie formuły mają stałe zmienne:

d .. dni pracowały w tym miesiącu
r .. nowe węzły naliczone
l .. wynik lojalnościowy
s .. subagent prowizji
b .. stawka podstawowa
i .. uzyskane przychody

Formuła może być następująca:

d*b+(l*4+r)+(i/d)+s

Każdy agent negocjuje formułę płatności z działem HR. Czy mogę zapisać formułę w tabeli agentów, a następnie mieć małą funkcję, która po prostu pobiera formułę z tabeli i tłumaczy ją wartościami i oblicza kwotę?

Przygotować

Twoje formuły wyglądają tak:

d*b+(l*4+r)+(i/d)+s

$nZastąpiłbym zmienne notacją, aby można je było zastąpić wartościami bezpośrednio w plpgsql EXECUTE(patrz poniżej):

$1*$5+($3*4+$2)+($6/$1)+$4

Możesz dodatkowo przechowywać oryginalne formuły (dla ludzkiego oka) lub dynamicznie generować ten formularz z wyrażeniem:

SELECT regexp_replace(regexp_replace(regexp_replace(
       regexp_replace(regexp_replace(regexp_replace(
      'd*b+(l*4+r)+(i/d)+s'
      , '\md\M', '$1', 'g')
      , '\mr\M', '$2', 'g')
      , '\ml\M', '$3', 'g')
      , '\ms\M', '$4', 'g')
      , '\mb\M', '$5', 'g')
      , '\mi\M', '$6', 'g');

Tylko upewnij się, że tłumaczenie jest prawidłowe. Kilka wyjaśnień wyrażeń regularnych :

\ m .. dopasowuje tylko na początku słowa
\ M .. dopasowuje tylko na końcu słowa

Czwarty parametr 'g'... wymienić globalnie

Funkcja podstawowa

CREATE OR REPLACE FUNCTION f_calc(
    d int         --  days worked that month
   ,r int         --  new nodes accuired
   ,l int         --  loyalty score
   ,s numeric     --  subagent commission
   ,b numeric     --  base rate
   ,i numeric     --  revenue gained
   ,formula text
   ,OUT result numeric
)  RETURNS numeric AS
$func$
BEGIN    
   EXECUTE 'SELECT '|| formula
   INTO   result
   USING  $1, $2, $3, $4, $5, $6;                                          
END
$func$ LANGUAGE plpgsql SECURITY DEFINER IMMUTABLE; 

Połączenie:

SELECT f_calc(1, 2, 3, 4.1, 5.2, 6.3, '$1*$5+($3*4+$2)+($6/$1)+$4');

Zwroty:

29.6000000000000000

Najważniejsze punkty

• Funkcja przyjmuje parametr 6 wartości i zajmuje 7 formula textmiejsce. Stawiam formułę na końcu, abyśmy mogli użyć $1 .. $6zamiast $2 .. $7. Tylko ze względu na czytelność.
  Przypisałem typy danych do wartości, które uznałem za stosowne. Przypisz odpowiednie typy (w celu wprowadzenia podstawowych kontroli poczytalności) lub po prostu wykonaj je wszystkie numeric:

• Przekaż wartości do dynamicznego wykonania z USINGklauzulą. Pozwala to uniknąć rzucania w tę iz powrotem, a wszystko jest prostsze, bezpieczniejsze i szybsze.

• Używam OUTparametru, ponieważ jest on bardziej elegancki i zapewnia krótszą, bardziej przejrzystą składnię. Ostateczny RETURNnie jest potrzebny, wartości parametrów OUT są zwracane automatycznie.

• Rozważ wykład @Chris na temat bezpieczeństwa i rozdział „Pisanie DEFINICERA BEZPIECZEŃSTWA w bezpieczny sposób” w podręczniku. W moim projekcie pojedynczym punktem wstrzyknięcia jest sama formuła.

• Możesz użyć wartości domyślnych dla niektórych parametrów w celu dalszego uproszczenia połączenia.

Przeczytaj uważnie niniejsze uwagi dotyczące bezpieczeństwa. Zasadniczo próbujesz wstrzyknąć dowolny kod SQL do swoich funkcji. W związku z tym musisz mieć to uruchamiane przez użytkownika z bardzo ograniczonymi uprawnieniami.

1. Utwórz użytkownika i odwołaj od niego wszystkie uprawnienia. Nie udzielaj uprawnień publicznym w tej samej bazie danych, gdy to robisz.

2. Utwórz funkcję do oceny wyrażenia, wprowadź je security defineri zmień właściciela na tego użytkownika z ograniczeniami.

3. Przetwarzaj wyrażenie, a następnie przekaż je utworzonej powyżej funkcji eval (). Możesz to zrobić w innej funkcji, jeśli potrzebujesz,

Pamiętaj, że ma to poważne konsekwencje dla bezpieczeństwa.

Edycja: Krótki przykładowy kod (nieprzetestowany, ale powinien Cię tam zabrać, jeśli postępujesz zgodnie z dokumentami):

CREATE OR REPLACE FUNCTION eval_numeric(text) returns numeric language plpgsql security definer immutable as
$$
declare retval numeric;
begin

execute $e$ SELECT ($1)::numeric$e$ into retval;
return retval;
end;
$$;

ALTER FUNCTION eval_numeric OWNER TO jailed_user;

CREATE OR REPLACE FUNCTION foo(expression text, a numeric, b numeric) returns numeric language sql immutable as $$
select eval(regexp_replace(regexp_replace($1, 'a', $2, 'g'), 'b', '$3', 'g'));
$$; -- can be security invoker, but eval needs to be jailed.

Alternatywą po prostu zapisanie formuły, a następnie jej wykonanie (co, jak wspomniał Chris, ma problemy z bezpieczeństwem ), byłoby utworzenie oddzielnej tabeli, formula_stepsktóra w zasadzie zawierałaby zmienne i operatory oraz kolejność ich wykonywania. Byłoby to trochę więcej pracy, ale byłoby bardziej bezpieczne. Tabela może wyglądać następująco:

formula_steps
-------------
  formula_step_id
  formula_id (FK, do którego odwołuje się tabela agentów)
  input_1
  input_2
  operator (może być również identyfikatorem tabeli dozwolonych operatorów, jeśli nie chcesz bezpośrednio przechowywać symboli operatora)
  sekwencja

Inną opcją byłoby użycie biblioteki / narzędzia innej firmy do oceny wyrażeń matematycznych. To sprawiłoby, że twoja baza danych byłaby mniej podatna na wstrzykiwanie SQL, ale teraz przeniosłeś możliwe problemy bezpieczeństwa na swoje narzędzie zewnętrzne (co nadal może być całkiem bezpieczne).

Ostatnią opcją byłoby napisanie (lub pobranie) procedury oceniającej wyrażenia matematyczne. Istnieją znane algorytmy tego problemu, więc znalezienie informacji w Internecie nie powinno być trudne.