Postgres Atoms: Environment Variables


When I started looking into my "atomic i18n" prototype, I discovered three things:

  1. some of the procedural languages available for Postgresql, such as PL/Perl, have support for session-wide global variables
  2. the most common, PL/pgSQL, doesn't
  3. my shared host only had PL/pgSQL installed.

So I've written a simple pair of functions, implemented using only PL/pgSQL, that provide a generic interface for what I think of as "environment" variables. By wrapping them like this, I figure they could easily be replaced by completely equivalent - and much simpler - functions in PL/Perl, or whatever other language you may have to hand.

Indeed, I plan to write implementations in other languages as soon as I have the chance - feel free to contact me if you do so before I do!


The Pl/Pgsql implementation takes advantage of the way PostgreSQL handles temporary tables - namely, that they last for the entire "session" of queries, wherever they were created. So you can run one function that creates a temporary table, and another that accesses it, and they'll "see" the same table - but if you do that twice in separate "sessions", each session will have its own copy. Voilá, somewhere to put your environment variables.



void env.set(variable text, value text)

Save a value to a named "environment" variable. Variable names and values can both be any "text" value; to use any other data type, simply cast or serialize it as appropriate.

text env.get(variable text)

Retrieve a named "environment" variable. Will return null if the value has not been set in this "session".

Licensing and Contact Info

© Copyright Rowan Collins, 2009, but hereby licensed for use by whomever, for whatever purpose, with no limitation, and accepting no liability, expressed or otherwise.

Let me know what you think of it, and what you're doing with it… Mail me on pg-atom [[AAHTT]]