pg13_ignore_nulls.patch

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28035f1635..3d73c96891 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15702,7 +15702,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <function>
          lag(<replaceable class="parameter">value</replaceable> <type>anyelement</type>
              [, <replaceable class="parameter">offset</replaceable> <type>integer</type>
-             [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]])
+             [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]]) [null_treatment]
        </function>
       </entry>
       <entry>
@@ -15731,7 +15731,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <function>
          lead(<replaceable class="parameter">value</replaceable> <type>anyelement</type>
               [, <replaceable class="parameter">offset</replaceable> <type>integer</type>
-              [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]])
+              [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]]) [null_treatment]
        </function>
       </entry>
       <entry>
@@ -15757,7 +15757,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <indexterm>
         <primary>first_value</primary>
        </indexterm>
-       <function>first_value(<replaceable class="parameter">value</replaceable> <type>any</type>)</function>
+       <function>first_value(<replaceable class="parameter">value</replaceable> <type>any</type>) [null_treatment]</function>
       </entry>
       <entry>
        <type>same type as <replaceable class="parameter">value</replaceable></type>
@@ -15773,7 +15773,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <indexterm>
         <primary>last_value</primary>
        </indexterm>
-       <function>last_value(<replaceable class="parameter">value</replaceable> <type>any</type>)</function>
+       <function>last_value(<replaceable class="parameter">value</replaceable> <type>any</type>) [null_treatment]</function>
       </entry>
       <entry>
        <type>same type as <replaceable class="parameter">value</replaceable></type>
@@ -15790,7 +15790,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
         <primary>nth_value</primary>
        </indexterm>
        <function>
-         nth_value(<replaceable class="parameter">value</replaceable> <type>any</type>, <replaceable class="parameter">nth</replaceable> <type>integer</type>)
+         nth_value(<replaceable class="parameter">value</replaceable> <type>any</type>, <replaceable class="parameter">nth</replaceable> <type>integer</type>) [null_treatment]
        </function>
       </entry>
       <entry>
@@ -15806,6 +15806,16 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
    </tgroup>
   </table>
 
+  <para>
+   In <xref linkend="functions-window-table"/>, <replaceable>null_treatment</replaceable> is one of:
+   <synopsis>
+     RESPECT NULLS
+     IGNORE NULLS
+   </synopsis>
+   <literal>RESPECT NULLS</literal> specifies the default behavior to include nulls in the result.
+   <literal>IGNORE NULLS</literal> ignores any null values when determining a result.
+  </para>
+
   <para>
    All of the functions listed in
    <xref linkend="functions-window-table"/> depend on the sort ordering
@@ -15843,17 +15853,11 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
 
   <note>
    <para>
-    The SQL standard defines a <literal>RESPECT NULLS</literal> or
-    <literal>IGNORE NULLS</literal> option for <function>lead</function>, <function>lag</function>,
-    <function>first_value</function>, <function>last_value</function>, and
-    <function>nth_value</function>.  This is not implemented in
-    <productname>PostgreSQL</productname>: the behavior is always the
-    same as the standard's default, namely <literal>RESPECT NULLS</literal>.
-    Likewise, the standard's <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
-    option for <function>nth_value</function> is not implemented: only the
-    default <literal>FROM FIRST</literal> behavior is supported.  (You can achieve
-    the result of <literal>FROM LAST</literal> by reversing the <literal>ORDER BY</literal>
-    ordering.)
+    The SQL standard defines a <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
+    option for <function>nth_value</function>. This is not implemented in <productname>PostgreSQL</productname>:
+    only the default <literal>FROM FIRST</literal> behavior is supported.
+    (You can achieve the result of <literal>FROM LAST</literal> by using negative number for the position argument,
+    as is done in many languages to indicate a <literal>FROM END</literal> index.)
    </para>
   </note>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 9f840ddfd2..a355e379e7 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -508,9 +508,9 @@ T612	Advanced OLAP operations			YES
 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	FROM LAST is supported by using negative number for the position argument
 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 96e7fdbcfe..db369b1f9a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -154,6 +154,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);
@@ -569,7 +570,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	xml_namespace_list
 %type <target>	xml_namespace_el
 
-%type <node>	func_application func_expr_common_subexpr
+%type <node>	func_application func_expr_common_subexpr func_expr_respect_ignore
 %type <node>	func_expr func_expr_windowless
 %type <node>	common_table_expr
 %type <with>	with_clause opt_with_clause
@@ -577,6 +578,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type <list>	within_group_clause
 %type <node>	filter_clause
+%type <ival>	null_treatment_clause
 %type <list>	window_clause window_definition_list opt_partition_clause
 %type <windef>	window_definition over_clause window_specification
 				opt_frame_clause frame_extent frame_bound
@@ -642,14 +644,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
 	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
@@ -658,14 +660,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
@@ -680,7 +682,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
@@ -724,6 +726,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
@@ -13769,6 +13772,10 @@ func_application: func_name '(' ')'
 				}
 		;
 
+null_treatment_clause:
+			RESPECT NULLS_P			{ $$ = 0; }
+			| IGNORE_P NULLS_P		{ $$ = WINFUNC_OPT_IGNORE_NULLS; }
+		;
 
 /*
  * func_expr and its cousin func_expr_windowless are split out from c_expr just
@@ -13816,8 +13823,133 @@ func_expr: func_application within_group_clause filter_clause over_clause
 				}
 			| func_expr_common_subexpr
 				{ $$ = $1; }
+			| func_expr_respect_ignore over_clause
+				{
+					FuncCall *n = (FuncCall *) $1;
+					n->over = $2;
+					$$ = (Node *) n;
+				}
+		;
+
+func_expr_respect_ignore:
+			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 ')' null_treatment_clause
+				{
+					FuncCall *n;
+					List *l = $3;
+					int winFuncArgs = $6;
+
+					/* Convert Nulls option to bool */
+					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;
+				}
 		;
 
+
 /*
  * As func_expr but does not accept WINDOW functions directly
  * (but they can still be contained in arguments for functions etc).
@@ -15225,6 +15357,7 @@ unreserved_keyword:
 			| FAMILY
 			| FILTER
 			| FIRST_P
+			| FIRST_VALUE
 			| FOLLOWING
 			| FORCE
 			| FORWARD
@@ -15240,6 +15373,7 @@ unreserved_keyword:
 			| HOUR_P
 			| IDENTITY_P
 			| IF_P
+			| IGNORE_P
 			| IMMEDIATE
 			| IMMUTABLE
 			| IMPLICIT_P
@@ -15260,9 +15394,12 @@ unreserved_keyword:
 			| ISOLATION
 			| KEY
 			| LABEL
+			| LAG
 			| LANGUAGE
 			| LARGE_P
 			| LAST_P
+			| LAST_VALUE
+			| LEAD
 			| LEAKPROOF
 			| LEVEL
 			| LISTEN
@@ -15290,6 +15427,7 @@ unreserved_keyword:
 			| NOTHING
 			| NOTIFY
 			| NOWAIT
+			| NTH_VALUE
 			| NULLS_P
 			| OBJECT_P
 			| OF
@@ -15341,6 +15479,7 @@ unreserved_keyword:
 			| REPLACE
 			| REPLICA
 			| RESET
+			| RESPECT
 			| RESTART
 			| RESTRICT
 			| RETURNS
@@ -15815,6 +15954,15 @@ 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);
 
@@ -15822,7 +15970,7 @@ makeBoolAConst(bool state, int location)
 	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 158784474d..ae7e821ab2 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9437,6 +9437,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
 	int			nargs;
 	List	   *argnames;
 	ListCell   *l;
+	bool		ignorenulls = false;
 
 	if (list_length(wfunc->args) > FUNC_MAX_ARGS)
 		ereport(ERROR,
@@ -9463,7 +9464,32 @@ 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 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 == IGNORENULLSOID)
+				{
+					/* parser does not save RESPECT NULLS arguments */
+					ignorenulls = true;
+					buf->len -= 2;
+				}
+			}
+		}
+        }
+
+	appendStringInfoChar(buf, ')');
+	if (ignorenulls)
+		appendStringInfoString(buf, " IGNORE NULLS");
 
 	if (wfunc->aggfilter != NULL)
 	{
@@ -9471,7 +9497,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
 		get_rule_expr((Node *) wfunc->aggfilter, context, false);
 	}
 
-	appendStringInfoString(buf, ") OVER ");
+	appendStringInfoString(buf, " OVER ");
 
 	foreach(l, context->windowClause)
 	{
@@ -9649,6 +9675,10 @@ get_const_expr(Const *constval, deparse_context *context, int showtype)
 				appendStringInfoString(buf, "false");
 			break;
 
+		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 f0c8ae686d..bc639b1883 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -38,7 +38,12 @@ 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);
+static Datum
+window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth);
 
 /*
  * utility routine for *_rank functions.
@@ -328,6 +333,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 +441,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 +494,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 +533,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 +579,149 @@ 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)
 {
 	WindowObject winobj = PG_WINDOW_OBJECT();
 	bool		const_offset;
 	Datum		result;
 	bool		isnull;
-	int32		nth;
+	bool		fromlast;
 
 	nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
 	if (isnull)
 		PG_RETURN_NULL();
 	const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
 
-	if (nth <= 0)
+	if (nth == 0)
 		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
-				 errmsg("argument of nth_value must be greater than zero")));
+		(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+		errmsg("argument of nth_value must be greater or less than zero")));
+	else if (nth < 0)
+	{
+		nth = abs(nth);
+		fromlast = true;
+	}
+	else
+		fromlast = false;
+
+	result = WinGetFuncArgInFrame(winobj,
+								0,
+								nth - 1,
+								fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD, const_offset,
+								&isnull,
+								NULL);
 
-	result = WinGetFuncArgInFrame(winobj, 0,
-								  nth - 1, 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)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	Datum		result;
+	bool		isnull,
+				isout;
+	bool		fromlast;
+	int32		tmp_offset, notnull_offset = 0;
+
+	nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+	if (isnull)
+		PG_RETURN_NULL();
+
+	if (nth == 0)
+		ereport(ERROR,
+		(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+		errmsg("argument of nth_value must be greater or less than zero")));
+	else if (nth < 0)
+	{
+		nth = abs(nth);
+		fromlast = true;
+		tmp_offset = 1;
+	}
+	else
+	{
+		fromlast = false;
+		tmp_offset = -1;
+	}
+
+	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,
+								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));
+}
+
+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));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..f07a9e442d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9539,32 +9539,64 @@
 { oid => '3106', descr => 'fetch the preceding row value',
   proname => 'lag', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement', prosrc => 'window_lag' },
+{ oid => '4191', 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 => '4192', 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 => '4193', 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 => '4194', 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 => '4195', 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 => '4196', 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 => '4197', 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 => '4198', 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 => '4199', 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' },
 
 # 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 4cf2b9df7b..3c847e6db5 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -594,5 +594,10 @@
   typname => 'anyrange', typlen => '-1', typbyval => 'f', typtype => 'p',
   typcategory => 'P', typinput => 'anyrange_in', typoutput => 'anyrange_out',
   typreceive => '-', typsend => '-', typalign => 'd', typstorage => 'x' },
+{ oid => '4142',
+  descr => 'boolean wrapper, \'true\'/\'false\'',
+  typname => 'ignorenulls', 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 da0706add5..0899bddd6e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -532,6 +532,11 @@ typedef struct WindowDef
 	(FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \
 	 FRAMEOPTION_END_CURRENT_ROW)
 
+/*
+ * Null Treatment option
+ */
+#define WINFUNC_OPT_IGNORE_NULLS			0x00001 /* IGNORE NULLS */
+
 /*
  * RangeSubselect - subquery appearing in a FROM clause
  */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index b1184c2d15..a33ce1d0fd 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 cd7fc03b04..0ac49263ec 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -73,7 +73,8 @@ WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%'
  3361 | pg_ndistinct
  3402 | pg_dependencies
  5017 | pg_mcv_list
-(4 rows)
+ 4142 | ignorenulls
+(5 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 +167,11 @@ 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
+(2 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 +219,11 @@ 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
+(2 rows)
 
 SELECT p1.oid, p1.typname, p2.oid, p2.proname
 FROM pg_type AS p1, pg_proc AS p2
@@ -280,10 +283,11 @@ 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
+(2 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 +344,11 @@ 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
+(2 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 d5fd4045f9..54ded65906 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2985,7 +2985,7 @@ LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
 SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
 ERROR:  argument of ntile must be greater than zero
 SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
-ERROR:  argument of nth_value must be greater than zero
+ERROR:  argument of nth_value must be greater or less than zero
 -- filter
 SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
     sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
@@ -3863,3 +3863,369 @@ SELECT * FROM pg_temp.f(2);
  {5}
 (5 rows)
 
+-- RESPECT NULLS 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) IGNORE NULLS OVER w AS nth_first_ignore,
+      nth_value(orbit,-2) 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'::integer) 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) 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) 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) 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) 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 fe273aa31e..548902d482 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1276,3 +1276,67 @@ $$ LANGUAGE SQL STABLE;
 
 EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
 SELECT * FROM pg_temp.f(2);
+
+-- RESPECT NULLS 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) IGNORE NULLS OVER w AS nth_first_ignore,
+      nth_value(orbit,-2) 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) 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) 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;
+
+--cleanup
+DROP TABLE planets CASCADE;