Input:
```
-- TITLE: Name of query
-- ABOUT: get a count for each user over the past 2 days
-- TODO: this is a long comment and it needs to go over 80 characters just to test pg_format
SELECT pt.column1, -- something we can order by such as a date
pt.column2, -- something we can group by such as an ID
pt.column3, -- something we can count such as a dollar amount
sum(pt.column3) over (partition by pt.column2 order by pt.column1
rows between unbounded preceding and current row) as total_count_per_user
from
schema.practice_table as pt
where 1 = 1
AND pt.column1 > GETDATE() - INTERVAL '2 days'
order by
total_count_per_user desc
limit 100
```
Output:
```
-- TITLE: Name of query
-- ABOUT: get a count for each user over the past 2 days
-- TODO: this is a long comment and it needs to go over 80 characters just to
-- test pg_format
SELECT
pt.column1, -- something we can order by such as a date
pt.column2, -- something we can group by such as an ID
pt.column3, -- something we can count such as a dollar amount
SUM(pt.column3) OVER (PARTITION BY pt.column2 ORDER BY pt.column1 ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count_per_user
FROM
schema.practice_table AS pt
WHERE
1 = 1
AND pt.column1 > GETDATE() - INTERVAL '2 days'
ORDER BY
total_count_per_user DESC
LIMIT 100
```
Desired Output:
```
-- TITLE: Name of query
-- ABOUT: get a count for each user over the past 2 days
-- TODO: this is a long comment and it needs to go over 80 characters just to
-- test pg_format
SELECT
pt.column1, -- something we can order by such as a date
pt.column2, -- something we can group by such as an ID
pt.column3, -- something we can count such as a dollar amount
SUM(pt.column3) OVER (PARTITION BY pt.column2 ORDER BY pt.column1 ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count_per_user
FROM schema.practice_table AS pt
WHERE 1 = 1
AND pt.column1 > GETDATE() - INTERVAL '2 days'
ORDER BY total_count_per_user DESC
LIMIT 100
```
There are only 4 differences between Output and Desired Output
I would like an empty line to remain above the main query if there is a comment(not needed)
I would like the line with the from keyword not to have a newline until the next keyword or it's over my wrap-limit
I would like 1 = 1 to be on the same line as the WHERE keyword
I would like everything in the order by clause to be on the same line unless it hits my wrap-limit.
Here's my pg_format config file:
```
####
# Copy this file as ~/.pg_format and adjust the default settings
# if you want to control the default behavior of pgFormatter.
####
# Obscure all literals in queries, use to hide confidential data before formatting.
anonymize=0
# In a parameters list, end or start with the comma. Default: end
comma=end
# In insert statement, add a newline after each comma.
comma-break=1
# Output format: text or html. Default: text.
format=text
# Add a newline between statements in transaction regroupement. Default is to group statements.
nogrouping=0
# Change the case of the reserved keyword. Default is uppercase: 2.
# Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize.
keyword-case=2
# Change the case of the data type name. Default is lowercase: 1.
# Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize.
type-case=2
# Change the case of the reserved keyword. Default is unchanged: 0.
# Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize.
function-case=2
# Do not add an extra empty line at end of the output.
no-extra-line=1
# Maximum length of a query, it will be cutted above the given size. Default: no truncate.
maxlength=0
# Remove any comment from SQL code.
nocomment=0
# Statement numbering as a comment before each query.
numbering=0
# Define the filename for the output. Default: stdout.
output=
# Set regex to find code that must not be changed.
placeholder=
# Use multi-line search for placeholder regex.
multiline=0
# Add RedShift keyworks to the list of SQL keyworks.
# (obsolete use extra-keyword=redshift instead)
redshift=0
# Dynamic code separator, default to single quote.
separator=
# Change space indent, default 4 spaces.
spaces=4
# Try another formatting type for some statements.
format-type=0
# Use tabs instead of space characters, when used spaces is set to 1 whatever is its value.
tabs=0
# Wrap queries at a certain length.
wrap-limit=80
# Number of column after which lists must be wrapped.
wrap-after=0
# with --wrap-limit, apply reformatting to comments.
wrap-comment=1
# Add a list of function to be formatted as PG internal functions.
#extra-function=/opt/pgFormatter/functions.lst
# Add a list of keywords to be formatted as PG internal keywords.
# Special value redshift mean use internal defined redshift keywords.
extra-keyword=redshift
# Remove the space character between a function call and the open parenthesis that follow.
no-space-function=1
```
Is this possible with pg_formatter?
Input:
``` -- TITLE: Name of query -- ABOUT: get a count for each user over the past 2 days -- TODO: this is a long comment and it needs to go over 80 characters just to test pg_format SELECT pt.column1, -- something we can order by such as a date pt.column2, -- something we can group by such as an ID pt.column3, -- something we can count such as a dollar amount sum(pt.column3) over (partition by pt.column2 order by pt.column1 rows between unbounded preceding and current row) as total_count_per_user from schema.practice_table as pt where 1 = 1 AND pt.column1 > GETDATE() - INTERVAL '2 days' order by total_count_per_user desc limit 100 ```Output:
``` -- TITLE: Name of query -- ABOUT: get a count for each user over the past 2 days -- TODO: this is a long comment and it needs to go over 80 characters just to -- test pg_format SELECT pt.column1, -- something we can order by such as a date pt.column2, -- something we can group by such as an ID pt.column3, -- something we can count such as a dollar amount SUM(pt.column3) OVER (PARTITION BY pt.column2 ORDER BY pt.column1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count_per_user FROM schema.practice_table AS pt WHERE 1 = 1 AND pt.column1 > GETDATE() - INTERVAL '2 days' ORDER BY total_count_per_user DESC LIMIT 100 ```Desired Output:
``` -- TITLE: Name of query -- ABOUT: get a count for each user over the past 2 days -- TODO: this is a long comment and it needs to go over 80 characters just to -- test pg_format SELECT pt.column1, -- something we can order by such as a date pt.column2, -- something we can group by such as an ID pt.column3, -- something we can count such as a dollar amount SUM(pt.column3) OVER (PARTITION BY pt.column2 ORDER BY pt.column1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_count_per_user FROM schema.practice_table AS pt WHERE 1 = 1 AND pt.column1 > GETDATE() - INTERVAL '2 days' ORDER BY total_count_per_user DESC LIMIT 100 ```There are only 4 differences between
Output
andDesired Output
Here's my pg_format config file:
``` #### # Copy this file as ~/.pg_format and adjust the default settings # if you want to control the default behavior of pgFormatter. #### # Obscure all literals in queries, use to hide confidential data before formatting. anonymize=0 # In a parameters list, end or start with the comma. Default: end comma=end # In insert statement, add a newline after each comma. comma-break=1 # Output format: text or html. Default: text. format=text # Add a newline between statements in transaction regroupement. Default is to group statements. nogrouping=0 # Change the case of the reserved keyword. Default is uppercase: 2. # Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize. keyword-case=2 # Change the case of the data type name. Default is lowercase: 1. # Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize. type-case=2 # Change the case of the reserved keyword. Default is unchanged: 0. # Values: 0=>unchanged, 1=>lowercase, 2=>uppercase, 3=>capitalize. function-case=2 # Do not add an extra empty line at end of the output. no-extra-line=1 # Maximum length of a query, it will be cutted above the given size. Default: no truncate. maxlength=0 # Remove any comment from SQL code. nocomment=0 # Statement numbering as a comment before each query. numbering=0 # Define the filename for the output. Default: stdout. output= # Set regex to find code that must not be changed. placeholder= # Use multi-line search for placeholder regex. multiline=0 # Add RedShift keyworks to the list of SQL keyworks. # (obsolete use extra-keyword=redshift instead) redshift=0 # Dynamic code separator, default to single quote. separator= # Change space indent, default 4 spaces. spaces=4 # Try another formatting type for some statements. format-type=0 # Use tabs instead of space characters, when used spaces is set to 1 whatever is its value. tabs=0 # Wrap queries at a certain length. wrap-limit=80 # Number of column after which lists must be wrapped. wrap-after=0 # with --wrap-limit, apply reformatting to comments. wrap-comment=1 # Add a list of function to be formatted as PG internal functions. #extra-function=/opt/pgFormatter/functions.lst # Add a list of keywords to be formatted as PG internal keywords. # Special value redshift mean use internal defined redshift keywords. extra-keyword=redshift # Remove the space character between a function call and the open parenthesis that follow. no-space-function=1 ```