Window keyword support

[chanmix51]

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

[jdorn]

I'm not familiar with that keyword. Should "window" be formatted like "join"?

On Mon, Dec 8, 2014, 13:54 Grégoire HUBERT [email protected] wrote:

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 )

— Reply to this email directly or view it on GitHub https://github.com/jdorn/sql-formatter/issues/71.

[chanmix51]

"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.