Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group