Check for value with current_setting()

17,014

Solution 1

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.

Solution 2

Please find the below example which i use while working with current_setting.

CREATE OR REPLACE FUNCTION public.usp_fetch_current_setting()
    RETURNS text
    LANGUAGE plpgsql
    AS $function$
    declare 
        v_appCode text;
    begin
        select current_setting('spm.appCode', 't') into v_appCode;
        return v_appCode;
    END;
$function$
;

while calling:

set session "spm.appCode" = 'spm-alignment-web';
SELECT public.usp_fetch_current_setting();
Share:
17,014

Related videos on Youtube

Alexander
Author by

Alexander

Updated on September 15, 2022

Comments

  • Alexander
    Alexander about 1 year

    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.

    • blubb
      blubb almost 6 years
      For anyone looking for a complete example, I created a gist of a working an auditing mechanism leveraging SET LOCAL and current_settings. Hope you find it useful :-)
    • Eugen Konkov
      Eugen Konkov about 5 years
      @blubb: awesome
  • toxaq
    toxaq over 8 years
    Did you ever get any traction on this?
  • chirlu
    chirlu about 8 years
    @toxaq: As far as I understand it, this feature will make it for 9.5 (though it's not in the alpha2 prerelease), in the form of an optional second parameter missing_ok: current_setting('foo', true) will not throw an error in case foo is undefined; see the devel-branch documentation. According to this blog post, the patch is by David Christensen (thanks!).
  • chirlu
    chirlu about 8 years
    @toxaq: Ugh; the patch was committed to the master branch, but not to the branch leading to the 9.5 release. So unless someone cherry-picks it for 9.5 (not sure if that can happen?), it seems the feature won't be available before 9.6, next year. :-/
  • toxaq
    toxaq about 8 years
    @chirlu Gutted. It would make some really nice options available in terms of passing values from an app into PG.
  • Craig Ringer
    Craig Ringer about 8 years
    @toxaq Bring it up on -hackers then.
  • Tim
    Tim almost 7 years
    The second missing_ok param is available in 9.6 now FYI
  • ortonomy
    ortonomy almost 6 years
    @Tim -- this is gold. This quesiton, and the additional parameter is EXACTLY what I was looking for. Thanks.