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.
Table of Contents
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:
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATEORREPLACEFUNCTIONformat_sql(text)
RETURNStextAS
$
DECLARE
v_ugly_stringALIASFOR$1;
v_beautytext;
v_tmp_nametext;
BEGIN
-- let us create a unique view name
v_tmp_name:='temp_'||md5(v_ugly_string);
EXECUTE'CREATE TEMPORARY VIEW '||
v_tmp_name||' AS '||v_ugly_string;
-- the magic happens here
SELECTpg_get_viewdef(v_tmp_name)INTOv_beauty;
-- cleanup the temporary object
EXECUTE'DROP VIEW '||v_tmp_name;
RETURNv_beauty;
EXCEPTIONWHENOTHERSTHEN
RAISEEXCEPTION'you have provided an invalid string: % / %',
sqlstate,sqlerrm;
END;
$LANGUAGE'plpgsql';
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:
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
test=#SELECTformat_sql('SELECT * FROM
pg_tables UNION
ALL SELECT * FROM
pg_tables');
format_sql
-------------------------------
SELECTpg_tables.schemaname,+
pg_tables.tablename,+
pg_tables.tableowner,+
pg_tables.tablespace,+
pg_tables.hasindexes,+
pg_tables.hasrules,+
pg_tables.hastriggers,+
pg_tables.rowsecurity+
FROMpg_tables+
UNIONALL+
SELECTpg_tables.schemaname,+
pg_tables.tablename,+
pg_tables.tableowner,+
pg_tables.tablespace,+
pg_tables.hasindexes,+
pg_tables.hasrules,+
pg_tables.hastriggers,+
pg_tables.rowsecurity+
FROMpg_tables;
(1row)
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:
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
test=#psetformatunaligned
Outputformatisunaligned.
test=#SELECTformat_sql('SELECT * FROM
pg_tables UNION
ALL
SELECT * FROM
pg_tables');
format_sql
SELECTpg_tables.schemaname,
pg_tables.tablename,
pg_tables.tableowner,
pg_tables.tablespace,
pg_tables.hasindexes,
pg_tables.hasrules,
pg_tables.hastriggers,
pg_tables.rowsecurity
FROMpg_tables
UNIONALL
SELECTpg_tables.schemaname,
pg_tables.tablename,
pg_tables.tableowner,
pg_tables.tablespace,
pg_tables.hasindexes,
pg_tables.hasrules,
pg_tables.hastriggers,
pg_tables.rowsecurity
FROMpg_tables;
(1row)
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:
PgSQL
1
2
3
test=#SELECTformat_sql('SELECT * FROM not_there');
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.
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?
Hans-Jürgen Schönig has worked with PostgreSQL since the 90's. He is the CEO and technical lead of CYBERTEC PostgreSQL International, a market leader in the field. He's served countless customers around the globe since the year 2000. He is also the author of the well-received "Mastering PostgreSQL" book series, as well as several other books about PostgreSQL replication and administration.
Nice solution! Unfortunately there is another limitation: temporary tables.
Aren't you missing a WHERE in the example select statement?
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?