Prettify SQL queries from command line

Around a month ago I wrote about new pretty-printer for SQL queries that I created.

Today, figured I'll add command line tool for putting queries through it, to make my life a bit easier.

Tool is named pg_prettify, and you can find in in my shell_utils repo.

Example usage:

=$ cat bad.sql 
SELECT n.nspname as "Schema", p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE
p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc'
ELSE 'func' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE
pg_catalog.pg_function_is_visible(p.oid) AND n.nspname <> 'pg_catalog' AND
n.nspname <> 'information_schema' ORDER BY 1, 2, 4;
 
=$ cat bad.sql | pg_prettify
SELECT
    n.nspname AS "Schema",
    p.proname AS "Name",
    pg_catalog.pg_get_function_result( p.oid ) AS "Result data type",
    pg_catalog.pg_get_function_arguments( p.oid ) AS "Argument data types",
    CASE p.prokind
        WHEN 'a' THEN 'agg'
        WHEN 'w' THEN 'window'
        WHEN 'p' THEN 'proc'
        ELSE 'func'
    END AS "Type"
FROM
    pg_catalog.pg_proc AS p
    LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = p.pronamespace
WHERE
    pg_catalog.pg_function_is_visible( p.oid ) AND
    n.nspname <> 'pg_catalog' AND
    n.nspname <> 'information_schema'
ORDER BY
    1,
    2,
    4;
-- Formatted by Pg::SQL::PrettyPrinter

There are even some options if you're interested:

=$ pg_prettify -h
Syntax:
    pg_prettify [-s] [-u URL] [-p PARAM_NAME] < input.file
or
    pg_prettify [-s] [-u URL] [-p PARAM_NAME] input.file
 
Options:
    -s - strip comment mentioning which tool was used to prettify
    -u - changes URL to use to prettify, default: https://paste.depesz.com/prettify
    -p - changes http parameter to use to send sql, default: q

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.