pgFormatter can format a SQL file according to the options. It can also be used as a CGI. Also is simple to use. The basic use is as follows.
pg_format [options] file.sql
There are various applications and websites that do this job in the market. You’re pasting the query, you’re getting formatted.
So, what do we need the pgFormatter?
In Linux environments, we use text editors such as vi, vim or nano when we want to modify a script while working on ssh.
vim can automatically colorize files with sql extension. This section is known by everyone. What most people don’t know is that vim has text formatting capabilities. We call them gq commands in general.
However, vim does not know the sql format when formatting text. She’s formatting it as a straight paragraph.
All we have to do is tell vim to use pgformatter when using gq commands on SQL files.
First, let’s use pg_format alone. I’ll use .sql files in the pg_format sample folder in my examples;
Format and save the output as a new file. Then look at our file again.
pg_format --output pgFormatter-2.0/samples/ex0_formatted.sql pgFormatter-2.0/samples/ex0.sql
As a note; You can also use pgFormatter to remove all of the comments in your sql file. You can use the –nocomment parameter for this.
Now let’s do formatting on vim. We need to tell vim how to format it before this.
Edit vim profile file;
Edit the contents as follows;
au FileType sql setl formatprg=/usr/local/bin/pg_format\ -
It should be as follows:
We color the vim in the first line. In the second line, we tell vim to use pg_format as the format application in sql files.
Now vim (or vi) knows how to format sql files. Let’s try;
Press letters starting with gq respectively; Note that the end G is a upper case. Do not press plus (+). This will format the entire document.