Обсуждение: Interpolation of environment variables in SQL at runtime?

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/

Andy Gimblett <A.M.Gimblett@swansea.ac.uk> writes:
> Question: is there any way to use environment variables (or something
> similar) in my saved SQL code, and have them expanded at runtime?

Only by preprocessing the script.  Have you considered something like

cat <<EOF
... WHERE $foo = ...
EOF | psql ...

            regards, tom lane

Andy Gimblett wrote:
> 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?

See plr_environ() in PL/R's pg_userfunc.c. You could probably rip it out
and use it standalone with reasonable ease (or perhaps modify it to
return a single requested environment variable).

Use it like this:

select value from plr_environ() where name = 'MANPATH';
            value
---------------------------
  /usr/local/pgsql-dev/man:
(1 row)

Joe