On Thu, Apr 25, 2013 at 1:17 AM, Darren Duncan <dar...@darrenduncan.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
