How to include files relative to the current executing script in psql?

Question

I have a PostgreSQL script (say, MAIN.sql in ~/sql/) which has lines like

\i components/helper-functions.sql

This include works fine if the $PWD is the same as the directory of my script (~/sql/), but if it is not, it looks for the included file relative to the $PWD instead of relative to MAIN.sql.

So if I call the script from ~/, it would look for ~/components/helper-functions.sql and not for ~/sql/components/helper-functions.sql.

I think a new \ir directive is going to be included in 9.2 for exactly this problem, but I'm running 8.3

Answer 1

Pass the directory name in as a psql variable and use that to assemble the absolute path to included files, e.g.,

$ cat ./tmp/foo.sql
\echo 'This is foo.'
\set abs_bar_sql :DIR '/bar.sql'
\i :abs_bar_sql

$ cat ./tmp/bar.sql
\echo 'This is bar.'

$ psql -f ./tmp/foo.sql -v DIR=$PWD/tmp
This is foo.
This is bar.

It's not pretty, but that's why \ir is being added after all.