Formatting SQL code with pgFormatter within EmacspgFormatter is a great Perl 5 tool that parses SQL input and re-format it in a beautiful way.
Despite the name, it works with any SQL piece of code, since it does support the standars from SQL-92 to SQL-2011, plus all little keywords and details that are specific to PostgreSQL.
Being myself an Emacs addicted, I reasoned about how to “pkug in”
pgFormatterinto Emacs, and I came up with a short and ugly snippet of code that does the trick.
But, being Emacs what it is, there is no particular need to plug in such code, as I will show you in a moment.
Emacs allows users to run a shell command over a region or a buffer content. The
pgFormatter from Emacs, the portable way
M-|(menomic: pipe) does that. With the universal prefix (
C-u) it can also replace the region or buffer you are running the command against.
This means that, given your own Emacs instance, you can format the code within the region by simply doing
C-u M-| pg_format
pg_formatis the name of the executable of pgFormatter (e.g., it is called like that in Rocky Linux).
A more lispy approachI developed a simple and ugly snippet of Lisp that can be loaded into Emacs to make the pgFormatter usage quicker.
(defun pgformatter-on-region () "A function to invoke pgFormatter as an external program." (interactive) (let ((b (if mark-active (min (point) (mark)) (point-min))) (e (if mark-active (max (point) (mark)) (point-max))) (pgfrm "/usr/bin/pg_format" ) ) (shell-command-on-region b e pgfrm (current-buffer) 1)) )
The above piece of code defines an interactive function (i.e., a function that can be invoked with
pgformatter-on-region. The function defined three variables:
bis the beginning of the region to format;
eis the end of the region to format;
pgfrmis the path to the executable to invoke.
mark-active) the function operates over the region, otherwise, if no region is applied, it operates on the whole buffer.
In the end, the function invokes the shell command
pgfrmusing the internal interactive function
shell-command-on-regionover the current buffer. The last argument,
1indicates that I want to substitute the content of the current region (or buffer) with the command output.
In order to execute the formatting, I then just need to
M-x pgformatter-on-regionwith either an active region or not. It is also possible to bind the function to a keyboard sequence with something like:
(global-set-key (kbd "C-i") 'pgformatter-on-region)
or a local key map entry.
The ending result is something like the following: