diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index edc9be92a6..cf44aeddcf 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14762,7 +14762,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lag(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -14791,7 +14791,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lead(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -14817,7 +14817,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; first_value - first_value(value any) + first_value(value any) [null_treatment] same type as value @@ -14833,7 +14833,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; last_value - last_value(value any) + last_value(value any) [null_treatment] same type as value @@ -14850,7 +14850,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; nth_value - nth_value(value any, nth integer) + nth_value(value any, nth integer) [from_first_last] [null_treatment] @@ -14865,6 +14865,23 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + + In , null_treatment is one of: + + RESPECT NULLS + IGNORE NULLS + + + and from_first_last is one of: + + FROM FIRST + FROM LAST + + RESPECT NULLS specifies the default behavior to include nulls in the result. + IGNORE NULLS ignores any null values when determining a result. + FROM FIRST specifies the default ordering, and FROM LAST reverses the ordering. + All of the functions listed in @@ -14901,22 +14918,6 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; Other frame specifications can be used to obtain other effects. - - - The SQL standard defines a RESPECT NULLS or - IGNORE NULLS option for lead, lag, - first_value, last_value, and - nth_value. This is not implemented in - PostgreSQL: the behavior is always the - same as the standard's default, namely RESPECT NULLS. - Likewise, the standard's FROM FIRST or FROM LAST - option for nth_value is not implemented: only the - default FROM FIRST behavior is supported. (You can achieve - the result of FROM LAST by reversing the ORDER BY - ordering.) - - - cume_dist computes the fraction of partition rows that are less than or equal to the current row and its peers, while diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index aeb262a5b0..30257157b8 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -494,9 +494,9 @@ T612 Advanced OLAP operations NO some forms supported T613 Sampling YES T614 NTILE function YES T615 LEAD and LAG functions YES -T616 Null treatment option for LEAD and LAG functions NO +T616 Null treatment option for LEAD and LAG functions YES T617 FIRST_VALUE and LAST_VALUE function YES -T618 NTH_VALUE function NO function exists, but some options missing +T618 NTH_VALUE function YES T619 Nested window functions NO T620 WINDOW clause: GROUPS option YES T621 Enhanced numeric functions YES diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 90dfac2cb1..44e8de3c12 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -153,6 +153,7 @@ static Node *makeBitStringConst(char *str, int location); static Node *makeNullAConst(int location); static Node *makeAConst(Value *v, int location); static Node *makeBoolAConst(bool state, int location); +static Node *makeTypedBoolAConst(bool state, char *type, int location); static RoleSpec *makeRoleSpec(RoleSpecType type, int location); static void check_qualified_name(List *names, core_yyscan_t yyscanner); static List *check_func_name(List *names, core_yyscan_t yyscanner); @@ -561,7 +562,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type xml_namespace_list %type xml_namespace_el -%type func_application func_expr_common_subexpr +%type func_application func_expr_common_subexpr func_expr_first_last %type func_expr func_expr_windowless %type common_table_expr %type with_clause opt_with_clause @@ -569,6 +570,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type within_group_clause %type filter_clause +%type from_first_last_null_treatment_clause null_treatment_clause %type window_clause window_definition_list opt_partition_clause %type window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound @@ -632,14 +634,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTENSION EXTERNAL EXTRACT - FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR + FALSE_P FAMILY FETCH FILTER FIRST_P FIRST_VALUE FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING GROUPS HANDLER HAVING HEADER_P HOLD HOUR_P - IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE + IDENTITY_P IF_P IGNORE_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION @@ -648,14 +650,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); KEY - LABEL LANGUAGE LARGE_P LAST_P LATERAL_P - LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL + LABEL LAG LANGUAGE LARGE_P LAST_P LAST_VALUE LATERAL_P + LEAD LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NONE - NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF + NOT NOTHING NOTIFY NOTNULL NOWAIT NTH_VALUE NULL_P NULLIF NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR @@ -670,7 +672,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP + RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -714,6 +716,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); /* Precedence: lowest to highest */ %nonassoc SET /* see relation_expr_opt_alias */ +%nonassoc FIRST_VALUE LAG LAST_VALUE LEAD NTH_VALUE %left UNION EXCEPT %left INTERSECT %left OR @@ -763,6 +766,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %right UMINUS %left '[' ']' %left '(' ')' +%left FROM FIRST_P LAST_P NULLS_P %left TYPECAST %left '.' /* @@ -13632,6 +13636,33 @@ func_application: func_name '(' ')' } ; +null_treatment_clause: + RESPECT NULLS_P { $$ = 0; } + | IGNORE_P NULLS_P { $$ = WINFUNC_OPT_IGNORE_NULLS; } + ; + +from_first_last_null_treatment_clause: + FROM FIRST_P null_treatment_clause + { + $$ = $3; + } + | FROM LAST_P null_treatment_clause + { + $$ = WINFUNC_OPT_FROM_LAST | $3; + } + | null_treatment_clause + { + $$ = $1; + } + | FROM FIRST_P + { + $$ = 0; + } + | FROM LAST_P + { + $$ = WINFUNC_OPT_FROM_LAST; + } + ; /* * func_expr and its cousin func_expr_windowless are split out from c_expr just @@ -13679,6 +13710,133 @@ func_expr: func_application within_group_clause filter_clause over_clause } | func_expr_common_subexpr { $$ = $1; } + | func_expr_first_last over_clause + { + FuncCall *n = (FuncCall *) $1; + n->over = $2; + $$ = (Node *) n; + } + ; + +func_expr_first_last: + FIRST_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause + { + FuncCall *n; + List *l = $3; + int winFuncArgs = $6; + + /* Convert Ignore Nulls option to bool */ + if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS) + l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2)); + + n = makeFuncCall(list_make1(makeString("first_value")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | + FIRST_VALUE '(' func_arg_list opt_sort_clause ')' + { + FuncCall *n; + List *l = $3; + + n = makeFuncCall(list_make1(makeString("first_value")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | LAG '(' func_arg_list opt_sort_clause ')' null_treatment_clause + { + FuncCall *n; + List *l = $3; + int winFuncArgs = $6; + + /* Convert Ignore Nulls option to bool */ + if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS) + l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2)); + + n = makeFuncCall(list_make1(makeString("lag")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | LAG '(' func_arg_list opt_sort_clause ')' + { + FuncCall *n; + List *l = $3; + + n = makeFuncCall(list_make1(makeString("lag")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | LAST_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause + { + FuncCall *n; + List *l = $3; + int winFuncArgs = $6; + + /* Convert Ignore Nulls option to bool */ + if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS) + l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2)); + + n = makeFuncCall(list_make1(makeString("last_value")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | LAST_VALUE '(' func_arg_list opt_sort_clause ')' + { + FuncCall *n; + List *l = $3; + + n = makeFuncCall(list_make1(makeString("last_value")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | LEAD '(' func_arg_list opt_sort_clause ')' null_treatment_clause + { + FuncCall *n; + List *l = $3; + int winFuncArgs = $6; + + /* Convert Ignore Nulls option to bool */ + if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS) + l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2)); + + n = makeFuncCall(list_make1(makeString("lead")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | LEAD '(' func_arg_list opt_sort_clause ')' + { + FuncCall *n; + List *l = $3; + + n = makeFuncCall(list_make1(makeString("lead")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | NTH_VALUE '(' func_arg_list opt_sort_clause ')' from_first_last_null_treatment_clause + { + FuncCall *n; + List *l = $3; + int winFuncArgs = $6; + + /* Convert Nulls and From First options to bools */ + if (winFuncArgs & WINFUNC_OPT_FROM_LAST) + l = lappend(l, makeTypedBoolAConst(true, "fromlast", @2)); + if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS) + l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2)); + + n = makeFuncCall(list_make1(makeString("nth_value")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } + | NTH_VALUE '(' func_arg_list opt_sort_clause ')' + { + FuncCall *n; + List *l = $3; + + n = makeFuncCall(list_make1(makeString("nth_value")), l, @1); + n->agg_order = $4; + $$ = (Node *) n; + } ; /* @@ -15082,6 +15240,7 @@ unreserved_keyword: | FAMILY | FILTER | FIRST_P + | FIRST_VALUE | FOLLOWING | FORCE | FORWARD @@ -15097,6 +15256,7 @@ unreserved_keyword: | HOUR_P | IDENTITY_P | IF_P + | IGNORE_P | IMMEDIATE | IMMUTABLE | IMPLICIT_P @@ -15117,9 +15277,12 @@ unreserved_keyword: | ISOLATION | KEY | LABEL + | LAG | LANGUAGE | LARGE_P | LAST_P + | LAST_VALUE + | LEAD | LEAKPROOF | LEVEL | LISTEN @@ -15147,6 +15310,7 @@ unreserved_keyword: | NOTHING | NOTIFY | NOWAIT + | NTH_VALUE | NULLS_P | OBJECT_P | OF @@ -15198,6 +15362,7 @@ unreserved_keyword: | REPLACE | REPLICA | RESET + | RESPECT | RESTART | RESTRICT | RETURNS @@ -15670,13 +15835,22 @@ makeAConst(Value *v, int location) static Node * makeBoolAConst(bool state, int location) { + return makeTypedBoolAConst(state, "bool", location); +} + +/* makeTypedBoolAConst() + * Create an A_Const string node from a boolean and store inside the specified type. + */ +static Node * +makeTypedBoolAConst(bool state, char *type, int location) +{ A_Const *n = makeNode(A_Const); n->val.type = T_String; n->val.val.str = (state ? "t" : "f"); n->location = location; - return makeTypeCast((Node *)n, SystemTypeName("bool"), -1); + return makeTypeCast((Node *)n, SystemTypeName(type), -1); } /* makeRoleSpec diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 065238b0fe..12e00ce2eb 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -9226,6 +9226,8 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context) int nargs; List *argnames; ListCell *l; + bool ignorenulls = false, + fromlast = false; if (list_length(wfunc->args) > FUNC_MAX_ARGS) ereport(ERROR, @@ -9252,15 +9254,47 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context) if (wfunc->winstar) appendStringInfoChar(buf, '*'); else - get_rule_expr((Node *) wfunc->args, context, true); + { + ListCell *arglist; + Node *argnode = (Node *) wfunc->args; + + get_rule_expr(argnode, context, true); + + /* Determine if FROM LAST and/or IGNORE NULLS should be appended */ + foreach(arglist, (List *) argnode) + { + Node *arg = (Node *) lfirst(arglist); + if (nodeTag(arg) == T_Const) + { + Const *constnode = (Const *) arg; + if (constnode->consttype == FROMLASTOID) + { + /* parser does not save FROM FIRST arguments */ + fromlast = true; + buf->len -= 2; + } + if (constnode->consttype == IGNORENULLSOID) + { + /* parser does not save RESPECT NULLS arguments */ + ignorenulls = true; + buf->len -= 2; + } + } + } + } + + appendStringInfoChar(buf, ')'); + if (fromlast) + appendStringInfoString(buf, " FROM LAST"); + if (ignorenulls) + appendStringInfoString(buf, " IGNORE NULLS"); if (wfunc->aggfilter != NULL) { appendStringInfoString(buf, ") FILTER (WHERE "); get_rule_expr((Node *) wfunc->aggfilter, context, false); } - - appendStringInfoString(buf, ") OVER "); + appendStringInfoString(buf, " OVER "); foreach(l, context->windowClause) { @@ -9435,6 +9469,11 @@ get_const_expr(Const *constval, deparse_context *context, int showtype) appendStringInfoString(buf, "false"); break; + case FROMLASTOID: + case IGNORENULLSOID: + showtype = -1; + break; + default: simple_quote_literal(buf, extval); break; diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index 40ba783572..094590334d 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -38,7 +38,14 @@ typedef struct static bool rank_up(WindowObject winobj); static Datum leadlag_common(FunctionCallInfo fcinfo, bool forward, bool withoffset, bool withdefault); - +static Datum leadlag_common_ignore_nulls(FunctionCallInfo fcinfo, + bool forward, bool withoffset, bool withdefault); +static Datum +window_nth_value_ignorenulls_common(FunctionCallInfo fcinfo, int32 nth, + bool fromlast); +static Datum +window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth, + bool fromlast); /* * utility routine for *_rank functions. @@ -328,6 +335,79 @@ leadlag_common(FunctionCallInfo fcinfo, PG_RETURN_DATUM(result); } +static Datum +leadlag_common_ignore_nulls(FunctionCallInfo fcinfo, + bool forward, bool withoffset, bool withdefault) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + int32 offset; + Datum result; + bool isnull; + bool isout = false; + int32 notnull_offset = 0, tmp_offset = 0; + + if (withoffset) + { + offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + if (offset < 0) + { + offset = abs(offset); + forward = !forward; + } else if (offset == 0) + { + result = WinGetFuncArgInPartition(winobj, 0, 0, + WINDOW_SEEK_CURRENT, + false, + &isnull, &isout); + if (isnull || isout) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(result); + } + } + else + offset = 1; + + while (notnull_offset < offset) + { + tmp_offset++; + result = WinGetFuncArgInPartition(winobj, 0, + (forward ? tmp_offset : -tmp_offset), + WINDOW_SEEK_CURRENT, + false, + &isnull, &isout); + if (isout) + goto out_of_frame; + else if (!isnull) + notnull_offset++; + } + + result = WinGetFuncArgInPartition(winobj, 0, + (forward ? tmp_offset : -tmp_offset), + WINDOW_SEEK_CURRENT, + false, + &isnull, &isout); + if (isout) + goto out_of_frame; + else + PG_RETURN_DATUM(result); + + out_of_frame: + /* + * target row is out of the partition; supply default value if + * provided. Otherwise return NULL. + */ + if (withdefault) + { + result = WinGetFuncArgCurrent(winobj, 2, &isnull); + PG_RETURN_DATUM(result); + } + else + PG_RETURN_NULL(); +} + /* * lag * returns the value of VE evaluated on a row that is 1 @@ -363,6 +443,24 @@ window_lag_with_offset_and_default(PG_FUNCTION_ARGS) return leadlag_common(fcinfo, false, true, true); } +Datum +window_lag_nulls_opt(PG_FUNCTION_ARGS) +{ + return leadlag_common_ignore_nulls(fcinfo, false, false, false); +} + +Datum +window_lag_with_offset_nulls_opt(PG_FUNCTION_ARGS) +{ + return leadlag_common_ignore_nulls(fcinfo, false, true, false); +} + +Datum +window_lag_with_offset_and_default_nulls_opt(PG_FUNCTION_ARGS) +{ + return leadlag_common_ignore_nulls(fcinfo, false, true, true); +} + /* * lead * returns the value of VE evaluated on a row that is 1 @@ -398,6 +496,24 @@ window_lead_with_offset_and_default(PG_FUNCTION_ARGS) return leadlag_common(fcinfo, true, true, true); } +Datum +window_lead_nulls_opt(PG_FUNCTION_ARGS) +{ + return leadlag_common_ignore_nulls(fcinfo, true, false, false); +} + +Datum +window_lead_with_offset_nulls_opt(PG_FUNCTION_ARGS) +{ + return leadlag_common_ignore_nulls(fcinfo, true, true, false); +} + +Datum +window_lead_with_offset_and_default_nulls_opt(PG_FUNCTION_ARGS) +{ + return leadlag_common_ignore_nulls(fcinfo, true, true, true); +} + /* * first_value * return the value of VE evaluated on the first row of the @@ -419,6 +535,31 @@ window_first_value(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } +Datum +window_first_value_nulls_opt(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + Datum result; + bool isnull, + isout; + int64 pos; + + isout = false; + pos = 0; + + while (!isout) + { + result = WinGetFuncArgInFrame(winobj, 0, + pos, WINDOW_SEEK_HEAD, false, + &isnull, &isout); + if (!isnull) + PG_RETURN_DATUM(result); + pos++; + } + + PG_RETURN_NULL(); +} + /* * last_value * return the value of VE evaluated on the last row of the @@ -440,35 +581,156 @@ window_last_value(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } +Datum +window_last_value_nulls_opt(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + Datum result; + bool isnull, + isout; + int64 pos; + + isout = false; + pos = 0; + + while (!isout) + { + result = WinGetFuncArgInFrame(winobj, 0, + pos, WINDOW_SEEK_TAIL, false, + &isnull, &isout); + if (!isnull) + PG_RETURN_DATUM(result); + pos--; + } + + PG_RETURN_NULL(); +} + /* * nth_value * return the value of VE evaluated on the n-th row from the first * row of the window frame, per spec. */ -Datum -window_nth_value(PG_FUNCTION_ARGS) +static Datum +window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth, + bool fromlast) { WindowObject winobj = PG_WINDOW_OBJECT(); bool const_offset; Datum result; bool isnull; - int32 nth; + 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 must be greater than zero"))); + + result = WinGetFuncArgInFrame(winobj, + 0, + nth - 1, + fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD, const_offset, + &isnull, + NULL); + if (isnull) + PG_RETURN_NULL(); + + PG_RETURN_DATUM(result); +} + +static Datum +window_nth_value_ignorenulls_common(FunctionCallInfo fcinfo, int32 nth, + bool fromlast) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + Datum result; + bool isnull, + isout; + int32 tmp_offset, notnull_offset = 0; nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (fromlast) + tmp_offset = 1; + else + tmp_offset = -1; + 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 must be greater than zero"))); + while (notnull_offset < nth) + { + fromlast ? tmp_offset-- : tmp_offset++; + result = WinGetFuncArgInFrame(winobj, 0, + tmp_offset, fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD, + false, &isnull, &isout); + if (isout) + PG_RETURN_NULL(); + if (!isnull) + notnull_offset++; + } + result = WinGetFuncArgInFrame(winobj, 0, - nth - 1, WINDOW_SEEK_HEAD, const_offset, - &isnull, NULL); - if (isnull) + tmp_offset, fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD, + false, &isnull, &isout); + if (isout || isnull) PG_RETURN_NULL(); PG_RETURN_DATUM(result); } + +Datum +window_nth_value(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + PG_RETURN_DATUM(window_nth_value_respectnulls_common(fcinfo, nth, false)); +} + +Datum +window_nth_value_with_first_opt(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + PG_RETURN_DATUM(window_nth_value_respectnulls_common(fcinfo, nth, true)); +} + +Datum +window_nth_value_with_nulls_opt(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + PG_RETURN_DATUM(window_nth_value_ignorenulls_common(fcinfo, nth, false)); +} + +Datum +window_nth_value_with_first_nulls_opts(PG_FUNCTION_ARGS) +{ + WindowObject winobj = PG_WINDOW_OBJECT(); + bool isnull; + int32 nth; + + nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); + if (isnull) + PG_RETURN_NULL(); + PG_RETURN_DATUM(window_nth_value_ignorenulls_common(fcinfo, nth, true)); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a14651010f..2fdb509a69 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -9432,32 +9432,72 @@ { oid => '3106', descr => 'fetch the preceding row value', proname => 'lag', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement', prosrc => 'window_lag' }, +{ oid => '4144', descr => 'fetch the preceding row value with nulls option', + proname => 'lag', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement ignorenulls', prosrc => 'window_lag_nulls_opt' }, { oid => '3107', descr => 'fetch the Nth preceding row value', proname => 'lag', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset' }, +{ oid => '4145', descr => 'fetch the Nth preceding row value with nulls option', + proname => 'lag', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 ignorenulls', + prosrc => 'window_lag_with_offset_nulls_opt' }, { oid => '3108', descr => 'fetch the Nth preceding row value with default', proname => 'lag', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4 anyelement', prosrc => 'window_lag_with_offset_and_default' }, +{ oid => '4146', descr => 'fetch the Nth preceding row value with default and nulls option', + proname => 'lag', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 anyelement ignorenulls', + prosrc => 'window_lag_with_offset_and_default_nulls_opt' }, { oid => '3109', descr => 'fetch the following row value', proname => 'lead', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement', prosrc => 'window_lead' }, +{ oid => '4147', descr => 'fetch the following row value with nulls option', + proname => 'lead', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement ignorenulls', prosrc => 'window_lead_nulls_opt' }, { oid => '3110', descr => 'fetch the Nth following row value', proname => 'lead', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset' }, +{ oid => '4148', descr => 'fetch the Nth following row value with nulls option', + proname => 'lead', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 ignorenulls', + prosrc => 'window_lead_with_offset_nulls_opt' }, { oid => '3111', descr => 'fetch the Nth following row value with default', proname => 'lead', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4 anyelement', prosrc => 'window_lead_with_offset_and_default' }, +{ oid => '4149', descr => 'fetch the Nth following row value with default and nulls option', + proname => 'lead', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 anyelement ignorenulls', + prosrc => 'window_lead_with_offset_and_default_nulls_opt' }, { oid => '3112', descr => 'fetch the first row value', proname => 'first_value', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement', prosrc => 'window_first_value' }, +{ oid => '4150', descr => 'fetch the first row value with nulls option', + proname => 'first_value', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement ignorenulls', prosrc => 'window_first_value_nulls_opt' }, { oid => '3113', descr => 'fetch the last row value', proname => 'last_value', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement', prosrc => 'window_last_value' }, +{ oid => '4151', descr => 'fetch the last row value with nulls option', + proname => 'last_value', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement ignorenulls', prosrc => 'window_last_value_nulls_opt' }, { oid => '3114', descr => 'fetch the Nth row value', proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4', prosrc => 'window_nth_value' }, +{ oid => '4152', descr => 'fetch the Nth row value with from first option', + proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 fromlast', + prosrc => 'window_nth_value_with_first_opt' }, +{ oid => '4153', descr => 'fetch the Nth row value with nulls option', + proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 ignorenulls', + prosrc => 'window_nth_value_with_nulls_opt' }, +{ oid => '4154', descr => 'fetch the Nth row value with from first and nulls option', + proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', + proargtypes => 'anyelement int4 fromlast ignorenulls', + prosrc => 'window_nth_value_with_first_nulls_opts' }, # functions for range types { oid => '3832', descr => 'I/O', diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat index 48e01cd694..f9f6933b18 100644 --- a/src/include/catalog/pg_type.dat +++ b/src/include/catalog/pg_type.dat @@ -938,5 +938,15 @@ typname => 'anyrange', typlen => '-1', typbyval => 'f', typtype => 'p', typcategory => 'P', typinput => 'anyrange_in', typoutput => 'anyrange_out', typreceive => '-', typsend => '-', typalign => 'd', typstorage => 'x' }, +{ oid => '4142', + typname => 'ignorenulls', descr => 'boolean wrapper, \'true\'/\'false\'', + typlen => '1', typbyval => 't', typtype => 'b', typcategory => 'B', + typinput => 'boolin', typoutput => 'boolout', typreceive => 'boolrecv', + typsend => 'boolsend', typalign => 'c' }, +{ oid => '4143', + typname => 'fromlast', descr => 'boolean wrapper, \'true\'/\'false\'', + typlen => '1', typbyval => 't', typtype => 'b', typcategory => 'B', + typinput => 'boolin', typoutput => 'boolout', typreceive => 'boolrecv', + typsend => 'boolsend', typalign => 'c' }, ] diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 2a2b17d570..ddd9208d8b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -534,6 +534,12 @@ typedef struct WindowDef FRAMEOPTION_END_CURRENT_ROW) /* + * From Last and Null Treatment options + */ +#define WINFUNC_OPT_FROM_LAST 0x00001 /* FROM LAST */ +#define WINFUNC_OPT_IGNORE_NULLS 0x00002 /* IGNORE NULLS */ + +/* * RangeSubselect - subquery appearing in a FROM clause */ typedef struct RangeSubselect diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 23db40147b..a043742768 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -164,6 +164,7 @@ PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD) PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD) PG_KEYWORD("filter", FILTER, UNRESERVED_KEYWORD) PG_KEYWORD("first", FIRST_P, UNRESERVED_KEYWORD) +PG_KEYWORD("first_value", FIRST_VALUE, UNRESERVED_KEYWORD) PG_KEYWORD("float", FLOAT_P, COL_NAME_KEYWORD) PG_KEYWORD("following", FOLLOWING, UNRESERVED_KEYWORD) PG_KEYWORD("for", FOR, RESERVED_KEYWORD) @@ -190,6 +191,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD) PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD) PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD) PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD) +PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD) PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD) PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD) @@ -223,10 +225,13 @@ PG_KEYWORD("isolation", ISOLATION, UNRESERVED_KEYWORD) PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD) PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD) +PG_KEYWORD("lag", LAG, UNRESERVED_KEYWORD) PG_KEYWORD("language", LANGUAGE, UNRESERVED_KEYWORD) PG_KEYWORD("large", LARGE_P, UNRESERVED_KEYWORD) PG_KEYWORD("last", LAST_P, UNRESERVED_KEYWORD) +PG_KEYWORD("last_value", LAST_VALUE, UNRESERVED_KEYWORD) PG_KEYWORD("lateral", LATERAL_P, RESERVED_KEYWORD) +PG_KEYWORD("lead", LEAD, UNRESERVED_KEYWORD) PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD) PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD) PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD) @@ -267,6 +272,7 @@ PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD) PG_KEYWORD("notify", NOTIFY, UNRESERVED_KEYWORD) PG_KEYWORD("notnull", NOTNULL, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("nowait", NOWAIT, UNRESERVED_KEYWORD) +PG_KEYWORD("nth_value", NTH_VALUE, UNRESERVED_KEYWORD) PG_KEYWORD("null", NULL_P, RESERVED_KEYWORD) PG_KEYWORD("nullif", NULLIF, COL_NAME_KEYWORD) PG_KEYWORD("nulls", NULLS_P, UNRESERVED_KEYWORD) @@ -336,6 +342,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD) PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD) PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD) +PG_KEYWORD("respect", RESPECT, UNRESERVED_KEYWORD) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD) diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out index b1419d4bc2..d6f38f5b75 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -73,7 +73,9 @@ WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%' 3361 | pg_ndistinct 3402 | pg_dependencies 210 | smgr -(4 rows) + 4142 | ignorenulls + 4143 | fromlast +(6 rows) -- Make sure typarray points to a varlena array type of our own base SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype, @@ -166,10 +168,12 @@ WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT (p1.typelem != 0 AND p1.typlen < 0) AND NOT (p2.prorettype = p1.oid AND NOT p2.proretset) ORDER BY 1; - oid | typname | oid | proname -------+-----------+-----+--------- - 1790 | refcursor | 46 | textin -(1 row) + oid | typname | oid | proname +------+-------------+------+--------- + 1790 | refcursor | 46 | textin + 4142 | ignorenulls | 1242 | boolin + 4143 | fromlast | 1242 | boolin +(3 rows) -- Varlena array types will point to array_in -- Exception as of 8.1: int2vector and oidvector have their own I/O routines @@ -217,10 +221,12 @@ WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT (p2.oid = 'array_out'::regproc AND p1.typelem != 0 AND p1.typlen = -1))) ORDER BY 1; - oid | typname | oid | proname -------+-----------+-----+--------- - 1790 | refcursor | 47 | textout -(1 row) + oid | typname | oid | proname +------+-------------+------+--------- + 1790 | refcursor | 47 | textout + 4142 | ignorenulls | 1243 | boolout + 4143 | fromlast | 1243 | boolout +(3 rows) SELECT p1.oid, p1.typname, p2.oid, p2.proname FROM pg_type AS p1, pg_proc AS p2 @@ -280,10 +286,12 @@ WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT (p1.typelem != 0 AND p1.typlen < 0) AND NOT (p2.prorettype = p1.oid AND NOT p2.proretset) ORDER BY 1; - oid | typname | oid | proname -------+-----------+------+---------- - 1790 | refcursor | 2414 | textrecv -(1 row) + oid | typname | oid | proname +------+-------------+------+---------- + 1790 | refcursor | 2414 | textrecv + 4142 | ignorenulls | 2436 | boolrecv + 4143 | fromlast | 2436 | boolrecv +(3 rows) -- Varlena array types will point to array_recv -- Exception as of 8.1: int2vector and oidvector have their own I/O routines @@ -340,10 +348,12 @@ WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT (p2.oid = 'array_send'::regproc AND p1.typelem != 0 AND p1.typlen = -1))) ORDER BY 1; - oid | typname | oid | proname -------+-----------+------+---------- - 1790 | refcursor | 2415 | textsend -(1 row) + oid | typname | oid | proname +------+-------------+------+---------- + 1790 | refcursor | 2415 | textsend + 4142 | ignorenulls | 2437 | boolsend + 4143 | fromlast | 2437 | boolsend +(3 rows) SELECT p1.oid, p1.typname, p2.oid, p2.proname FROM pg_type AS p1, pg_proc AS p2 diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 562006a2b8..87b9340129 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -3787,3 +3787,369 @@ SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w 5 | t | t | t (5 rows) +-- FROM LAST and IGNORE NULLS tests +CREATE TEMPORARY TABLE planets ( + name text, + orbit int +); +INSERT INTO planets VALUES + ('mercury', 88), + ('venus', 224), + ('earth', NULL), + ('mars', NULL), + ('jupiter', 4332), + ('saturn', 24491), + ('uranus', NULL), + ('neptune', 60182), + ('pluto', 90560); + -- test view definitions are preserved +CREATE TEMP VIEW v_planets AS + SELECT + name, + sum(orbit) OVER (order by orbit) as sum_rows, + lag(orbit, 1) RESPECT NULLS OVER (ORDER BY name DESC) AS lagged_by_1, + lag(orbit, 2) IGNORE NULLS OVER w AS lagged_by_2, + first_value(orbit) IGNORE NULLS OVER w AS first_value_ignore, + nth_value(orbit,2) FROM FIRST IGNORE NULLS OVER w AS nth_first_ignore, + nth_value(orbit,2) FROM LAST IGNORE NULLS OVER w AS nth_last_ignore + FROM planets + WINDOW w as (ORDER BY name ASC); +SELECT pg_get_viewdef('v_planets'); + pg_get_viewdef +---------------------------------------------------------------------------------- + SELECT planets.name, + + sum(planets.orbit) OVER (ORDER BY planets.orbit) AS sum_rows, + + lag(planets.orbit, 1) OVER (ORDER BY planets.name DESC) AS lagged_by_1, + + lag(planets.orbit, 2) IGNORE NULLS OVER w AS lagged_by_2, + + first_value(planets.orbit) IGNORE NULLS OVER w AS first_value_ignore, + + nth_value(planets.orbit, 2) IGNORE NULLS OVER w AS nth_first_ignore, + + nth_value(planets.orbit, 2) FROM LAST IGNORE NULLS OVER w AS nth_last_ignore+ + FROM planets + + WINDOW w AS (ORDER BY planets.name); +(1 row) + +SELECT name, lag(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lag +---------+------- + earth | + jupiter | + mars | 4332 + mercury | + neptune | 88 + pluto | 60182 + saturn | 90560 + uranus | 24491 + venus | +(9 rows) + +SELECT name, lag(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lag +---------+------- + earth | + jupiter | + mars | 4332 + mercury | + neptune | 88 + pluto | 60182 + saturn | 90560 + uranus | 24491 + venus | +(9 rows) + +SELECT name, lag(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lag +---------+------- + earth | + jupiter | + mars | 4332 + mercury | 4332 + neptune | 88 + pluto | 60182 + saturn | 90560 + uranus | 24491 + venus | 24491 +(9 rows) + +SELECT name, lead(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lead +---------+------- + earth | 4332 + jupiter | + mars | 88 + mercury | 60182 + neptune | 90560 + pluto | 24491 + saturn | + uranus | 224 + venus | +(9 rows) + +SELECT name, lead(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lead +---------+------- + earth | 4332 + jupiter | + mars | 88 + mercury | 60182 + neptune | 90560 + pluto | 24491 + saturn | + uranus | 224 + venus | +(9 rows) + +SELECT name, lead(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lead +---------+------- + earth | 4332 + jupiter | 88 + mars | 88 + mercury | 60182 + neptune | 90560 + pluto | 24491 + saturn | 224 + uranus | 224 + venus | +(9 rows) + +SELECT name, lag(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lag +---------+------- + earth | 4332 + jupiter | 88 + mars | 88 + mercury | 60182 + neptune | 90560 + pluto | 24491 + saturn | 224 + uranus | 224 + venus | +(9 rows) + +SELECT name, lead(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | lead +---------+------- + earth | + jupiter | + mars | 4332 + mercury | 4332 + neptune | 88 + pluto | 60182 + saturn | 90560 + uranus | 24491 + venus | 24491 +(9 rows) + +SELECT name, first_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | first_value +---------+------------- + earth | + jupiter | + mars | + mercury | + neptune | + pluto | + saturn | + uranus | + venus | +(9 rows) + +SELECT name, first_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | first_value +---------+------------- + earth | + jupiter | + mars | + mercury | + neptune | + pluto | + saturn | + uranus | + venus | +(9 rows) + +SELECT name, first_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | first_value +---------+------------- + earth | 4332 + jupiter | 4332 + mars | 4332 + mercury | 4332 + neptune | 4332 + pluto | 4332 + saturn | 4332 + uranus | 4332 + venus | 4332 +(9 rows) + +SELECT name, last_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | last_value +---------+------------ + earth | 224 + jupiter | 224 + mars | 224 + mercury | 224 + neptune | 224 + pluto | 224 + saturn | 224 + uranus | 224 + venus | 224 +(9 rows) + +SELECT name, last_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | last_value +---------+------------ + earth | 224 + jupiter | 224 + mars | 224 + mercury | 224 + neptune | 224 + pluto | 224 + saturn | 224 + uranus | 224 + venus | 224 +(9 rows) + +SELECT name, last_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | last_value +---------+------------ + earth | 224 + jupiter | 224 + mars | 224 + mercury | 224 + neptune | 224 + pluto | 224 + saturn | 224 + uranus | 224 + venus | 224 +(9 rows) + +SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 4332 + jupiter | 4332 + mars | 4332 + mercury | 4332 + neptune | 4332 + pluto | 4332 + saturn | 4332 + uranus | 4332 + venus | 4332 +(9 rows) + +SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 88 + jupiter | 88 + mars | 88 + mercury | 88 + neptune | 88 + pluto | 88 + saturn | 88 + uranus | 88 + venus | 88 +(9 rows) + +SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 4332 + jupiter | 4332 + mars | 4332 + mercury | 4332 + neptune | 4332 + pluto | 4332 + saturn | 4332 + uranus | 4332 + venus | 4332 +(9 rows) + +SELECT name, nth_value(orbit, 2) FROM FIRST OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 4332 + jupiter | 4332 + mars | 4332 + mercury | 4332 + neptune | 4332 + pluto | 4332 + saturn | 4332 + uranus | 4332 + venus | 4332 +(9 rows) + +SELECT name, nth_value(orbit, 2) FROM FIRST IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 88 + jupiter | 88 + mars | 88 + mercury | 88 + neptune | 88 + pluto | 88 + saturn | 88 + uranus | 88 + venus | 88 +(9 rows) + +SELECT name, nth_value(orbit, 2) FROM FIRST RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 4332 + jupiter | 4332 + mars | 4332 + mercury | 4332 + neptune | 4332 + pluto | 4332 + saturn | 4332 + uranus | 4332 + venus | 4332 +(9 rows) + +SELECT name, nth_value(orbit, 2) FROM LAST OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | + jupiter | + mars | + mercury | + neptune | + pluto | + saturn | + uranus | + venus | +(9 rows) + +SELECT name, nth_value(orbit, 2) FROM LAST IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | 24491 + jupiter | 24491 + mars | 24491 + mercury | 24491 + neptune | 24491 + pluto | 24491 + saturn | 24491 + uranus | 24491 + venus | 24491 +(9 rows) + +SELECT name, nth_value(orbit, 2) FROM LAST RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + name | nth_value +---------+----------- + earth | + jupiter | + mars | + mercury | + neptune | + pluto | + saturn | + uranus | + venus | +(9 rows) + +--cleanup +DROP TABLE planets CASCADE; +NOTICE: drop cascades to view v_planets diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index e2943a38f1..95d5125533 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1241,3 +1241,66 @@ SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FO SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b) WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING); + +-- FROM LAST and IGNORE NULLS tests +CREATE TEMPORARY TABLE planets ( + name text, + orbit int +); + +INSERT INTO planets VALUES + ('mercury', 88), + ('venus', 224), + ('earth', NULL), + ('mars', NULL), + ('jupiter', 4332), + ('saturn', 24491), + ('uranus', NULL), + ('neptune', 60182), + ('pluto', 90560); + + -- test view definitions are preserved +CREATE TEMP VIEW v_planets AS + SELECT + name, + sum(orbit) OVER (order by orbit) as sum_rows, + lag(orbit, 1) RESPECT NULLS OVER (ORDER BY name DESC) AS lagged_by_1, + lag(orbit, 2) IGNORE NULLS OVER w AS lagged_by_2, + first_value(orbit) IGNORE NULLS OVER w AS first_value_ignore, + nth_value(orbit,2) FROM FIRST IGNORE NULLS OVER w AS nth_first_ignore, + nth_value(orbit,2) FROM LAST IGNORE NULLS OVER w AS nth_last_ignore + FROM planets + WINDOW w as (ORDER BY name ASC); +SELECT pg_get_viewdef('v_planets'); + +SELECT name, lag(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, lag(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, lag(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + +SELECT name, lead(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, lead(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, lead(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + +SELECT name, lag(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, lead(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + +SELECT name, first_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, first_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, first_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + +SELECT name, last_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, last_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, last_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + +SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) FROM FIRST OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) FROM FIRST IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) FROM FIRST RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) FROM LAST OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) FROM LAST IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; +SELECT name, nth_value(orbit, 2) FROM LAST RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name; + +--cleanup +DROP TABLE planets CASCADE;