up vote 1 down vote favorite

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.

share|improve this question
    
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. – a_horse_with_no_name Nov 30 '13 at 14:04
3  
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) – wildplasser Nov 30 '13 at 14:15
    
You are right, the order is important. So, how can i create tables with the mentioned summary/toc file/script?? – Berlin_J Nov 30 '13 at 18:15
up vote 1 down vote accepted

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.

share|improve this answer
    
You can use drop table if exists ... to prevent errors. – a_horse_with_no_name Nov 30 '13 at 19:52
    
thanks, that's it! for giving the tables just the file's names (without path and extension) i used:stackoverflow.com/questions/3362920/… – Berlin_J Dec 2 '13 at 10:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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