Automating database creation using psql

Question

I have a file that contains SQL commands for the creation of a database.

I also have a set of seperate files, each of which contain SQL commands for creating functions. For example:

• funcs_algebra.sql
• funcs_trigonometry.sql
• funcs_geometry.sql

Lets say my main SQLscript looks like this:

CREATE DATABASE mydb;
CREATE TABLE foo(id INT, name VARCHAR(32));
CREATE TABLE foobar(id INT, age REAL);
-- Commands below to import the functions in the separate files
-- funcs_algebra.sql
-- funcs_trigonometry.sql
-- funcs_geometry.sql

I want to know how to include the files in my 'main SQL' so that I have only one file to pass to psql.

The objective is to be able to use psql to create the database (complete with functions) by merely passing the commands in this file to psql.

Anyone knows how?

[Edit]

I probably should have stated what I thought was obvious. I want to keep the function related SQL in SEPARATE files so that I have only one source to modify. It is a way of me partitioning logic and keeping things DRY.

The function definitions are used for creating other template databases - so I want to keep them in separate files, but reference the files from within my SQL script.

The only other way I can think of doing this is writing a bash script that makes successive calls to psql - first create the database, and then add the functions - not very elegant, and not very DRY. Is there another (more elegant) way?

Answer 1

You can write:

\i funcs_algebra.sql

in your main SQL script to include the extra functions script.

• Milen A. Radev: A word of warning though: that will make your script unusable in anything but psql.