Formatting Ad-Hoc Reports in PostgreSQL
OR/M and noSQL guys see SQL as an awkward way to store and retrieve objects from the database. Here at Vertabelo, we see SQL as a useful language to query data. Making reports in pure SQL is a pure pleasure. Running an interactive query is almost the same experience as asking a human to provide information.
At some point you’ll have a need to write a report in a few seconds. Running IDE and coding is not a viable option. In this article I’ll show you how to perform and format ad-hoc reports using a PostgreSQL command line client. I’ll focus on formatting options so examples will be simplified.
Here we go.
Case #1 – Single User Details
You have to retrieve information about one user and email it ASAP.
Here is a query:
select * from user_account wher id = 10101;
And the default output is:
Which is unreadable. Fortunately, there is an “\x” option which transposes output.
\x select * from user_account wher id = 10101;
The output this time is far more readable than previously:
The “\x” option is really usefull if you have a lot of columns to show.
Case #2 – List of Users in a Readable Format
You have to make a list of your users and send it to the marketing department. Marketing guys are not used to geeky console outputs; they prefer graphical formats. HTML is a common format nowadays. PSQL can output rows as HTML. Here is a example script:
# switch to HTML output \H # save output as report.htm \o report.html # add some HTML at the begining \qecho # execute query select * from user_account # add some HTML at the end of the document \qecho # quit \q
I won’t try to describe all of PSQL’s options since the PostgreSQL documentation covers them very well. I just wanted to show you that quick & dirty solutions are good enough in some cases. PSQL has a lot of options. In addition to the options shown here, you can experiment with the “\f” option to generate a CSV file or the “\set” option to parametrize queries. Have fun!