Re: custom session variables? - Mailing list pgsql-general

On 2013.04.24 7:16 PM, � wrote:
> Maybe you must see this extension [1] ;-)
>
> [1] http://pgxn.org/dist/session_variables/
>
> Fabrízio de Royes Mello

Thanks for your response.

====

/*
  * 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';

====

So, ok, basically the same as
http://frefo.blogspot.ca/2009/04/session-variables-in-postgresql.html which I
initially pointed to.

I'll take that as several people endorsing set_config()/current_setting() as a
preferred way to do this.

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.

Well, I can choose either then as the circumstances warrant.

Thanks for the feedback, I now consider my question to be answered.

-- Darren Duncan