Thread: [GENERAL] Shared Constants in PLPGSQL

----- Original Message -----
> From: "Tim Uckun" <timuckun@gmail.com>
> To: "pgsql-general" <pgsql-general@postgresql.org>
> Sent: Tuesday, August 1, 2017 5:56:02 AM
> Subject: [GENERAL] Shared Constants in PLPGSQL
>
> 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.
>
> Is there a better way to deal with this?
>

One alternative is to create a configuration values table to store application configuration parameters. This table
generallyhas exactly one row. You can add new application configuration parameters easily as application requirements
evolveby adding a new column of an appropriate data type. Then, when you need the configuration values in PLPSQL or
anywherefor that matter, you do a simple SELECT statement. Also you, can update parameters if necessary (supposing if
theyare not actually *constants*) at run time very easily, too. 

-- B

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

On Tue, Aug 1, 2017 at 8:04 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> 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.

This can be automated too, via event triggers:
https://www.postgresql.org/docs/9.3/static/sql-createeventtrigger.html

merlin

On Tue, Aug 1, 2017 at 8:29 AM, Tim Uckun <timuckun@gmail.com> wrote:
> 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.

Sure. The point is, by having a proc return a table based composite
type, you can simplify changes down the line.   Adding a new setting
can be done via ALTER.  Changing a setting (should it become
necessary) can be done with an UPDATE.   The immutable wrapping
function does eliminate some fetches and I would generally write that
wrapper.

merlin