新的SQL格式化输出工具——基于解析，而不是regexp

很长一段时间，我一直在寻找SQL漂亮的打印机。

这些年来，我不得不处理的一些问题，比如：

SELECT
DECODE(a.category,NULL,b.category,a.category) "category",b.par "Total object Request",b.ps "objects Served",
b.ar "Total sushi Request", a.sushis "sushis Served",
ROUND((DECODE(b.ar,0,0,(b.ar - CAST(DECODE(a.sushis,NULL,0,a.sushis) AS numeric))/b.ar))*100,3) "USR",
a.clk "points", DECODE(b.ps,0,0,ROUND((a.clk/b.ps)*100,3)) "CTR",a.cpc "CPC", a.tc "Cost",
DECODE(b.ps,0,0,CAST((a.tc/b.ps)*1000 AS numeric(8,3))) "effectcost"
FROM
(SELECT
DECODE(b.category,NULL,'N/A',b.category) category, SUM(doughnuts) sushis, SUM(points) clk,
ROUND(CAST(SUM(total_cost) AS numeric),3) tc,
DECODE(SUM(points),0,0,ROUND(CAST((SUM(total_cost)/SUM(points)) AS numeric),3)) cpc
FROM
daily_city_dealer_summary a, category_dealer_map b
WHERE
a.category_dealer_id=b.category_dealer_id  AND   created_day BETWEEN '2010-05-01' AND '2010-05-25'
GROUP BY b.category) a     full outer join
 
(SELECT
DECODE(a.category,NULL,'N/A', DECODE(a.category,'-','World-Remaining countries',a.category)) category,
SUM(valid_object_request) par, SUM(valid_sushi_request) ar, SUM(object_doughnuts) ps
FROM
traffic_hit a
WHERE
request_date BETWEEN '2010-05-01' AND '2010-05-25'
GROUP BY a.category)  b
ON LOWER(a.category)=LOWER(b.category)
ORDER BY 4 DESC;

多年来，格式化查询的最佳方式（在我看来）是来自pgFormatter项目的pg_format程序。

这确实令人印象深刻，但有一个重要的缺点——重新格式化是通过应用一些相当复杂的regexp和定制的标记器来完成的。

我梦见了一些能够实际解析查询的东西，并基于解析树使用一些一致的逻辑重建查询,有libpg_query库，基于它的是Ruby GEM。这段代码使用从源代码中提取的Pg解析器。并将任意查询解析为相对容易使用的树。

基于此，我编写了解析器微服务，然后用perl编写了漂亮的打印机。

今天我改变了explain.com和paste.depesz.com使用此新代码进行漂亮打印。

如果一些数据无法解析，或者我的代码不支持它（目前），两个站点都会使用pgFormatter。

此外，我还添加了一种使用paste.depesz进行漂亮打印的方法，甚至不在paste.depesz上保存数据.com网站，使用/prettify url。

您可以这样使用它：

=$ echo 'select a from b' | curl -s -XPOST --data-urlencode "q@-" https://paste.depesz.com/prettify
SELECT
    a
FROM
    b;
-- Formatted by Pg::SQL::PrettyPrinter

您可以自己编写一个简单的脚本，如/usr/local/bin/prettify。sh内容：

#!/usr/bin/env bash
 
curl -s -XPOST --data-urlencode "q@${1:--}" https://paste.depesz.com/prettify

然后您也可以将其用于文件：

=$ prettify.sh z.sql 
SELECT
    123;
-- Formatted by Pg::SQL::PrettyPrinter

所有经过修饰的SQL（通过/prettify，或在explain.depesz.com上，或在

paste.depesz.com将在最后一行包含注释，说明使用了哪个美化库。

因此，如果您的查询没有被Pg：：SQL：：PrettyPrinter修饰，那么您认为应该这样做。或者是，但方式不正确，请通过填写问题与我联系。

目前只处理DML查询（选择、插入、更新、删除），但我将在将来添加DDL。

希望你会发现它有用。

此外，我还要感谢亚历山大·费利佩（bob）和安德烈亚斯·舍尔巴姆（Andreas‘ads’Scherbaum）督促我完成这项工作。如果没有你，我可能不会这么做

最后，作为一个快速示例，在使用Pg SQL PrettyPrinter格式化后，从post开始的查询如下所示：

SELECT
    decode(
        a.category,
        NULL,
        b.category,
        a.category
    ) AS category,
    b.par AS "Total object Request",
    b.ps AS "objects Served",
    b.ar AS "Total sushi Request",
    a.sushis AS "sushis Served",
    round(
        decode(
            b.ar,
            0,
            0,
            ( b.ar - decode( a.sushis, NULL, 0, a.sushis )::pg_catalog.numeric ) / b.ar
        ) * 100,
        3
    ) AS "USR",
    a.clk AS points,
    decode(
        b.ps,
        0,
        0,
        round(
            ( a.clk / b.ps ) * 100,
            3
        )
    ) AS "CTR",
    a.cpc AS "CPC",
    a.tc AS "Cost",
    decode(
        b.ps,
        0,
        0,
        ( a.tc / b.ps ) * 1000::pg_catalog.numeric( 8, 3 )
    ) AS effectcost
FROM
    (
        SELECT
            decode(
                b.category,
                NULL,
                'N/A',
                b.category
            ) AS category,
            sum( doughnuts ) AS sushis,
            sum( points ) AS clk,
            round(
                sum( total_cost )::pg_catalog.numeric,
                3
            ) AS tc,
            decode(
                sum( points ),
                0,
                0,
                round(
                    sum( total_cost ) / sum( points )::pg_catalog.numeric,
                    3
                )
            ) AS cpc
        FROM
            daily_city_dealer_summary AS a,
            category_dealer_map AS b
        WHERE
            a.category_dealer_id = b.category_dealer_id AND
            created_day BETWEEN '2010-05-01' AND '2010-05-25'
        GROUP BY
            b.category
    ) AS a
    FULL JOIN (
        SELECT
            decode(
                a.category,
                NULL,
                'N/A',
                decode(
                    a.category,
                    '-',
                    'World-Remaining countries',
                    a.category
                )
            ) AS category,
            sum( valid_object_request ) AS par,
            sum( valid_sushi_request ) AS ar,
            sum( object_doughnuts ) AS ps
        FROM
            traffic_hit AS a
        WHERE
            request_date BETWEEN '2010-05-01' AND '2010-05-25'
        GROUP BY
            a.category
    ) AS b ON lower( a.category ) = lower( b.category )
ORDER BY
    4 DESC;
-- Formatted by Pg::SQL::PrettyPrinter

也许并不理想，但这是一种改变。

斯蒂芬说：我认为格式化是人偏好的问题，我更喜欢使用新的格式化程序的case语句，因为它更多地在一行中。然而，我个人更喜欢在一行（或逻辑上属于一起的内容）上解码，而不是分散在这么多行中。但现在我必须搜索pg文档，我认为在这种情况下解码在Postgres中不存在，只有Oracle可以。

原文标题：New SQL pretty printer – based on parsing, and not regexps
原文链接：https://www.depesz.com/2022/08/31/new-sql-pretty-printer-based-on-parsing-and-not-regexps/