A script to connect to a remote (or local) postgres server to execute queries and handle the results in csv form.

postgres_remote_queries.sh

#!/bin/bash

#Script to run automated sql queries

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/'

sqlfilecat=

if [ $# -gt "1" ]
then
	db_host="$1"
	db_port="$2"
	db_user="$3"
	db_pw="$4"
	db_name="$5"
	sqlfile="$6"
	out_path="$7"
fi

sqlfile_expanded="$(cat ${sql_file})"
stripped_sql_file_name=$(echo ${sql_file} | rev | cut -c 5- | rev)

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_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"

echo "End of script"
exit 0