How can I run a script with nzsql that calls other scripts using \i with paths specified relative to the path of the calling script?

The solution must work whether nzsql is being run interactively or non-interactively.

For example suppose run.sql, gather_data.sql, and generate_report.sql are all in /home/my_user/sql/generate_report.

-- run.sql
\set caller_path <magic_here>
\set gather_data_sql :caller_path / gather_data.sql
\set generate_report_sql :caller_path / generate_report.sql
\i :gather_data_sql
\i :generate_report_sql

If I could fill in <magic_here> I would be able to call

\i /home/my_user/sql/generate_report/run.sql 

no matter what the current working directory was of nzsql.

I want to accomplish this without having to specify absolute paths or paths relative to cwd for scripts called by other scripts, but so far nzsql has thwarted my every attempt. Is there something I'm missing?


Oracle has an sqlplus feature @@ that automatically does this for you.

Even better would be a way to change the working directory, but still there needs to be a way to determine the path of the calling script.

The only solution I have found involves processing the history file with shell commands to find the last \i and creating and calling a temporary script with \set caller_path ..., however this does not work if nzsql is being run non-interactively because commands are not saved to the history file in that case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.