Extra Window Functions for PostgreSQL

compatible 9.6–15

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.

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

Functions provided:

• lag_ignore_nulls(expression[, offset[, default]])
• lead_ignore_nulls(expression[, offset[, default]])
• first_value_ignore_nulls(expression)
• last_value_ignore_nulls(expression)
• nth_value_from_last(expression, offset)
• nth_value_ignore_nulls(expression, offset)
• nth_value_from_last_ignore_nulls(expression, offset)

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.

Functions provided:

• first_value_ignore_nulls(expression, default)
• last_value_ignore_nulls(expression, default)
• nth_value_from_last(expression, offset, default)
• nth_value_ignore_nulls(expression, offset, default)
• nth_value_from_last_ignore_nulls(expression, offset, default)

Non-Standard Functions

This extension introduces a new partition-level window function flip_flop() and implements the "flip floperator".

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

Functions provided:

• flip_flop(expression[, expression])