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?

I personally like your approach, but ran into this question my self a few years back. This was the alternative that I found http://www.postgresql.org/docs/9.3/static/plperl-global.html. I'm not sure it buys you anything, but it's an option. Along with the table approach below. — Kuberchaun, Mar 24 '14 at 17:05
@JustKim That does not enforce data type constraints and a cast is needed on the function's returned text when the data is not text. — Clodoaldo Neto, Mar 24 '14 at 17:21
@JVerstry Also take a look at this link http://stackoverflow.com/questions/22663599/creating-constant-string-for-a-postgres-entire-database. It looks like your approach may be the way to go. — Kuberchaun, Mar 26 '14 at 15:22
@JustKim Nice finding. If you create an answer with a reference to the question, I'll approve it. — Jérôme Verstrynge, Mar 26 '14 at 15:32
Possible duplicate of [Is there a way to define a named constant in a PostgreSQL query?](http://stackoverflow.com/questions/13316773/is-there-a-way-to-define-a-named-constant-in-a-postgresql-query) — jtbandes, Feb 06 '17 at 18:17

score 4 · Answer 1 · edited Jul 13 '21 at 12:05

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)

Clodoaldo Neto · Answer 2 · 2014-03-24T22:57:13.717

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.

edited Mar 24 '14 at 22:57

answered Mar 24 '14 at 15:38

Clodoaldo Neto

118,695
26
233
260

Ok, but how is that better than an immutable function? – Jérôme Verstrynge Mar 24 '14 at 15:39
I'd be interested to see why a table is better. Wouldn't that table become "hot"? – Kuberchaun Mar 24 '14 at 17:02
@Just I'm declaring the function [`immutable`](http://www.postgresql.org/docs/current/static/sql-createfunction.html) – Clodoaldo Neto Mar 24 '14 at 17:11
@Just If by _hot_ you mean updateable by the users then the adequate permissions should be set. – Clodoaldo Neto Mar 24 '14 at 17:12
I just mean if you have a system with 500 users and you make use of this table heavily it will have lots of io against it. Be it reads or writes. Don't get me wrong I like the approach I have just been guided away from this type of approach in the past by people with more weight than me, and I had to figure an other solution out. I never had the chance to test the "hottness" factor. – Kuberchaun Mar 24 '14 at 17:52
@JustKim See if you like the alternative. – Clodoaldo Neto Mar 24 '14 at 22:58

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

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

Create constant string for entire database

edited May 23 '17 at 12:33

Community

1
1

answered Mar 26 '14 at 15:35

Kuberchaun

29,160
7
51
59

Eerik Sven Puudist · Answer 4 · 2021-07-13T12:02:15.187

0

You can declare a table named constants for that purpose as mentioned that answer where each column corresponds to one setting.

To ensure that no more than one row can be added to the table, this answer might be helpful.

As come comments have pointed to the increased I/O when constants are stored like this as opposed to storing constants as functions, this might be a good reeding.

edited Jul 13 '21 at 12:02

answered Jul 11 '21 at 08:27

Eerik Sven Puudist

2,098
2
23
42

Defining global constants in Postgresql stored function/procedures?

4 Answers4