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 <mmoncure@xxxxxxxxx> wrote:
On Tue, Aug 1, 2017 at 4:56 AM, Tim Uckun <timuckun@xxxxxxxxx> 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
