Window keyword support
Opened this issue · 2 comments
It seems sql-formatter does not recognize the window
SQL keyword (SQL2003, Postgres does support it). The following query
select
slug,
lag(slug) over published_at_wdw as next_slug,
lead(slug) over published_at_wdw as prev_slug
from
pomm.news
window published_at_wdw as (order by published_at desc)
is output as
select
slug,
lag(slug) over published_at_wdw as next_slug,
lead(slug) over published_at_wdw as prev_slug
from
pomm.news window published_at_wdw as (
order by
published_at desc
)
sql-formatter 1.2.17
I'm not familiar with that keyword. Should "window" be formatted like
"join"?
On Mon, Dec 8, 2014, 13:54 Grégoire HUBERT notifications@github.com wrote:
It seems sql-formatter does not recognize the window SQL keyword
(SQL2003, Postgres does support it). The following queryselect
slug,
lag(slug) over published_at_wdw as next_slug,
lead(slug) over published_at_wdw as prev_slug
from
pomm.news
window published_at_wdw as (order by published_at desc)is output as
select
slug,
lag(slug) over published_at_wdw as next_slug,
lead(slug) over published_at_wdw as prev_slug
from
pomm.news window published_at_wdw as (
order by
published_at desc
)—
Reply to this email directly or view it on GitHub
#71.
"window" is a declaration the same level as "from". It declares a data window some functions in the select use to give related data between each other (aka window functions, see Postgres documentation)
Syntax can be:
select
wdw_function(field) over wdw_name as column,
…
from table1
join table2 …
other joins
window wdw_name as (partition by field1 order by field2 desc)
…
or inline:
select
wdw_function(field) over (partition by field1 order by field2) as column,
…
A comprehensive guide of SQL window syntax can be found in Postgres documentation.