• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Careers

  • Blog

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Formatting SQL code with pgFormatter within Vim

    Josh Tolley

    By Josh Tolley
    April 26, 2022

    Outdoor view of a creek bank with dry trees and old wooden buildings against a blue sky Photo by Garrett Skinner

    Sometimes a little, seemingly simple tip can make a world of difference. I’ve got enough gray hair these days that it would be pretty easy for me to start thinking I’d seen an awful lot, yet quite frequently when I watch a colleague working in a meeting or a tmux session or somewhere, I learn some new and simple thing that makes my life demonstrably easier.

    Luca Ferrari recently authored a post about using pgFormatter in Emacs; essentially the same thing works in Vim, my editor of choice, and it’s one of my favorite quick tips when working with complicated queries. I don’t especially want to get involved an editor war, and offer the following only in the spirit of friendly cooperation for the Vim users out there.

    As Luca mentioned, pgFormatter is a convenient way to make SQL queries readable, automatically. It’s easy enough to feed it some SQL, and get a nice-looking result as output:

    $ pg_format < create_outbreaks.sql
    INSERT INTO outbreak                      
    SELECT                              
        nextval('outbreak_id'::regclass),
        extract('year' FROM now())::text || '-' || nextval('outbreak_number_seq')::text, --number
        (                        
            SELECT  
                first_name
            FROM person TABLESAMPLE BERNOULLI (10)
    LIMIT 1), -- name
        NOW() - interval '1 day' * random() * 100, (
            SELECT
                id
            FROM "user" TABLESAMPLE BERNOULLI (10)
    ...
    

    In my perfect world I might quibble with some of its formatting decisions, such as the lack of indent on the LIMIT 1 line above. But in practice the results are good enough for my tastes that I haven’t bothered to investigate whether I can improve them. I just use it, and it’s good enough for me.

    And because Vim lets me highlight a region, pipe it through an external program, and replace the region with that program’s output, it’s easy to use it simply by selecting a section of code and typing :!pg_format like this:

    pgformatter example animation of terminal

    tips open-source tools postgres vim


    Comments