clathrop · August 29, 2015 14:26 · Aug 4, 2015 · Aug 4, 2015 · Aug 4, 2015
diff --git a/postgres_remote_queries.sh b/postgres_remote_queries.sh
@@ -10,7 +10,7 @@ db_name='newsroom'
 sql_file='loadItems.sql'
 out_path='/Users/clathrop/work/sqlscripts/out/'
 
-sqlfile=$(cat ${sql_file})
+sqlfilecat=
 
 if [ $# -gt "1" ]
 then
@@ -19,29 +19,33 @@ then
 	db_user="$3"
 	db_pw="$4"
 	db_name="$5"
-	sqlfile="$(cat $6)"
+	sqlfile="$6"
 	out_path="$7"
 fi
 
+sqlfile_expanded="$(cat ${sql_file})"
 stripped_sql_file_name=$(echo ${sql_file} | rev | cut -c 5- | rev)
 
-echo "stripped_sql_file_name = $stripped_sql_file_name"
-
-echo "$db_host"
+echo "host: $db_host"
+echo "port: $db_port"
+echo "postgres user: $db_user"
+echo "password: $db_pw"
+echo "database name: $db_name"
+echo "SQL file to run: $sqlfile"
+echo "output file: ${out_path}${stripped_sql_file_name}.csv"
 
 # Connect to remote DB, providing explicit query and output results into file
 #db_query="select objectid from jiveobjecttagset limit 10"
 #psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "$db_query" > file.txt
 
-
 # Connect to remote DB, use execute SQL statement from file, and output results into file
 #psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -f "$path_to_file" > file.txt
 
 # Connect to remote DB, execute sql statement and output result to comma (default) delimited csv file
 #psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "COPY (select objectid from jiveobjecttagset limit 10) To '/tmp/csvfile.csv' With CSV" > file.txt
 
 # Connect to remote DB, execute sql statement and output result to tab delimited csv file
-psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "COPY ($sqlfile) To '${out_path}${stripped_sql_file_name}.csv' (format csv, delimiter E'\t')" > /dev/null
+psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "COPY ($sqlfile_expanded) To '${out_path}${stripped_sql_file_name}.csv' (format csv, delimiter E'\t')" > /dev/null
 
 echo "Script completed, file is generated at ${out_path}${stripped_sql_file_name}.csv"
 

diff --git a/postgres_remote_queries.sh b/postgres_remote_queries.sh
@@ -1,7 +1,6 @@
 #!/bin/bash
 
 #Script to run automated sql queries
-#Declaring mysql DB connection 
 
 db_host='localhost'
 db_port=5432

diff --git a/postgres_remote_queries.sh b/postgres_remote_queries.sh
@@ -0,0 +1,50 @@
+#!/bin/bash
+
+#Script to run automated sql queries
+#Declaring mysql DB connection 
+
+db_host='localhost'
+db_port=5432
+db_user='postgres'
+db_pw=''
+db_name='newsroom'
+sql_file='loadItems.sql'
+out_path='/Users/clathrop/work/sqlscripts/out/'
+
+sqlfile=$(cat ${sql_file})
+
+if [ $# -gt "1" ]
+then
+	db_host="$1"
+	db_port="$2"
+	db_user="$3"
+	db_pw="$4"
+	db_name="$5"
+	sqlfile="$(cat $6)"
+	out_path="$7"
+fi
+
+stripped_sql_file_name=$(echo ${sql_file} | rev | cut -c 5- | rev)
+
+echo "stripped_sql_file_name = $stripped_sql_file_name"
+
+echo "$db_host"
+
+# Connect to remote DB, providing explicit query and output results into file
+#db_query="select objectid from jiveobjecttagset limit 10"
+#psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "$db_query" > file.txt
+
+
+# Connect to remote DB, use execute SQL statement from file, and output results into file
+#psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -f "$path_to_file" > file.txt
+
+# Connect to remote DB, execute sql statement and output result to comma (default) delimited csv file
+#psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "COPY (select objectid from jiveobjecttagset limit 10) To '/tmp/csvfile.csv' With CSV" > file.txt
+
+# Connect to remote DB, execute sql statement and output result to tab delimited csv file
+psql -h $db_host -p $db_port -U $db_user -W $db_pw -d $db_name -c "COPY ($sqlfile) To '${out_path}${stripped_sql_file_name}.csv' (format csv, delimiter E'\t')" > /dev/null
+
+echo "Script completed, file is generated at ${out_path}${stripped_sql_file_name}.csv"
+
+echo "End of script"
+exit 0
No results found