15

How do call multiple sql files in a single sql file, in postgres

For example I have aaa.sql, bbb.sql, ccc.sql.

I want to execute this 3 files from xxx.sql.

Can anyone suggest me how to do this.

Trip
  • 411

3 Answers3

16

If you are running these files through psql you want the \i directive ("execute commands from file").

xxx.sql:

\i aaa.sql
\i bbb.sql
\i ccc.sql

If you are passing these through some other program you will need to combine the files yourself - I do not believe there is any SQL-standard way of executing external files.

voretaq7
  • 80,391
  • Thank you for the reply. I will test and confirm if I was able to achieve it or not – Trip Feb 03 '12 at 22:13
  • Doesn't look like working yet Not sure if I am missing something. do I need semi colon at the end??? -Not Really !!! – Trip Feb 04 '12 at 00:04
  • 2
    You need to be more specific than "not working" - I assure you this does work -- Refer to the psql manual – voretaq7 Feb 04 '12 at 01:09
  • It worked. Finally :-) Thanku Problem was -- it was not able to recognize the file and O specifiled the relative path for the file and its working Thank you once again – Trip Feb 06 '12 at 15:34
  • @Trip Trip, don't forget to give voretaq credit for his answer! – Richard T Apr 12 '15 at 20:43
  • The current version also supports the \ir or \include_relative meta-command. When invoked from a script, \ir interprets file names relative to the directory in which the script is located, rather than the current working directory. – Clint Pachl Mar 19 '20 at 12:15
5

On a bash shell you can do it also with a simple find -exec 

find sql/ -name *.sql -exec psql -U user -f {} \;
Panciz
  • 251
4

Not exactly what you are asking for, but will serve your purpose: 1) Put all of your script files in a folder; and 2) use a bash script to iterate through your files and run psql. For example:

SCRIPTS_DIR=/home/myproject/scripts
DATABASE_NAME=database_name

for file in $SCRIPTS_DIR/*.sql
    do sudo -u postgres psql $DATABASE_NAME -f $file
done

This is in fact a little better because you won't have to type your files' names.

mehmet
  • 141