Setting a configuration parameter for functions implemented in PL/pgSQL

Question

I've written a couple of functions in PL/pgSQL and I would like to control their behavior through some configuration entries, changeable at run time too (per session). Is it possible to define new custom-defined configuration entries in postgresql.conf? If not, what's the work around?

As my search results, I came across the part of documentation which says:

18.16. Customized Options

This feature was designed to allow parameters not normally known to PostgreSQL to be added by add-on modules (such as procedural languages). This allows extension modules to be configured in the standard ways.

If this passage answers my question with a "No", can my PL/pgSQL functions be considered an extension module so that they can have their own configuration entries in the configuration file?

Perhaps there's been a misunderstanding, the configuration I'm looking for is absolutely tailored to my needs and has nothing to do with the PostgreSQL or PL/pgSQL behavior. It's something I would like to check in my code to enable / disable a part of code. — Mehran
I'm not sure what you mean by subsequent calls, but what I'm looking for is whether some part of the code is active or not. Consider an if statement checking whether some part of the code should be executed or not. It's as simple as that! — Mehran

klin klin · Accepted Answer · 2015-12-26T23:22:33

You can define your custom parameters in postgresql.conf. Just append a line (e.g.):

my_param.new_param = 'something'

and reload configuration (or restart server).

In your client you can access the parameter with show command:

SHOW my_param.new_param;

or with current_setting() function:

SELECT current_setting('my_param.new_param');

You can change the current parameter (locally in the session):

SET my_param.new_param TO 'new value';

It is also possible to define custom parameters for a database:

ALTER DATABASE test SET my_param.new_param TO 'new test value';
-- each new client to the database will see the parameter with new value
-- current setting of the parameter remains unchanged

-- or
SET my_param.new_param TO 'new test value';
ALTER DATABASE test SET my_param.new_param FROM CURRENT;

A custom parameter must contain a period. Formally, the prefix should indicate the extension to which it relates, but Postgres does not check it in any way. You can have many custom parameters.

Setting a configuration parameter for functions implemented in PL/pgSQL

1 Answers