Interpolation of environment variables in SQL at runtime?

From: Andy Gimblett <A(dot)M(dot)Gimblett(at)swansea(dot)ac(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Interpolation of environment variables in SQL at runtime?
Date: 2004-10-28 12:29:02
Message-ID: 20041028122902.GB22647@cspcag.swan.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

Question: is there any way to use environment variables (or something
similar) in my saved SQL code, and have them expanded at runtime?

I can't see a way to do this in the manuals, and a colleague tells me
it can't be done, but I wanted to check, because if this _can_ be done
it will make my life significantly more optimal.

In more detail:

- I deal with postgres in one of two ways:

1. From an interactive emacs session, where I edit code in one
buffer, and send it to another one (in SQL mode) for
execution - which I imagine is an entirely familiar idea to
many readers of this list.

2. Using python programs which have SQL embedded directly in
them.

- Some of this code refers to files on disk, using absolute
pathnames. In particular, there are a lot of \i directives (ie to
include another .sql file).

- This code is shared with colleagues, and of course people have
different paths to the files involved.

- So, it would be really REALLY nice if I could rewrite things so
that:

\i /home/foo/some/path/to/root_of_files/some_file.sql

would be:

\i $ROOT_OF_FILES/some_file.sql

with $ROOT_OF_FILES referring to an environment variable, which is
then replaced with the value of that variable at the time that the
database executes the code. Then everybody who uses this code
just needs to ensure that ROOT_TO_FILES is set appropriately for
them, and everything works fine.

Hopefully what I'm asking is fairly obvious. But is it possible? I
can imagine some fairly unwiedly ways to do this involving
preprocessing, but would involve jumping through lots of hoops, and
I'd hope for a cleaner solution. Environment variables seems the
obvious "Unixy" approach...

Surely people have hit this problem before? How's it solved?

Many thanks for any advice!

-Andy

--
Andy Gimblett
Computer Science Department
University of Wales Swansea
http://www.cs.swan.ac.uk/~csandy/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gaetano Mendola 2004-10-28 12:43:04 Re: '1 year' = '360 days' ????
Previous Message Thomas Hallgren 2004-10-28 11:02:33 Re: Reasoning behind process instead of thread based