Search code examples
databasepostgresqlconfiguration-filescustom-options

PostgreSQL : function set_config(unknown, bigint, boolean) doesn't exist


I am trying to use the current_setting() and set_config() functions for plpgpsq, to change the value of a custom setting I've added to my postgresql.conf file.

Here's what I added to the end of my postgresql.conf file :

IntegrityPackage.NestLevel = 0

After restarting the PostgreSQL service, here's what I get when I want to see the setting's value :

SELECT current_setting('IntegrityPackage.NestLevel');
    
current_setting |
----------------|
0               |

However, when I try to edit the value, I get an error message :

SELECT set_config('IntegrityPackage.NestLevel', 0, false);
    
ERROR: function set_config(unknown, bigint, boolean) doesn't exist.
Hint: No function corresponds to given name and argument types
You must add explicit type conversions

(Vaguely translated from french since the error message was in french)

There is very little documnetation about this, therefore I'm stuck and I can't think of another way to do this.

Edit - the exact error text (in psql 9.2 / postgresql 10.0) is:

ERROR:  function set_config(unknown, integer, boolean) does not exist
LINE 1: SELECT set_config('my.foo',obj_id::int ,false) FROM objects ...
                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Solution

  • This will work:

    SELECT set_config('IntegrityPackage.NestLevel', '0', false);