Check for value with current_setting()
postgresql, postgresql-9.2
Solution
9.6 and newer:
PostgreSQL (9.6+) supports `current_setting('setting_name', 't')` to fetch a setting and return `NULL` if it's unset. you can combine this with `coalesce` to supply a default.
9.5 and older:
Per the question, you can do it with a `plpgsql` function that uses a `BEGIN ... EXCEPTION` handler, if you don't mind the performance hit and clumsiness. But there's no built-in support.
Problem
I'm trying to work with `current_setting()`. I came up with this: ``` CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $audit$ DECLARE user_id integer; BEGIN BEGIN user_id := current_setting('hws.current_user_id'); EXCEPTION WHEN OTHERS THEN user_id := NULL; END; ... RETURN NULL; END; $audit$ LANGUAGE plpgsql; ``` The setting is set via: ``` SELECT set_config('hws.current_user_id', '5', true); -- true = local setting -> only visible in current transaction ``` The problem is, that `current_setting()` throws an exception if the value is not valid. I don't want to use `EXCEPTION` because I read that exception blocks are expensive. Is there a way to check if the setting has a value without using exceptions? Btw: I also tried to read from `pg_settings` but that doesn't seem to work with local settings.