Loading a list of SQL scripts with relative paths
Hello,
I often test SQL in separate text files, loading them into the server
using psql -d -f from the command line, wrapping the SQL in a BEGIN;
... ROLLBACK; transaction until I'm sure it does what I want. Often the
SQL script is just a small part of a larger chunk of work I'm doing on
the development server, so I'll have a number of these files that I'll
want to apply to the production server at a single time. I've been
trying to figure out an efficient way to load all of the scripts
together.
One option is to copy all of the scripts into one file, which I have
done on occasion. What I've been doing more recently is making an
additional file that uses \i to call the other files, for example
\i /path/to/script1
\i /path/to/script2
\i /path/to/script3
This works well, but I think I'll have to change all of the paths when
I move the group of scripts to the production server to load them. What
I'd like to do is be able to use paths relative to the file that
contains the \i commands. This doesn't seem to work when using \i.
I imagine that I'm not alone in wanting to load a bunch of scripts at a
go, and I'm wondering how others handle this situation. Advice?
Suggestions? What works for you?
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann wrote:
This works well, but I think I'll have to change all of the paths
when I move the group of scripts to the production server to load
them. What I'd like to do is be able to use paths relative to the
file that contains the \i commands. This doesn't seem to work when
using \i.
The paths are, as you probably found out, relative to the current
working directory. Since the path of the current script is not exposed
as a variable or something like that in psql, I don't think you can do
any computations within psql to achieve what you want. Others may have
ideas how you can organize your scripts differently, though.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
On Tuesday 10 May 2005 17:24, Michael Glaesemann wrote:
This works well, but I think I'll have to change all of the paths
when I move the group of scripts to the production server to load
them. What I'd like to do is be able to use paths relative to the
file that contains the \i commands. This doesn't seem to work when
using \i.I imagine that I'm not alone in wanting to load a bunch of scripts at
a go, and I'm wondering how others handle this situation. Advice?
Suggestions? What works for you?
I had a similar problem, which I solved by using environment variables:
After scrutinizing the psql documentation at
<http://www.postgresql.org/docs/8.0/static/app-psql.html>, I found
that this actually works:leif=> \set importdir `echo $IMPORTDIR`
leif=> \echo :importdir
/home/leif/slekta/import/scriptsThis doesn't:
leif=> \i :importdir/test.sql
\i: extra argument "/test.sql" ignoredBut this does:
leif=> \cd :importdir
leif=> \i test.sqlSo, the problem is solved, sort of. It may also be prudent to save the
old pwd and return there when the work is done:leif=> \set olddir `echo $PWD`
leif=> \set importdir `echo $IMPORTDIR`
leif=> \cd :importdir
leif=> \i test.sql
leif=> \cd :olddir
Here's the whole thread:
<http://www.mail-archive.com/pgsql-general@postgresql.org/msg60216.html>
--
Leif Biberg Kristensen
http://solumslekt.org/
On May 11, 2005, at 3:58, Leif B. Kristensen wrote:
On Tuesday 10 May 2005 17:24, Michael Glaesemann wrote:
This works well, but I think I'll have to change all of the paths
when I move the group of scripts to the production server to load
them. What I'd like to do is be able to use paths relative to the
file that contains the \i commands. This doesn't seem to work when
using \i.I imagine that I'm not alone in wanting to load a bunch of scripts at
a go, and I'm wondering how others handle this situation. Advice?
Suggestions? What works for you?I had a similar problem, which I solved by using environment variables:
<snip />
Here's the whole thread:
<http://www.mail-archive.com/pgsql-general@postgresql.org/
msg60216.html>
--
Thanks, Leif, for the great summary of your technique! I'll definitely
give it a go.
Michael Glaesemann
grzm myrealbox com