Interpolation of environment variables in SQL at runtime?

[Andy Gimblett]

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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Andy Gimblett <A.**********@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Andy Gimblett <A.**********@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)