On Tue, Aug 1, 2017 at 4:56 AM, Tim Uckun <timuc...@gmail.com> wrote: > What's the best way to deal with global constants in PLPGSQL. Currently I am > putting them in a function with out parameters and then calling that > function from every other function that needs them like this. > > CREATE OR REPLACE FUNCTION hashids.constants( > OUT min_alphabet_length integer, > OUT sep_div numeric, > OUT guard_div numeric, > OUT default_steps text, > OUT default_alphabet text, > OUT salt text) > > I am presuming that if I set this function as immutable the calls to this > function will be cached and will not incur much overhead.
Yes. Couple things I'd suggest changing. 1. Make a control table, say, hashids.config and put your data there. CREATE TABLE hashids.config ( min_alphabet_length integer, ... ); -- one record only, please: CREATE UNIQUE INDEX ON hashids.config((1)); 2. let's change your function to return the table type! CREATE OR REPLACE FUNCTION hashids.constants() RETURNS hashids.config AS $$ SELECT * FROM hashids.config; $$ LANGUAGE SQL IMMUTABLE; ...here we're breaking a rule. This is technically not an immutable query. However, if you are calling this all over the place in plpgsql, you can save a few cycles since operations of the form of: DECLARE settings hashid.config; BEGIN settings := hashids.constants(); ... ...will be calculated at plan time and not re-evaluated every time the function is called. The savings here are pretty minor but I've employed this trick many times because there's very little downside to doing so. You do have to remember to recreate the constants() function every time you change a setting in order to force the plan to re-evaluate. The main advantage over your approach is that you don't have to modify multiple things every time you add a new config values; just add a column and replace the function. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
