In my case I don't expect these constants to be changed on a regular basis. They will be set just once and that's it. I was thinking it would be just as easy to set them in a proc as it would be to set them in a table. By putting them in an immutable proc I can hopefully save a couple of compute cycles.
On Wed, Aug 2, 2017 at 1:04 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > 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 >
