03aux_pgsql.sh

#!/bin/bash
# pomaha generovat SQL

mkdir data/nahled

tg="pg_aux.sql"
echo "-- autogenerated\n\n" > $tg

for file in data/*.csv.gz; do
	# sql create table
	echo "drop table if exists $(basename ${file%.*}); create table $(basename ${file%.*}) (" >> $tg
	printf "\t" >> $tg
	gunzip -c $file | head -n1 | sed -e $'s/,/ varchar,\\\n\\\t/g' >> $tg
	echo ");\n\n" >> $tg

	# file preview
	gunzip -c $file | head -n 100 > "data/nahled/$(basename ${file%.*})"

done

sed -i '' -e 's/\.csv//g' $tg

# id... jsou char(40) jen mimo ciselniky
# sed -i '' -e 's/\(id[a-zA-Z]*\) varchar/\1 char(40)/g' $tg
sed -i '' -e 's/\(dt[a-zA-Z]*\) varchar/\1 timestamp/g' $tg
sed -i '' -e 's/\([a-zA-Z]*Datum\) varchar/\1 timestamp/g' $tg
sed -i '' -e 's/\dPlatnost varchar/dPlatnost timestamp/g' $tg
sed -i '' -e 's/\(castka[a-zA-Z]*\) varchar/\1 float/g' $tg
sed -i '' -e 's/\([a-zA-Z]*Indikator\) varchar/\1 bool/g' $tg