9

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?

Mehran
  • 15,593
  • 27
  • 122
  • 221
  • Any kind, but for now a boolean will do – Mehran Dec 26 '15 at 23:07
  • 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 Dec 26 '15 at 23:12
  • 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 Dec 26 '15 at 23:17

1 Answers1

10

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • Thanks, I went as far as setting my custom entry but then I only tried `pg_settings` and could not find my entry there so I thought there should be a module defining the entry before I can use it. – Mehran Dec 26 '15 at 23:26
  • can It be removed? – Diego Jun 24 '17 at 05:13
  • @DIEGOF.G. - You cannot remove a local custom parameter (set in a current session). For a database - use `alter database test reset my_param.new_param;` (the effect will be visible in next sessions, not in the current one) – klin Jun 24 '17 at 09:46