25

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.

Alexander
  • 1,495
  • 2
  • 19
  • 24
  • 1
    For anyone looking for a complete example, I created [a gist of a working an auditing mechanism](https://gist.github.com/stellingsimon/f09005f666b01f2560d73f9e603b2a97) leveraging `SET LOCAL` and `current_settings`. Hope you find it useful :-) – blubb Jan 04 '18 at 21:27
  • @blubb: awesome – Eugen Konkov Aug 13 '18 at 14:46

2 Answers2

31

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Did you ever get any traction on this? – toxaq Jul 23 '15 at 03:43
  • 2
    @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](http://www.postgresql.org/docs/devel/static/functions-admin.html). According to [this blog post](http://adpgtech.blogspot.de/2015/04/dynamically-disabling-triggers-without.html), the patch is by David Christensen (thanks!). – chirlu Sep 03 '15 at 18:05
  • 2
    @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. :-/ – chirlu Sep 03 '15 at 19:29
  • 1
    @chirlu Gutted. It would make some really nice options available in terms of passing values from an app into PG. – toxaq Sep 03 '15 at 23:13
  • @toxaq Bring it up on -hackers then. – Craig Ringer Sep 03 '15 at 23:20
  • 10
    The second `missing_ok` param is available in 9.6 now FYI – Tim Dec 01 '16 at 23:07
  • @Tim -- this is gold. This quesiton, and the additional parameter is EXACTLY what I was looking for. Thanks. – ortonomy Jan 28 '18 at 13:51
1

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();
VBAGuy
  • 172
  • 1
  • 9