Defining global constants in Postgresql stored function / procedures?

Question

I have a set of functions that I created in PostgreSql. I would like to be able to customize behavior and constraints using global constants.

So far I have implemented functions that my functions call to get a constant value:

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

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

Answer 1

Take a look at this other answer. He uses the same approach as you.

Create a persistent row for the entire database

Answer 2

: - . , , JSONB, .

:

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$;

, . ( JSON), . , - , .

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$;

:

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

Answer 3

:

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

SQL, PL/pgSQL, :

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

:

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

.

If the table is really that bad, put all the values ​​in one 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.