In the SQL snippet below, you see custom aggregates that are similar to first_value/last_value but which ignore NULL values, making them more useful in my opinion.
-- "s" stands for state, "v" stands for value, "sf" stands for state (transition) function
-- since these are defined as 'strict', "s" is the previous, while "v" is the next, non-null
-- value in the defined window.
create or replace function sf_first_value_ignore_nulls(s anyelement, v anyelement)
returns anyelement language sql immutable strict as $$ -- note the use of strict here
select s $$;
create or replace function sf_last_value_ignore_nulls(s anyelement, v anyelement)
returns anyelement language sql immutable strict as $$ -- note the use of strict here
select v $$;
create or replace aggregate first_value_ignore_nulls(anyelement) (
sfunc = sf_first_value_ignore_nulls,
msfunc = sf_first_value_ignore_nulls,
minvfunc = sf_last_value_ignore_nulls,
mstype = anyelement,
stype = anyelement
);
create or replace aggregate last_value_ignore_nulls(anyelement) (
sfunc = sf_last_value_ignore_nulls,
msfunc = sf_last_value_ignore_nulls,
minvfunc = sf_first_value_ignore_nulls,
mstype = anyelement,
stype = anyelement
);These aggregate functions are called, predictably enough, first_value_ignore_nulls and last_value_ignore_nulls.
In a previous naming attempt, I have tried to call two custom aggregate functions I created: previous() and upcoming(). This was a grave mistake, which I paid for with plenty of confusion later.
The reason the previous naming was a mistake is because the window definition is a crucial part of whether these functions may or may not grab such chronological entries.
select
bucket_refnr,
-- Get the first non-null value from following rows: upcoming value
first_value_ignore_nulls(bucket_refnr)
over following_rows as upcoming_bucket_refnr,
-- Get the last non-null value from preceding rows: previous value
last_value_ignore_nulls(bucket_first_refnr)
over preceding_rows as previous_bucket_refnr,
created
from fetch_cycle_buffer_prototype
window
following_rows as (order by created asc
rows between current row
and unbounded following exclude current row),
preceding_rows as (order by created asc
rows between unbounded preceding
and current row exclude current row)
order by created desc;| bucket_refnr | upcoming_bucket_refnr | previous_bucket_refnr | created (from most-recent to older) |
|---|---|---|---|
| "12828-864194702-S" | NULL | "10000-1202568525-S" | 2025-08-31 16:11:13.626707+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:12.336566+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:11.002826+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:09.701598+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:08.387731+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:07.064722+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:05.735659+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:04.413709+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:03.065035+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:01.750781+00 |
| NULL | "12828-864194702-S" | "10000-1202568525-S" | 2025-08-31 16:11:00.937005+00 |
| "10000-1202568525-S" | "12828-864194702-S" | "10001-1001408491-S" | 2025-08-29 08:16:42.210199+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:40.885751+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:39.568994+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:38.237202+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:36.839075+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:35.498574+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:34.154221+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:32.825715+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:31.505758+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:30.172848+00 |
| NULL | "10000-1202568525-S" | "10001-1001408491-S" | 2025-08-29 08:16:29.621112+00 |
| "10001-1001408491-S" | "10000-1202568525-S" | NULL | 2025-08-29 08:14:47.800227+00 |