Search Postgresql Archives

Re: custom session variables?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux