create tables with a bash file from (Postgres) sql select statement stored in files

Question

Given a set of complex, consecutive (Postgres) SQL select statements, each stored in a single sql-file, how can I write a bash-script dropping (if exists) and creating a table with the results of each statement having the same table name as file name.

background: we share these sql-files in a git repo where different users want to use the statements in a different way. I want automatically create tables, the others use temp tables, thus I dont want to write the 'create table...' in the sql-file's headers.

• user330315: I don't know much about bash scripting, but something like this might work: loop over all files, "echo" a drop table <filename> to an intermediate SQL script. At the same time echo a create table <filename> to another script and append the contents of the "current" file to that "header". Then run all the drop scripts and the create scripts.
• wildplasser: In most cases, the order in which you submit the separate .sql files will be important, so you cannot automate this tasks wihtout having a summary/toc file/script that dictates this order. (a naming convention might help, but can lead to extreme uglyness)
• Berlin_J: You are right, the order is important. So, how can i create tables with the mentioned summary/toc file/script??

Answer 1

A skeleton for your shell script could look like this:

set -e # stop immediately on any error
for script in s1 s2 s3
do
 echo "processing $script"
 select=`cat $script`
 psql -d dbname -U user <<EOF
DROP TABLE IF EXISTS "$script";
CREATE TABLE "$script" AS $select ;
EOF
done

Note however that any SELECT is not necessarily suitable as the source for a CREATE TABLE .. AS SELECT...

As the simplest example, consider the case when two different columns share the same name. This is legal in a SELECT, but an error condition when creating a table from it.

• Berlin_J: thanks, that's it! for giving the tables just the file's names (without path and extension) i used:stackoverflow.com/questions/3362920/…