Postgres query result formatting in bash script send mail

Question

I have a bash script where I execute a Postgres query using psql client, store the result into a variable and send the result in email using mail utility.

Below are the bash scripts I have tried. 1:

#!/bin/bash
varresult=`psql -H -A -h host_name  -p port  -U usr -d mydb -t -c "select rpad(sch_name, 10, ' '), rpad(trs_name, 63, ' '), rpad(trs_type, 11, ' '), comments from table1
;"`
mail -s "testing email" someone@example.com <<< $varresult

2:

#!/bin/bash
mail -s "testing email" someone@example.com <<< `psql -H -A -h host_name  -p port  -U usr -d mydb -t -c "select rpad(sch_name, 10, ' '), rpad(trs_name, 63, ' '), rpad(trs_type, 11, ' '), comments from table1
;"`

Both the above scripts send same unformatted result set in email. The query result has 4 columns and are appearing without any format in the receiver's email. So I used rpad function in the query to align them properly, but in the email the result still looks unformatted. When I copy the email content to a text file, the content looks perfectly formatted.

How the result looks in email:

 COL1            | COL2           | COL3
 val_t        | test_rgt         | test cmts apr
 val_t        | test_t         | test cmts
 val_tr        | test_wgdt        | test cmts
 val_t        | test_hty       | test cmts stgr
 val_t        | test_sf        | test cmts gedf

How the result looks in notepad after copy/pasting the above result:

 COL1         | COL2          | COL3
 val_t        | test_rgt      | test cmts apr
 val_t        | test_t        | test cmts
 val_tr       | test_wgdt     | test cmts
 val_t        | test_hty      | test cmts stgr
 val_t        | test_sf       | test cmts gedf

Can someone help me on how to make the result look perfectly formatted in email?

Below is the mail version:

Heirloom Mail version 12.5 7/5/10.

I think setting a text/plain mime type will be sufficient. I'm not familiar with the `mail` utility you mention, though, can you include the output of `mail --version`? — AdamKG, Aug 18 '22 at 12:44
Kindly do add your tried code as your efforts in form of code in your question, which is highly encouraged on SO, thank you. — RavinderSingh13, Aug 18 '22 at 12:50
If you are using the`psql` client to execute your query, you can use the `-H` flag to generate html output. That output might be more consistently formatted in an email — j_b, Aug 18 '22 at 13:01
The query output will by raw html, but I think email clients should render the html appropriately. — j_b, Aug 18 '22 at 13:09
Again, without seeing how you invoke `mail`, we can't tell you how to fix your problem; but the first comment is almost certainly right. The solution will also depend on the `mail` version; there are three different heritages with different features and options in common use. — tripleee, Aug 18 '22 at 16:02

tripleee · Answer 1 · 2022-08-19T10:07:06.760

You are effectively asking the shell to remove all the spaces from the output from psql; see When to wrap quotes around a shell variable. Besides, that's basically a useless use of echo though with the here string, somewhat in disguise.

#!/bin/sh
psql -H -A -h host_name  -p port  -U usr -d mydb -t \
  -c "select rpad(sch_name, 10, ' '), rpad(trs_name, 63, ' '), rpad(trs_type, 11, ' '), comments from table1;" |
mail -s "testing email" someone@example.com

Capturing a large amount of data into a variable just so you can echo that variable once is also a waste of memory.

http://shellcheck.net/ basically identifies both of these problems, though you might need to do some digging to uncover exactly why they are wrong. Going forward, probably try this free service before asking for human assistance.

Without the <<< here string syntax, there is no longer any Bash-specific code in this script, so I changed the shebang to /bin/sh which on many platforms is a smaller, faster, and more secure alternative; perhaps see also Difference between sh and bash.

The HTML output from psql -H should also be marked in the email headers as text/html (the default is text/plain) but if your email client displays the HTML already, that's probably not strictly necessary if you can ensure that all recipients have a client which behaves like this, in spite of pertinent Internet RFCs.

Heirloom mailx does not seem to support overriding the Content-type: header from the command line, though it would contain logic to heuristically infer the content type if you sent the body as an attachment instead.

Alternatively, you could assemble a simple MIME message yourself, and pass it to sendmail. This is brittle for other reasons (mainly, you have to know what you are doing if you want to modify the code; and the location of sendmail is system-dependent. Look in /usr/sbin or /usr/libexec if you can't find it on your PATH).

( printf '%s\n' \
  "From: you <sender@example.org>" \
  "To: victim <someone@example.com>" \
  "Subject: testing email" \
  "MIME-Version: 1.0" \
  'Content-type: text/html; charset="utf-8"' \
  "Content-transfer-encoding: quoted-printable" \
  ""
  psql -H -A -h host_name  -p port  -U usr -d mydb -t \
    -c "select rpad(sch_name, 10, ' '), rpad(trs_name, 63, ' '), rpad(trs_type, 11, ' '), comments from table1;" |
  # really cheap quoted-printable encoding
  fold -w72 |
  sed -e 's/=/=3D/g;s/$/=/' ) |
sendmail -oi -t

For what it's worth https://stackoverflow.com/a/48588035/874188 expands on the different heritages of `mail`/`mailx` — tripleee, Aug 19 '22 at 09:41

Postgres query result formatting in bash script send mail

1 Answers1