/* * Author: Fabrízio de Royes Mello * Created at: Thu Oct 27 14:37:36 -0200 2011 * */
CREATE FUNCTION set_value(TEXT, TEXT) RETURNS void AS $$ BEGIN PERFORM set_config('session_variables.'||$1, $2, false); RETURN; END; $$ LANGUAGE plpgsql; COMMENT ON FUNCTION set_value(TEXT, TEXT) IS 'Create/Assign value to a new/existing session variable';
SET check_function_bodies TO OFF; CREATE FUNCTION get_value(TEXT) RETURNS TEXT AS $$ SELECT current_setting('session_variables.'||$1); $$ LANGUAGE sql; COMMENT ON FUNCTION get_value(TEXT) IS 'Returns the value of session variable passed as a parameter';
I'll take that as several people endorsing set_config()/current_setting() as a preferred way to do this.
I use this feature to store global session variables for a long time... In my first implementation of this feature I used temp tables, but this caused catalog bloat. So I had to change this strategy using set_config/current_setting functions and it has worked fine since then.
The main limitation seems to be that those builtins just store and return text values, but a little casting on store/fetch should take care of that.
The temporary table approach wouldn't need casting in contrast.
To solve this you can extend this extension... ;-)
CREATE FUNCTION get_value_as_integer(TEXT) RETURNS INTEGER AS $$ SELECT CAST(get_value($1) AS INTEGER); $$ LANGUAGE sql;
