 | |
8.3 and current_setting() of unset variables. Again
comp.databases.postgresql comp.databases.postgresql
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: 8.3 and current_setting() of unset variables. Again -
01-15-2008
, 02:16 AM
|
to access settings petersprc had this solution: select setting from pg_catalog.pg_settings where name = 'modwork.transaction_id' but this does not work. The table looks like this: custom_variable_classes | modwork | | Customized Options The variable 'transaction_id' is not accessible. How can you query for the value of the custom variable? PS: See old question some messages ago. I can confirm that the variable does not show up in pg_catalog.pg_settings, but I had ne problem setting and retrieving it with SET and SHOW: Yes, first setting it, then showing it is no problem for me, too. But how can I check if the variable was already set? With 8.1.4 I get 'unset' if the variable is not set. With 8.3beta3 I get an syntax error which can't be caught with plpythonu (inside a trigger). |
a PL/pgSQL function like this:
CREATE FUNCTION get_setting(IN param text, OUT value text)
LANGUAGE plpgsql STABLE STRICT AS
$$BEGIN
SELECT current_setting(param) INTO value;
EXCEPTION
WHEN UNDEFINED_OBJECT THEN
value := NULL;
END;$$;
which you can then use from your code.
It will return NULL if the configuration parameter is unknown or not set.
Yours,
Laurenz Albe
#32
| |||
| |||
|
|
to access settings petersprc had this solution: select setting from pg_catalog.pg_settings where name = 'modwork.transaction_id' but this does not work. The table looks like this: custom_variable_classes | modwork | | Customized Options The variable 'transaction_id' is not accessible. How can you query for the value of the custom variable? PS: See old question some messages ago. I can confirm that the variable does not show up in pg_catalog.pg_settings, but I had ne problem setting and retrieving it with SET and SHOW: Yes, first setting it, then showing it is no problem for me, too. But how can I check if the variable was already set? With 8.1.4 I get 'unset' if the variable is not set. With 8.3beta3 I get an syntax error which can't be caught with plpythonu (inside a trigger). |
a PL/pgSQL function like this:
CREATE FUNCTION get_setting(IN param text, OUT value text)
LANGUAGE plpgsql STABLE STRICT AS
$$BEGIN
SELECT current_setting(param) INTO value;
EXCEPTION
WHEN UNDEFINED_OBJECT THEN
value := NULL;
END;$$;
which you can then use from your code.
It will return NULL if the configuration parameter is unknown or not set.
Yours,
Laurenz Albe
#33
| |||
| |||
|
|
to access settings petersprc had this solution: select setting from pg_catalog.pg_settings where name = 'modwork.transaction_id' but this does not work. The table looks like this: custom_variable_classes | modwork | | Customized Options The variable 'transaction_id' is not accessible. How can you query for the value of the custom variable? PS: See old question some messages ago. I can confirm that the variable does not show up in pg_catalog.pg_settings, but I had ne problem setting and retrieving it with SET and SHOW: Yes, first setting it, then showing it is no problem for me, too. But how can I check if the variable was already set? With 8.1.4 I get 'unset' if the variable is not set. With 8.3beta3 I get an syntax error which can't be caught with plpythonu (inside a trigger). |
a PL/pgSQL function like this:
CREATE FUNCTION get_setting(IN param text, OUT value text)
LANGUAGE plpgsql STABLE STRICT AS
$$BEGIN
SELECT current_setting(param) INTO value;
EXCEPTION
WHEN UNDEFINED_OBJECT THEN
value := NULL;
END;$$;
which you can then use from your code.
It will return NULL if the configuration parameter is unknown or not set.
Yours,
Laurenz Albe
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.