Thread: 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/scripts > > This doesn't: > > leif=> \i :importdir/test.sql > \i: extra argument "/test.sql" ignored > > But this does: > > leif=> \cd :importdir > leif=> \i test.sql > > So, 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