From b131e074b0399b1859712d5fda8e62754c3eddbd Mon Sep 17 00:00:00 2001 From: Vik Fearing Date: Thu, 4 Jun 2020 08:23:42 +0200 Subject: [PATCH 2/2] new window functions to aid testing --- src/backend/utils/adt/windowfuncs.c | 88 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 12 ++++ src/test/regress/expected/window.out | 49 ++++++++++++++++ src/test/regress/sql/window.sql | 23 ++++++++ 4 files changed, 172 insertions(+) diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index f0c8ae686d..616a8d84a1 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -472,3 +472,91 @@ window_nth_value(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } + +Datum +window_nth_value_last(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool const_offset; + Datum result; + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1); + + if (nth <= 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE), + errmsg("argument of nth_value_last must be greater than zero"))); + + result = WinGetFuncArgInFrame(winobj, 0, + -(nth - 1), WINDOW_SEEK_TAIL, const_offset, + &isnull, NULL); + if (isnull) + PG_RETURN_NULL(); + + PG_RETURN_DATUM(result); +} + +Datum +window_abs_nth(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool const_offset; + Datum result; + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1); + + if (nth <= 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE), + errmsg("argument of abs_nth must be greater than zero"))); + + result = WinGetFuncArgInPartition(winobj, 0, + nth - 1, + WINDOW_SEEK_HEAD, + const_offset, + &isnull, NULL); + if (isnull) + PG_RETURN_NULL(); + + PG_RETURN_DATUM(result); +} + +Datum +window_abs_nth_last(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool const_offset; + Datum result; + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1); + + if (nth <= 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE), + errmsg("argument of abs_nth_last must be greater than zero"))); + + result = WinGetFuncArgInPartition(winobj, 0, + -(nth - 1), + WINDOW_SEEK_TAIL, + const_offset, + &isnull, NULL); + if (isnull) + PG_RETURN_NULL(); + + PG_RETURN_DATUM(result); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index ed0e44c1f2..94d78d3abc 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -9720,6 +9720,18 @@ proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4', prosrc => 'window_nth_value', pronulltreatment => 't' }, +{ oid => '8114', descr => 'fetch the Nth row value', + proname => 'nth_value_last', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4', prosrc => 'window_nth_value_last', + pronulltreatment => 't' }, +{ oid => '8115', descr => 'fetch the Nth row value', + proname => 'abs_nth', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4', prosrc => 'window_abs_nth', + pronulltreatment => 't' }, +{ oid => '8116', descr => 'fetch the Nth row value', + proname => 'abs_nth_last', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4', prosrc => 'window_abs_nth_last', + pronulltreatment => 't' }, # functions for range types { oid => '3832', descr => 'I/O', diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 29d0247a7a..f91cb21893 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -3998,6 +3998,55 @@ WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLO xyzzy | | 4332 | 88 | 224 | (10 rows) +-- nth_value_last +SELECT name, + orbit, + nth_value_last(orbit, 2) RESPECT NULLS OVER w1, + nth_value_last(orbit, 2) IGNORE NULLS OVER w1, + nth_value_last(orbit, 2) RESPECT NULLS OVER w2, + nth_value_last(orbit, 2) IGNORE NULLS OVER w2 +FROM planets +WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) +; + name | orbit | nth_value_last | nth_value_last | nth_value_last | nth_value_last +---------+-------+----------------+----------------+----------------+---------------- + earth | | 224 | 24491 | 4332 | + jupiter | 4332 | 224 | 24491 | | 4332 + mars | | 224 | 24491 | 88 | 88 + mercury | 88 | 224 | 24491 | 60182 | 60182 + neptune | 60182 | 224 | 24491 | 90560 | 90560 + pluto | 90560 | 224 | 24491 | 24491 | 90560 + saturn | 24491 | 224 | 24491 | | 24491 + uranus | | 224 | 24491 | 224 | 24491 + venus | 224 | 224 | 24491 | 224 | 24491 + xyzzy | | 224 | 24491 | 224 | +(10 rows) + +-- abs_nth, abs_nth_last +SELECT name, + orbit, + abs_nth(orbit, 3) RESPECT NULLS OVER w, + abs_nth(orbit, 3) IGNORE NULLS OVER w, + abs_nth_last(orbit, 3) RESPECT NULLS OVER w, + abs_nth_last(orbit, 3) IGNORE NULLS OVER w +FROM planets +WINDOW w AS (ORDER BY name) +; + name | orbit | abs_nth | abs_nth | abs_nth_last | abs_nth_last +---------+-------+---------+---------+--------------+-------------- + earth | | | 60182 | | 90560 + jupiter | 4332 | | 60182 | | 90560 + mars | | | 60182 | | 90560 + mercury | 88 | | 60182 | | 90560 + neptune | 60182 | | 60182 | | 90560 + pluto | 90560 | | 60182 | | 90560 + saturn | 24491 | | 60182 | | 90560 + uranus | | | 60182 | | 90560 + venus | 224 | | 60182 | | 90560 + xyzzy | | | 60182 | | 90560 +(10 rows) + -- last_value SELECT name, orbit, diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 35a7081a45..2b5048784c 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1353,6 +1353,29 @@ WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLO w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) ; +-- nth_value_last +SELECT name, + orbit, + nth_value_last(orbit, 2) RESPECT NULLS OVER w1, + nth_value_last(orbit, 2) IGNORE NULLS OVER w1, + nth_value_last(orbit, 2) RESPECT NULLS OVER w2, + nth_value_last(orbit, 2) IGNORE NULLS OVER w2 +FROM planets +WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) +; + +-- abs_nth, abs_nth_last +SELECT name, + orbit, + abs_nth(orbit, 3) RESPECT NULLS OVER w, + abs_nth(orbit, 3) IGNORE NULLS OVER w, + abs_nth_last(orbit, 3) RESPECT NULLS OVER w, + abs_nth_last(orbit, 3) IGNORE NULLS OVER w +FROM planets +WINDOW w AS (ORDER BY name) +; + -- last_value SELECT name, orbit, -- 2.17.1