File: README.md

package info (click to toggle)
extra-window-functions 1.0-2
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, sid
  • size: 476 kB
  • sloc: ansic: 281; sql: 205; makefile: 12; sh: 1
file content (52 lines) | stat: -rw-r--r-- 2,146 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# Extra Window Functions for PostgreSQL

[![License](https://img.shields.io/badge/license-PostgreSQL-blue)](https://www.postgresql.org/about/licence/)
[![Code of Conduct](https://img.shields.io/badge/code%20of%20conduct-PostgreSQL-blueviolet)](https://www.postgresql.org/about/policies/coc/)

[![Travis Build Status](https://api.travis-ci.com/xocolatl/extra_window_functions.svg?branch=master)](https://travis-ci.com/xocolatl/extra_window_functions)

*compatible 9.6–13*

This extension provides additional window functions to PostgreSQL.  Some of
them provide SQL Standard functionality but without the SQL Standard grammar,
others extend on the SQL Standard, and still others are novel and hopefully
useful to someone.

## Simualating Standard SQL

The window functions `LEAD()`, `LAG()`, `FIRST_VALUE()`, `LAST_VALUE()`, and
`NTH_VALUE()` can skip over null values.  PostgreSQL does not implement the
syntax required for that feature but this extension provides additional
functions that give you the same behavior.

In addition to this, `NTH_VALUE()` can count from the start or the end of the
window frame.

Despite these functions having long names, there isn't really any difference in
length compared to the excessively verbose SQL Standard syntax.

```
-- Standard SQL:
NTH_VALUE(x, 3) FROM LAST IGNORE NULLS OVER w

-- This extension:
nth_value_from_last_ignore_nulls(x, 3) OVER w
```

## Extending Standard SQL

The functions `LEAD()` and `LAG()` accept a default value for when the
requested row falls outside of the partition.  However, the functions
`FIRST_VALUE()`, `LAST_VALUE()`, and `NTH_VALUE()` do not have default values
for when the requested row is not in the frame.

## Non-Standard Functions

This extension introduces a new partition-level window function `flip_flop()`
and implements the
"[flip floperator](https://en.wikipedia.org/wiki/Flip-flop_(programming))".

In the first variant, the function returns false until the expression given as
an argument returns true.  It then keeps returning true until expression is
matched again.  The second variant takes two expressions: the first to flip,
the second to flop.