The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact.
Lets take currency as an example. We store currency as numeric
, but when displayed we want it to be of a certain format, say to_char(_numeric_value, 'FM999G999G999G999G990D00')
.
The obvious solution is to create a table that stores this format and uses is whenever needed. But since this is very commonly called function we don't want it to be slow. The solution I came up with is to re-compile format function each time config is changed. I use on-update trigger for that and also store function source-code. This way performance should be the same as if the format was hardcoded, but it doesn't seem to perform as expected.
What are down sides of this solution, am I missing something? Maybe there's an even better solution?
sqlfiddle is down for me currently, so I will post all necessary DDL/DML here.
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 (!)
fnc_common_format_money(numeric)
, nottest_fnc_common_format_money(numeric)
. Typo or are you actually testing the wrong function by mistake?