On TERENA systems, this script is located at
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.
Or 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:
For this work, you need enough privileges, so su to user postgres before running this.
Also, you need a working mutt installed.
In case it wasn't clear, this script is meant to view data, which implies SELECT. So don't do stupid things like UPDATE or DELETE.
CSV files with e-mail addresses
The generated CSV files can be used by the Mailtweak Thunderbird plugin, so that a mail merge can be done by end users.
Unfortunately this process is a bit tricky when names include UTF-8 characters:
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 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, by adding the Perl procedural language. I needed to use the "unsafe" version, so that libraries can be used.
To do this:
- Install the PL/Perl procedural language for PostgreSQL:
apt-get -s install postgresql-plperl-8.4
Add it to your database. You need postgres superuser privileges to do so:
Add the following function to your database:
At this point you can use your new function to generate nicely formatted MIME headers:
MailTweak and Thunderbird now can digest the addresses, and UTF-8 will work OK