CYBERTEC Logo

Abusing PostgreSQL as an SQL beautifier

12.2019 / Category: / Tags: |

SQL is query a language that is usually pretty easy to read. However, if people don't format their queries properly even SQL turns out to be a nightmare. That's why developers often turn to an SQL beautifier to turn an ugly query into a nicely formatted string. Various tools are available on the web to achieve exactly that.

 

PostgreSQL as a SQL Beautifier

 

Can the same thing be achieved using only PostgreSQL onboard tools? The answer is yes. This post will show you how to achieve that.

How PostgreSQL handles views

In PostgreSQL a view is not stored as plain text. Instead, it is stored inside the system table in binary, machine readable format:

The pg_node_tree data type contains all the magic here. This makes a lot of sense because data is more directly accessible during query execution. In addition, it allows PostgreSQL to easily handle changing column names and so on without breaking views. Internally, PostgreSQL is only using an object ID, and therefore names, and so on don't matter at all. Views will not be invalidated by renaming tables or a column.

However, if you use d+: How does PostgreSQL then provide the definition of a view in human readable format? The answer is: PostgreSQL reassembles the query again. This mechanism can be used to format an SQL string and turn it into something more beautiful.

Keep in mind: The mechanism was never intended to do that, but it is a nice illustration of what can be done.

SQL Beautifier: Turning a view into a proper query string

The pg_get_viewdef function returns the definition of a view as a string. We can make use of that. Let's take a look at the following function:

What it basically does is to take a string and turn it into a temporary view. This view is then turned into a string again and dropped. Here is the function in action:

As you can see the string is returned in a proper format. PostgreSQL will even resolve the “*” for you and turn it into a proper column list. What you see here is that psql has added a + in case a newline appears. We can easily fix that and tell psql to change its behavior:

PostgreSQL has created a beautiful SQL string for us.

Limitations of the SQL Beautifier

Of course, this approach comes with a couple of limitations. First of all, the function will only work if you provide a query that actually has a chance of being executed. If tables don't exist, an error will be thrown:

This can be seen as a feature or as a problem - it depends on what you are trying to achieve. In addition to that comments will be removed. There is no way to prevent that from happening.

Further reading

If you want to learn more about views in PostgreSQL, I highly encourage you to check out our post about views and row-level-security.

If you want to read more about views and dependency tracking check out our post about that.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes
Article Rating
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Alexander Yuryshev
Alexander Yuryshev
3 years ago

That's a good trick!
But actually I'm quite dissatisfied with postgresql formatting and trying to find is there a way to change way how postgresql format SQL.
Do you have ideas for that?

Tobias Schmid
Tobias Schmid
4 years ago

Nice solution! Unfortunately there is another limitation: temporary tables.

Rafael Barros Félix
Rafael Barros Félix
4 years ago

Aren't you missing a WHERE in the example select statement?

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram