Check for value with current_setting()
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();
Related videos on Youtube
Alexander
Updated on September 15, 2022Comments
-
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 useEXCEPTION
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 almost 6 yearsFor anyone looking for a complete example, I created a gist of a working an auditing mechanism leveraging
SET LOCAL
andcurrent_settings
. Hope you find it useful :-) -
Eugen Konkov about 5 years@blubb: awesome
-
-
toxaq over 8 yearsDid you ever get any traction on this?
-
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 casefoo
is undefined; see the devel-branch documentation. According to this blog post, the patch is by David Christensen (thanks!). -
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 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 about 8 years@toxaq Bring it up on -hackers then.
-
Tim almost 7 yearsThe second
missing_ok
param is available in 9.6 now FYI -
ortonomy almost 6 years@Tim -- this is gold. This quesiton, and the additional parameter is EXACTLY what I was looking for. Thanks.