1

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
Berlin_J
  • 367
  • 1
  • 3
  • 15
  • I don't know much about bash scripting, but something like this might work: loop over all files, "echo" a `drop table ` to an intermediate SQL script. At the same time echo a `create table ` to another script and append the contents of the "current" file to that "header". Then run all the drop scripts and the create scripts. –  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

1 Answers1

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.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • thanks, that's it! for giving the tables just the file's names (without path and extension) i used:http://stackoverflow.com/questions/3362920/bash-script-get-just-filename-from-path – Berlin_J Dec 02 '13 at 10:26