將公式儲存在表格中並在函式中使用公式

我有一個 PostgreSQL 9.1 數據庫，其中一部分處理代理佣金。每個代理都有他/她自己的計算他們獲得多少佣金的公式。我有一個函式可以生成每個代理應該獲得的佣金數量，但隨著代理數量的增長，它變得無法使用。我被迫做一些非常長的案例陳述和重複程式碼，這使我的功能變得非常大。

所有公式都有常量變數：

d .. 那個月的工作天數
r .. 獲得新節點
l ..忠誠度得分
s .. 分代理委員會
b .. 基準利率
i .. 獲得的收入

公式可以是這樣的：

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

每個代理與人力資源部門協商付款方式。那麼我可以將公式儲存在代理表中，然後像一個小函式一樣從表​​中獲取公式並將其轉換為值併計算數量嗎？

準備

您的公式如下所示：

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

我會用$n符號替換變數，以便它們可以直接在 plpgsql 中替換為值EXECUTE（見下文）：

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

您可以額外儲存原始公式（用於人眼）或使用如下表達式動態生成此表單：

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');

只要確保你的翻譯是正確的。正則表達式的一些解釋：

\m .. 僅匹配單詞的開頭

\M .. 僅匹配單詞的結尾

第 4 個參數'g'.. 全域替換

核心功能

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; 

稱呼：

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

回報：

29.6000000000000000

要點

• 該函式採用 6 個值參數和formula text第 7 個值。我把公式放在最後，所以我們可以使用$1 .. $6而不是$2 .. $7. 只是為了可讀性。

我為我認為合適的值分配了數據類型。分配正確的類型（以實現基本的健全性檢查）或將它們全部設為numeric：

• USING使用該子句為動態執行傳入值。這避免了來迴轉換，使一切變得更簡單、更安全、更快捷。
• 我使用OUT參數是因為它更優雅，並且語法更短更清晰。RETURN不需要final ，OUT 參數的值會自動返回。
• 考慮@Chris 的安全講座和手冊中的“安全地編寫安全定義器函式”一章。在我的設計中，單點注入就是公式本身。
• 您可以對某些參數使用預設值以進一步簡化呼叫。

引用自：https://dba.stackexchange.com/questions/33894