psql has the ability to execute commands from a file, but if one wishes to develop and provide a modularized set of sql files, then psql is not very helpful because the \i command can open file paths either if they are absolute paths or if they are palced correctly relative to psql's current working directory.
Attached patch adds a new meta-command to psql, '\ir' that allows the user to process files relative to currently processing file.
Also attached is a sample use case ir_sample. One can extract it _anywhere_ on the filesystem and invoke psql with the path to main.sql and the rest of the files will be automatically included from that location.
Sample session:
[/tmp]$ psql -f ~/dev/ir_sample/main.sql
processing main.sql
BEGIN
processing subdir1/1.sql
processing subdir1/2.sql
processing subdir2/1.sql
processing subdir2/subdir2.1/1.sql
processing subdir2/2.sql
processing subdir2/3.sql
COMMIT
And here's what the sample's directory structure and files look like:
[ir_sample]$ find ./ -name "*.sql" | while read f; do echo === $f ====; cat $f; done
=== ./main.sql ====
\echo processing main.sql
BEGIN;
\ir subdir1/1.sql
\ir subdir1/2.sql
\ir subdir2/1.sql
\ir subdir2/2.sql
\ir subdir2/3.sql
COMMIT;
=== ./subdir1/1.sql ====
\echo processing subdir1/1.sql
=== ./subdir1/2.sql ====
\echo processing subdir1/2.sql
=== ./subdir2/subdir2.1/1.sql ====
\echo processing subdir2/subdir2.1/1.sql
=== ./subdir2/1.sql ====
\echo processing subdir2/1.sql
\ir subdir2.1/1.sql
=== ./subdir2/2.sql ====
\echo processing subdir2/2.sql
=== ./subdir2/3.sql ====
\echo processing subdir2/3.sql
Regards,
--
Gurjeet Singh
EnterpriseDB CorporationThe Enterprise
PostgreSQL Company