PostgreSQL – Assign Result of current_setting(‘the_setting’) to a Variable in PL/pgSQL

parameterplpgsqlpostgresql

I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not recognized (because it does not exist yet) the function dies. I want to evaluate if the variable was able to be assigned and if not set a null value to it.

This is what I tried:

   DECLARE
      conf_param text;
      num integer;

   BEGIN
      SELECT current_setting('the_setting') INTO conf_param;
         -- here is where dies when the_setting is not recognized!
      IF FOUND THEN
         num := conf_param::integer;
      ELSE
         num := NULL;
      END IF;
      -- more stuff

I am not sure if I am using Found the way is needed.

Best Answer

In Postgres 9.6 or later you can have this much simpler. Like a_horse commented, there is a new variant of current_setting() that takes a second parameter missing_ok, to suppress exceptions if the configuration parameter (poor man's "global variable") is not set. So no expensive error trapping required any more.

And you don't need another variable conf_param, you can cast the result of the function call directly:

DECLARE
   num integer;
BEGIN
   num := current_setting('the_setting', true)::int;
   -- more stuff
END;

This is assuming the config parameter is always a valid numeric string if set. If you are not completely sure about that, you can either add an EXCEPTION clause to trap possible casting errors after all. Or test the value before casting to rule out exceptions. Typically much cheaper. Detailed instructions:

Related Question