Postgresql – Postgres admin GUI that can execute a master file containing multiple sql files

postgresqlpsql

I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.

example: master.sql

contents (I don't know the syntax to use):

file1.sql
file2.sql
file3.sql

I've found several tutorials about using psql -f in the command-line and \i to include these files, but the preference here is to avoid the terminal and use a GUI to manage my database and execute the master.sql script.

Is this possible in any postgres admin GUI? Presumably it's not in pgAdmin.

Best Answer

A psql script

A psql script can index multiple files, let's assume 01_mydb.psql, is in the current working directory, and you have a directory 01 that has the files 01_schema.sql and 02_types.sql.

01_mydb.psql may look like this.

\i 01/01_schema.sql
\i 01/02_types.sql

Or however you want to structure it. If things get more complex and need more order, add more subdirectories

\i 01/10_tables/01_foo.sql
\i 01/10_tables/02_bar.sql

Then you would just either..

  1. Add an index file in 01, something like ./01/10_tables.psql
  2. Or, just add them all to 01_mydb.sql

I'll show the second method using find,

Using find to generate a master-load script.

Let's go ahead and create that structure.

01/
├── 01_schema.psql
├── 02_types.psql
└── 10_tables
    ├── 01_foo.psql
    └── 02_bar.psql

Here is the commands we use to create it.,

mkdir 01
touch 01/01_schema.sql
touch 01/02_types.sql
mkdir 01/10_tables
touch 01/10_tables/01_foo.sql
touch 01/10_tables/02_bar.sql

Now you can use find to generate a load script

find ./01/ -type f -printf '\\i %p\n' | sort | tee master.psql
\i ./01/01_schema.sql
\i ./01/02_types.sql
\i ./01/10_tables/01_foo.sql
\i ./01/10_tables/02_bar.sql

Now just run master.psql;

psql -d database -f master.psql
Related Question