3

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.

  • 2
    If you are using Postgres 9.6 you can simplify that to conf_param := current_setting('the_setting', true); In version before that you need to catch the exception – a_horse_with_no_name Feb 23 '17 at 21:08
4

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:

  • 1
    @RomanTkachuk: True, if we are not sure that the parameter is always valid numericif set. The OP's code has no provision for this, either. Assigning to a text variable and then casting does not buy anything here. I added some pointers to address the potential problem. – Erwin Brandstetter Feb 23 '17 at 22:44
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.