PostgreSQL 和儲存配置

問題是儲存和使用全域配置，例如貨幣/日期格式等。由於這個函式通常被稱為我關心性能影響。讓我們以貨幣為例。我們將貨幣儲存為numeric，但在顯示時我們希望它具有某種格式，例如to_char(_numeric_value, 'FM999G999G999G999G990D00')。

顯而易見的解決方案是創建一個儲存此格式並在需要時使用的表。但由於這是非常常用的函式，我們不希望它變慢。我想出的解決方案是每次更改配置時重新編譯格式函式。我為此使用更新觸發器並儲存函式原始碼。這種方式的性能應該與硬編碼格式相同，但它似乎沒有按預期執行。

這個解決方案的缺點是什麼，我錯過了什麼嗎？也許有更好的解決方案？

sqlfiddle目前不適合我，所以我將在此處發布所有必要的 DDL/DML。

CREATE TABLE test_config(money_format text);
CREATE TABLE test_config_fncs(fnc_name text, fnc_source TEXT);

--This is function for performance comparison.
CREATE OR REPLACE FUNCTION test_fnc_common_format_money_no_precompile(_value numeric)
   RETURNS text AS
$BODY$
BEGIN
   RETURN to_char(_value, money_format)FROM test_config;
END;
$BODY$
   LANGUAGE plpgsql STRICT STABLE;

CREATE OR REPLACE FUNCTION test_tfnc_config_fnc_precompiler()
   RETURNS trigger AS
$BODY$
BEGIN
   IF (NOT (NEW.money_format = OLD.money_format)) THEN
       EXECUTE 
       (
           SELECT 
                   replace(fnc_source, '>money_mask<', NEW.money_format)
               FROM test_config_fncs 
               WHERE fnc_name='test_fnc_common_format_money'
       );
   END IF;
   RETURN NEW;
END;
$BODY$
   LANGUAGE plpgsql;

CREATE TRIGGER test_tg_config_aiu_fnc_precompile
   AFTER UPDATE
   ON test_config
   FOR EACH ROW
   EXECUTE PROCEDURE test_tfnc_config_fnc_precompiler();
-- DDL DONE
-- INITIAL INSERTS:
INSERT INTO test_config(money_format, date_format) values (null);

INSERT INTO test_config_fncs(fnc_name, fnc_source) VALUES ('test_fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION test_fnc_common_format_money(_value numeric)
   RETURNS text AS
$BODY$
BEGIN
   RETURN to_char(_value, '>money_mask<');
END;
$BODY$
   LANGUAGE plpgsql STRICT IMMUTABLE;
$fnc$);

--TEST QUERIES:
UPDATE test_config SET money_format='FM999G999G999G999G990D00';

SELECT * FROM test_fnc_common_format_money(11111.2222); -- runtime 1700ms per 1 million queries
SELECT * FROM test_fnc_common_format_money_no_precompile(11111.2222); -- 13100ms per 1 million (!)

大多數情況下，您的程式碼看起來很成熟，但請參見下文！一旦你解決了這個問題，我看不出有什麼問題。不過，我也懷疑它會大大提高性能。並且可能有更好的選擇，具體取決於具體要求。

一般建議

對於顯示的簡單功能：

1. **LANGUAGE sql**使用可以在外部查詢的上下文中內聯的簡單 SQL 函式 ( )。對於通常嵌套的簡單函式，SQL 更可取。

• PostgreSQL 儲存過程性能

2. 刪除**STRICT**修飾符。與您可能期望的相反，這可能對簡單的 SQL 函式沒有幫助，因為它可以防止所說的內聯。（STRICT不過，可能有助於昂貴的功能。）

• 沒有 STRICT 修飾符的函式執行得更快？

多個使用者

為了適應具有不同但或多或少穩定偏好的多個使用者，您可以在典型的預設search_path設置 上改進您的設計serach_path = "$user",public。在各自的私有模式中為每個具有相同函式名稱
的 使用者創建一個單獨的版本。除非模式合格，否則每個使用者都會看到他自己的函式版本。他們都可以使用相同的查詢。fnc_common_format_money()

切換架構

或者，如果我們正在處理單個使用者的不斷變化的需求，您可以search_path手動切換（以及一系列功能）：

SET search_path = "$user", verbose_format, public

verbose_format是具有專門功能的模式的名稱之一。也可以來之前"$user"。

有關的：

• 如何在 PostgreSQL 中為單元測試偽造 inet_client_addr()？

您可能不再需要動態設置的機制。如果您仍然這樣做，請test_config使用列**username**（或schemaname）擴展您的表，以便為每個使用者（模式）儲存單獨的行並相應地調整您的程式碼。讓它變得更簡單、更安全：

程式碼建議

對於“多使用者”場景。

CREATE OR REPLACE FUNCTION fnc_test_config_precompiler()
 RETURNS trigger AS
$func$
BEGIN
  EXECUTE (
     SELECT format(fnc_source, NEW.username, NEW.money_format)
     FROM   test_config_fncs 
     WHERE  fnc_name = 'test_fnc_common_format_money'
     );

  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER test_config_precompile
AFTER INSERT OR UPDATE OF username, money_format      -- !!
ON test_config
FOR EACH ROW EXECUTE PROCEDURE fnc_test_config_precompiler();

...

INSERT INTO test_config_fncs(fnc_name, fnc_source)
VALUES ('fnc_common_format_money',
$fnc$
CREATE OR REPLACE FUNCTION %$1I.fnc_common_format_money(_value numeric)
 RETURNS text LANGUAGE sql IMMUTABLE AS
'SELECT to_char(_value, %$2L)';
$fnc$);

要點

• 每個使用者設置建構一個功能。（可選，但建議的其餘部分在任何情況下都適用。）
• 直接在觸發器中建構條件，只有在相關列被更改或打開後才呼叫觸發器函式INSERT（這樣你OLD在觸發器函式中沒有引用，這不適用於額外的呼叫ON INSERT。簡單觸發器只創建所需的函式並且不處理孤立版本。
• SQL 函式體內的參數名稱需要 pg 9.2+。
• 用於在語句format()中註入（清理！）參數。使用SQL 注入CREATE FUNCTION解決可能的安全問題。

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