Defining global constants in Postgresql stored function/procedures?

Question

I have a set of functions I have created in PostgreSql. I would like to be able to configure some behavior and limits with global constants.

So far, I have implemented functions like these, which my functions call to retrieve the constant value:

CREATE OR REPLACE FUNCTION slice_length()
RETURNS integer AS $$
BEGIN
    RETURN 50;
END; $$
LANGUAGE plpgsql IMMUTABLE;

I was wondering, is there a better/smarter way to achieve this?

Answer 1

I had a similar problem: store some sort of configuration and access it from my function. To solve the problem I created a function which returns a constant JSONB containing my configuration.

The function looks like this:

create or replace function config()
    returns jsonb
    language plpgsql
    immutable 
as $BODY$

declare
job_manager_config constant jsonb := '{

    "notify": {
        
        "channels": {
            "all_available": "all.available",
            "all_status": "all.status",
            "task_available": "task.%s.available",
            "task_status": "task.%s.status",
            "job": "job.%s"
        },

    }

    "allowed_pets": {
        "dogs": 6,
        "cats": 6,
        "rabbits": 3,
        "lions": 2,
        "sweet_piranha": 8
    }

}';

begin
    return job_manager_config;
end;
$BODY$;

To access configuration elements quickly I defined a second function to query the configuration. This function accepts a path as a list of strings and return the value (or JSON object) found at the path. Note that the returned value is text but you can easily cast it to the actual type.

create or replace function job_manager.config(
    variadic path_array text[])
    returns text
    language plpgsql
    immutable 
as $BODY$

begin
    -- Return selected object as text (instead of json or jsonb)
    return jsonb_extract_path_text(job_manager.config(), variadic path_array);
end;

$BODY$;

This is a usage example:

test=# select job_manager.config('notify', 'channels', 'job');
       config       
--------------------
 job_manager.job.%s
(1 row)

Answer 2

Take a look at this other answer. It uses the same approach that you do.

Create constant string for entire database

Answer 3

I would create a table for that:

create table constant (c1 int, c2 numeric);
insert into constant (c1, c2) values (100, 33.2);

The function, SQL not PL/pgSQL, would retrieve the single row:

create or replace function get_constants()
returns constant as $$
    select *
    from constant;
$$ language sql immutable;

And would be called for each constant:

select (get_constants()).c1, (get_constants()).c2;

All data would be in a single place and retrieved with a single function.

If a table is really that bad then place all the values in a single function:

create or replace function get_constants (
    c1 out int, c2 out numeric
) returns record as $$
    select 100, 33.5;
$$ language sql immutable;

And use it as above.