4

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.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
Kaz Miller
  • 255
  • 3
  • 10

2 Answers2

5

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:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
2

I had to surround that line in a block and catch the exception:

DECLARE
   num integer;

BEGIN
   BEGIN
      num := current_setting('the_setting')::integer;
   EXCEPTION
      WHEN SQLSTATE '42704' THEN
         num := NULL;
   END;
   --more stuff
Kaz Miller
  • 255
  • 3
  • 10