Page History

We often end up creating CSV files from the results of some specific PostgreSQL query, and mailing that around. This usually involves starting up a GUI database tool (we use EMS PostgreSQL manager), clicking through some windows, then saving a CSV file, then mailing it.

WouldnOr manually doing everything from a shell. But wouldn't it be nice if this could all be done in one go? The following shell script takes care of that:

...

#!/bin/bash
#
# Script to e-mail the output of an SQL query
# Dick Visser <visser@terena.org> 20120214
# $Id: pgmailcsv 191192 2012-02-1617 2210:3418:21Z27Z visser $
if [ $# -ne 3 ]; then
    echo "FAIL!!!!Error...
This script needs exactly THREE arguments:
    1. Database name
    2. E-mail address
    3. SQL query, surrounded by DOUBLE QUOTES
Quitting...Example:
$0 tnc2012 webmaster@terena.org \"SELECT fname FROM users\"
Long or complicated queries can be stored in an SQL text file:
$0 tnc2012 webmaster@terena.org \"\`cat complicated.sql\`\"
"
    exit
else
    OUTFILE="/tmp/PostgreSQL_Output_`date +%F_`date`%Hh%Mm%Ss`.csv"
    touch "$OUTFILE"
    # LATIN1 is needed, UTF8 borks out on CSV
    psql "$1" -Atc "COPY ($3) to '$OUTFILE' WITH CSV HEADER"
    echo "
    Please -en "\nPlease find attached the CSV formatted results of this SQL query to the '$1' database:
    \n
$3\n $1
    " | mutt -a "$OUTFILE" -s "PostgreSQL query results from '$1'" -- "$2"
    rm "$OUTFILE"
fi

For this work, you need enough privileges, so su to user postgres before running this.

...

In case it wasn't clear, this script is meant to view data, which implies SELECT. DonSo don't do stupid things like UPDATE or DELETE. 

CSV files with e-mail addresses

...

This is a problem with MailTweak, because it doesn't grok MIME encoding, so it will use this string verbatim in the mail header, which violates MIME specs, and the text will become garbage (as well as tripping spam filters upstreamwhich which screw up the text (and likely trip any upstream spam filters).

This can be fixed by using Perl's Encode::MIME::Header. I choose to do this from within PostgreSQL, but by adding the Perl procedural language. I needed to use the "unsafe" version, so that libraries can be used.

...