8

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.

Improve this question
8
  • You can edit your SQL files in any editor (there are much better options than pgAdmin) and then only run a single command in psql (say psql -f master.sql).
    – András Váczi
    Commented Mar 12, 2013 at 20:15
  • OK, then I will be clear: you presumably can't do that in pgAdmin. Anyway, I can't really see why this is a problem (I may be too used to this limitation).
    – András Váczi
    Commented Mar 13, 2013 at 8:41
  • Thanks for the comment. And you'll be able to see that it is a problem without a solution if you attempt to do what I want in pgAdmin. It may be a limitation (WADU) of pgAdmin, as you say, so I'll edit my question to open it up to asking for ANY postgres GUI that will do this.
    – jnmitch
    Commented Mar 13, 2013 at 18:34
  • 1
    SQL Workbench/J can do that: sql-workbench.net/manual/wb-commands.html#command-wbinclude
    – user1822
    Commented Mar 13, 2013 at 22:59
  • Thanks @a_horse_with_no_name. I noticed in the URL referenced that: "If the included SQL script contains SELECT queries, the result of those queries will not be displayed in the GUI". My scripts have many select statements. To confirm this disclaimer, I tested this product using the WbInclude command on a very simple script containing a select and confirmed it doesn't display the results. I wonder why? Anyway, aside from that, this product definitely works at calling other scripts within scripts.
    – jnmitch
    Commented Mar 14, 2013 at 7:23

1 Answer 1

Reset to default
2

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.