Re: custom session variables?

On Thu, Apr 25, 2013 at 1:17 AM, Darren Duncan <darren(at)darrenduncan(dot)net>wrote:

> On 2013.04.24 7:16 PM, � wrote:
>
>> Maybe you must see this extension [1] ;-)
>>
>> [1] http://pgxn.org/dist/session_**variables/<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<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.
>
>
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;

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello