On Mon Jul 21, 2025 at 5:23 PM -03, Vik Fearing wrote:
>
> On 21/07/2025 14:47, Matheus Alcantara wrote:
>> Hi all,
>>
>> I'm sending a proof-of-concept patch to add support for the QUALIFY
>> clause in Postgres. This feature allows filtering rows after window
>> functions are computed, using a syntax similar to the WHERE or HAVING
>> clauses.
>
>
> I took a very brief look at this, and I think your grammar is wrong.
> The QUALIFY clause should go after the WINDOW clause, just like
> FROM/WHERE and GROUP BY/HAVING.
>
>
> That is what I am proposing to the standards committee, and I already
> have some buy-in for that.
>
Thank you for the brief review and for the comments!
I'm not sure if I fully understand but please see the new attached
version.
Thanks,
--
Matheus Alcantara
From 28a4d49c27ba039518d3272aa35cb0176bab7750 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths....@pm.me>
Date: Wed, 4 Jun 2025 15:56:59 -0300
Subject: [PATCH v1] QUALIFY clause
---
src/backend/optimizer/plan/planner.c | 5 ++
src/backend/parser/analyze.c | 8 +-
src/backend/parser/gram.y | 13 ++-
src/backend/parser/parse_agg.c | 7 +-
src/backend/parser/parse_clause.c | 57 +++++++++++-
src/backend/parser/parse_expr.c | 4 +
src/backend/parser/parse_func.c | 3 +
src/backend/parser/parse_relation.c | 18 ++++
src/include/nodes/parsenodes.h | 5 ++
src/include/parser/kwlist.h | 1 +
src/include/parser/parse_clause.h | 5 +-
src/include/parser/parse_node.h | 3 +
src/test/regress/expected/window.out | 125 +++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 51 +++++++++++
14 files changed, 296 insertions(+), 9 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c
b/src/backend/optimizer/plan/planner.c
index 549aedcfa99..1a8fb387e47 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4710,6 +4710,11 @@ create_one_window_path(PlannerInfo *root,
if (!topwindow)
topqual = lappend(topqual, opexpr);
}
+
+ /* Add QUALIFY qual */
+ if (wc->qualifyQual != NULL)
+ topqual = lappend(topqual, (Expr *)
wc->qualifyQual);
+
}
path = (Path *)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 34f7c17f576..dd27fd3730b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1481,9 +1481,12 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
qry->limitOption = stmt->limitOption;
/* transform window clauses after we have seen all window functions */
+ pstate->p_targetList = qry->targetList;
qry->windowClause = transformWindowDefinitions(pstate,
pstate->p_windowdefs,
-
&qry->targetList);
+
&qry->targetList,
+
stmt->qualifyClause);
+
/* resolve any still-unresolved output columns as being type text */
if (pstate->p_resolve_unknowns)
@@ -2975,7 +2978,8 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt
*stmt)
/* transform window clauses after we have seen all window functions */
qry->windowClause = transformWindowDefinitions(pstate,
pstate->p_windowdefs,
-
&qry->targetList);
+
&qry->targetList,
+
NULL); /* FIXME(matheus) */
qry->rtable = pstate->p_rtable;
qry->rteperminfos = pstate->p_rteperminfos;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 73345bb3c70..117f20c8fa2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -520,7 +520,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <node> TableElement TypedTableElement ConstraintElem
DomainConstraintElem TableFuncElement
%type <node> columnDef columnOptions optionalPeriodName
%type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
-%type <node> def_arg columnElem where_clause where_or_current_clause
+%type <node> def_arg columnElem where_clause qualify_clause
where_or_current_clause
a_expr b_expr c_expr AexprConst indirection_el
opt_slice_bound
columnref having_clause func_table xmltable
array_expr
OptWhereClause operator_def_arg
@@ -760,7 +760,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
- QUOTE QUOTES
+ QUALIFY QUOTE QUOTES
RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
@@ -12994,7 +12994,7 @@ select_clause:
simple_select:
SELECT opt_all_clause opt_target_list
into_clause from_clause where_clause
- group_clause having_clause window_clause
+ group_clause having_clause window_clause qualify_clause
{
SelectStmt *n = makeNode(SelectStmt);
@@ -13006,6 +13006,7 @@ simple_select:
n->groupDistinct = ($7)->distinct;
n->havingClause = $8;
n->windowClause = $9;
+ n->qualifyClause = $10;
$$ = (Node *) n;
}
| SELECT distinct_clause target_list
@@ -14135,6 +14136,11 @@ where_clause:
| /*EMPTY*/
{ $$ = NULL; }
;
+qualify_clause:
+ QUALIFY a_expr
{ $$ = $2; }
+ | /*EMPTY*/
{ $$ = NULL; }
+ ;
+
/* variant for UPDATE and DELETE */
where_or_current_clause:
WHERE a_expr
{ $$ = $2; }
@@ -18250,6 +18256,7 @@ reserved_keyword:
| ORDER
| PLACING
| PRIMARY
+ | QUALIFY
| REFERENCES
| RETURNING
| SELECT
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 0ac8966e30f..deeb3584f97 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -402,6 +402,9 @@ check_agglevels_and_constraints(ParseState *pstate, Node
*expr)
case EXPR_KIND_WHERE:
errkind = true;
break;
+ case EXPR_KIND_QUALIFY:
+ errkind = true;
+ break;
case EXPR_KIND_POLICY:
if (isAgg)
err = _("aggregate functions are not allowed in
policy expressions");
@@ -878,8 +881,6 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
case EXPR_KIND_WHERE:
errkind = true;
break;
- case EXPR_KIND_POLICY:
- err = _("window functions are not allowed in policy
expressions");
break;
case EXPR_KIND_HAVING:
errkind = true;
@@ -895,6 +896,8 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
err = _("window functions are not allowed in window
definitions");
break;
case EXPR_KIND_SELECT_TARGET:
+ case EXPR_KIND_POLICY:
+ case EXPR_KIND_QUALIFY:
/* okay */
break;
case EXPR_KIND_INSERT_TARGET:
diff --git a/src/backend/parser/parse_clause.c
b/src/backend/parser/parse_clause.c
index 9f20a70ce13..75f5ab727f9 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1842,6 +1842,51 @@ setNamespaceLateralState(List *namespace, bool
lateral_only, bool lateral_ok)
}
}
+Node *
+transformQualifyClause(ParseState *pstate, List *targetlist, Node *qualify)
+{
+ Node *where;
+
+ if (qualify == NULL)
+ return NULL;
+
+ where = transformWhereClause(pstate,
+ qualify,
+
EXPR_KIND_QUALIFY,
+ "QUALIFY");
+
+ /*
+ * Transform any Var referencing a WindowFunc into a real WindowFunc de
+ * fact.
+ */
+ if (IsA(where, OpExpr))
+ {
+ ListCell *lc;
+ OpExpr *op = (OpExpr *) where;
+ List *newArgs = NIL;
+
+ foreach(lc, op->args)
+ {
+ Node *node = lfirst(lc);
+
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+ TargetEntry *tle = (TargetEntry *)
lfirst(&targetlist->elements[var->varattno - 1]);
+
+
+ Assert(IsA(tle->expr, WindowFunc));
+ newArgs = lappend(newArgs, (Node *) tle->expr);
+ }
+ else
+ newArgs = lappend(newArgs, node);
+ }
+ op->args = newArgs;
+ }
+
+ return where;
+}
+
/*
* transformWhereClause -
@@ -2764,7 +2809,8 @@ transformSortClause(ParseState *pstate,
List *
transformWindowDefinitions(ParseState *pstate,
List *windowdefs,
- List **targetlist)
+ List **targetlist,
+ Node *qualify)
{
List *result = NIL;
Index winref = 0;
@@ -2776,6 +2822,7 @@ transformWindowDefinitions(ParseState *pstate,
WindowClause *refwc = NULL;
List *partitionClause;
List *orderClause;
+ Node *qualifyClause;
Oid rangeopfamily = InvalidOid;
Oid rangeopcintype = InvalidOid;
WindowClause *wc;
@@ -2824,12 +2871,20 @@ transformWindowDefinitions(ParseState *pstate,
EXPR_KIND_WINDOW_PARTITION,
true /* force SQL99 rules */ );
+ /*
+ * transform QUALIFY. targetlist is used find the window
function
+ * reference.
+ *
+ */
+ qualifyClause = transformQualifyClause(pstate, *targetlist,
qualify);
+
/*
* And prepare the new WindowClause.
*/
wc = makeNode(WindowClause);
wc->name = windef->name;
wc->refname = windef->refname;
+ wc->qualifyQual = qualifyClause;
/*
* Per spec, a windowdef that references a previous one copies
the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..d30661e8da5 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -575,6 +575,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
case EXPR_KIND_COPY_WHERE:
case EXPR_KIND_GENERATED_COLUMN:
case EXPR_KIND_CYCLE_MARK:
+ case EXPR_KIND_QUALIFY:
/* okay */
break;
@@ -1794,6 +1795,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_FROM_SUBSELECT:
case EXPR_KIND_FROM_FUNCTION:
case EXPR_KIND_WHERE:
+ case EXPR_KIND_QUALIFY:
case EXPR_KIND_POLICY:
case EXPR_KIND_HAVING:
case EXPR_KIND_FILTER:
@@ -3219,6 +3221,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "GENERATED AS";
case EXPR_KIND_CYCLE_MARK:
return "CYCLE";
+ case EXPR_KIND_QUALIFY:
+ return "QUALIFY";
/*
* There is intentionally no default: case here, so
that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..4e202949bfd 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2557,6 +2557,9 @@ check_srf_call_placement(ParseState *pstate, Node
*last_srf, int location)
case EXPR_KIND_WHERE:
errkind = true;
break;
+ case EXPR_KIND_QUALIFY:
+ errkind = true;
+ break;
case EXPR_KIND_POLICY:
err = _("set-returning functions are not allowed in
policy expressions");
break;
diff --git a/src/backend/parser/parse_relation.c
b/src/backend/parser/parse_relation.c
index 04ecf64b1fc..d3bb9346dec 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -922,6 +922,24 @@ colNameToVar(ParseState *pstate, const char *colname, bool
localonly,
newresult = scanNSItemForColumn(orig_pstate, nsitem,
sublevels_up,
colname, location);
+ /*
+ * If we are parsing a QUALIFY expression try to search
the window
+ * function reference on target list
+ */
+ if (newresult == NULL && orig_pstate->p_hasWindowFuncs
+ && pstate->p_expr_kind == EXPR_KIND_QUALIFY)
+ {
+ ListCell *lc;
+
+ foreach(lc, pstate->p_targetList)
+ {
+ TargetEntry *tle = (TargetEntry *)
lfirst(lc);
+
+ if (strcmp(tle->resname, colname) == 0)
+ newresult = (Node *)
makeVarFromTargetEntry(OUTER_VAR, tle);
+ }
+ }
+
if (newresult)
{
if (result)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..824b7238ca9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -220,6 +220,8 @@ typedef struct Query
Node *havingQual; /* qualifications applied to groups */
+ Node *qualifyQual; /* qualifications applied to window
functions */
+
List *windowClause; /* a list of WindowClause's */
List *distinctClause; /* a list of SortGroupClause's */
@@ -1575,6 +1577,8 @@ typedef struct WindowClause
int frameOptions; /* frame_clause options, see
WindowDef */
Node *startOffset; /* expression for starting bound, if
any */
Node *endOffset; /* expression for ending bound, if any
*/
+ /* QUALIFY clause */
+ Node *qualifyQual;
/* in_range function for startOffset */
Oid startInRangeFunc
pg_node_attr(query_jumble_ignore);
/* in_range function for endOffset */
@@ -2190,6 +2194,7 @@ typedef struct SelectStmt
List *targetList; /* the target list (of ResTarget) */
List *fromClause; /* the FROM clause */
Node *whereClause; /* WHERE qualification */
+ Node *qualifyClause; /* QUALIFY qualification */
List *groupClause; /* GROUP BY clauses */
bool groupDistinct; /* Is this GROUP BY DISTINCT? */
Node *havingClause; /* HAVING conditional-expression */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..f7e267dd241 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -359,6 +359,7 @@ PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD,
BARE_LABEL)
PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("qualify", QUALIFY, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("quotes", QUOTES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_clause.h
b/src/include/parser/parse_clause.h
index 3e9894926de..5580842ee33 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -20,6 +20,8 @@ extern void transformFromClause(ParseState *pstate, List
*frmList);
extern int setTargetTable(ParseState *pstate, RangeVar *relation,
bool inh, bool alsoSource,
AclMode requiredPerms);
+
+extern Node * transformQualifyClause(ParseState *pstate, List *targetlist,
Node *qualify);
extern Node *transformWhereClause(ParseState *pstate, Node *clause,
ParseExprKind
exprKind, const char *constructName);
extern Node *transformLimitClause(ParseState *pstate, Node *clause,
@@ -35,7 +37,8 @@ extern List *transformSortClause(ParseState *pstate, List
*orderlist,
extern List *transformWindowDefinitions(ParseState *pstate,
List *windowdefs,
-
List **targetlist);
+
List **targetlist,
+
Node *qualify);
extern List *transformDistinctClause(ParseState *pstate,
List
**targetlist, List *sortClause, bool is_agg);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..05f0f17ba3d 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -45,6 +45,7 @@ typedef enum ParseExprKind
EXPR_KIND_FROM_FUNCTION, /* function in FROM clause */
EXPR_KIND_WHERE, /* WHERE */
EXPR_KIND_HAVING, /* HAVING */
+ EXPR_KIND_QUALIFY, /* QUALIFY */
EXPR_KIND_FILTER, /* FILTER */
EXPR_KIND_WINDOW_PARTITION, /* window definition PARTITION BY */
EXPR_KIND_WINDOW_ORDER, /* window definition ORDER BY */
@@ -231,6 +232,8 @@ struct ParseState
Node *p_last_srf; /* most recent set-returning func/op
found */
+ List *p_targetList; /* target list (of TargetEntry)
*/
+
/*
* Optional hook functions for parser callbacks. These are null unless
* set up by the caller of make_parsestate.
diff --git a/src/test/regress/expected/window.out
b/src/test/regress/expected/window.out
index b86b668f433..5c886c06141 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4537,6 +4537,131 @@ WHERE first_emp = 1 OR last_emp = 1;
sales | 4 | 4800 | 08-08-2007 | 3 | 1
(6 rows)
+-- Test QUALIFY clause
+SELECT *,
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2;
+ depname | empno | salary | enroll_date | rank
+-----------+-------+--------+-------------+------
+ develop | 8 | 6000 | 10-01-2006 | 1
+ develop | 10 | 5200 | 08-01-2007 | 2
+ develop | 11 | 5200 | 08-15-2007 | 2
+ personnel | 2 | 3900 | 12-23-2006 | 1
+ personnel | 5 | 3500 | 12-10-2007 | 2
+ sales | 1 | 5000 | 10-01-2006 | 1
+ sales | 4 | 4800 | 08-08-2007 | 2
+ sales | 3 | 4800 | 08-01-2007 | 2
+(8 rows)
+
+SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC) = 1;
+ depname | empno | salary | enroll_date | row_number
+-----------+-------+--------+-------------+------------
+ develop | 7 | 4200 | 01-01-2008 | 1
+ personnel | 5 | 3500 | 12-10-2007 | 1
+ sales | 4 | 4800 | 08-08-2007 | 1
+(3 rows)
+
+SELECT *,
+ AVG(salary) OVER (PARTITION BY depname) AS avg_salary
+FROM empsalary
+QUALIFY salary > avg_salary;
+ depname | empno | salary | enroll_date | avg_salary
+-----------+-------+--------+-------------+-----------------------
+ develop | 11 | 5200 | 08-15-2007 | 5020.0000000000000000
+ develop | 8 | 6000 | 10-01-2006 | 5020.0000000000000000
+ develop | 10 | 5200 | 08-01-2007 | 5020.0000000000000000
+ personnel | 2 | 3900 | 12-23-2006 | 3700.0000000000000000
+ sales | 1 | 5000 | 10-01-2006 | 4866.6666666666666667
+(5 rows)
+
+SELECT *,
+ COUNT(*) OVER (PARTITION BY depname, salary)
+FROM empsalary
+QUALIFY COUNT(*) OVER (PARTITION BY depname, salary) = 1;
+ depname | empno | salary | enroll_date | count
+-----------+-------+--------+-------------+-------
+ develop | 7 | 4200 | 01-01-2008 | 1
+ develop | 9 | 4500 | 01-01-2008 | 1
+ develop | 8 | 6000 | 10-01-2006 | 1
+ personnel | 5 | 3500 | 12-10-2007 | 1
+ personnel | 2 | 3900 | 12-23-2006 | 1
+ sales | 1 | 5000 | 10-01-2006 | 1
+(6 rows)
+
+SELECT *,
+ RANK() OVER (ORDER BY salary DESC) as rank
+FROM empsalary
+QUALIFY rank = 2;
+ depname | empno | salary | enroll_date | rank
+---------+-------+--------+-------------+------
+ develop | 10 | 5200 | 08-01-2007 | 2
+ develop | 11 | 5200 | 08-15-2007 | 2
+(2 rows)
+
+SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) <= 3;
+ depname | empno | salary | enroll_date | row_number
+-----------+-------+--------+-------------+------------
+ develop | 8 | 6000 | 10-01-2006 | 1
+ develop | 10 | 5200 | 08-01-2007 | 2
+ develop | 11 | 5200 | 08-15-2007 | 3
+ personnel | 2 | 3900 | 12-23-2006 | 1
+ personnel | 5 | 3500 | 12-10-2007 | 2
+ sales | 1 | 5000 | 10-01-2006 | 1
+ sales | 3 | 4800 | 08-01-2007 | 2
+ sales | 4 | 4800 | 08-08-2007 | 3
+(8 rows)
+
+SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ORDER BY depname, salary DESC;
+ depname | empno | salary | enroll_date | row_number
+-----------+-------+--------+-------------+------------
+ develop | 8 | 6000 | 10-01-2006 | 1
+ develop | 10 | 5200 | 08-01-2007 | 2
+ personnel | 2 | 3900 | 12-23-2006 | 1
+ personnel | 5 | 3500 | 12-10-2007 | 2
+ sales | 1 | 5000 | 10-01-2006 | 1
+ sales | 4 | 4800 | 08-08-2007 | 2
+(6 rows)
+
+SELECT *,
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ AND enroll_date > DATE '2007-01-01';
+ depname | empno | salary | enroll_date | rank
+-----------+-------+--------+-------------+------
+ develop | 10 | 5200 | 08-01-2007 | 2
+ develop | 11 | 5200 | 08-15-2007 | 2
+ personnel | 5 | 3500 | 12-10-2007 | 2
+ sales | 4 | 4800 | 08-08-2007 | 2
+ sales | 3 | 4800 | 08-01-2007 | 2
+(5 rows)
+
+SELECT *,
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC) = 1
+ OR salary < 4000;
+ depname | empno | salary | enroll_date | rank
+-----------+-------+--------+-------------+------
+ develop | 8 | 6000 | 10-01-2006 | 1
+ personnel | 2 | 3900 | 12-23-2006 | 1
+ personnel | 5 | 3500 | 12-10-2007 | 2
+ sales | 1 | 5000 | 10-01-2006 | 1
+(4 rows)
+
-- cleanup
DROP TABLE empsalary;
-- test user-defined window function with named args and default args
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 02f105f070e..4f376d1b459 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1522,6 +1522,57 @@ SELECT * FROM
FROM empsalary) emp
WHERE first_emp = 1 OR last_emp = 1;
+-- Test QUALIFY clause
+SELECT *,
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2;
+
+SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC) = 1;
+
+SELECT *,
+ AVG(salary) OVER (PARTITION BY depname) AS avg_salary
+FROM empsalary
+QUALIFY salary > avg_salary;
+
+SELECT *,
+ COUNT(*) OVER (PARTITION BY depname, salary)
+FROM empsalary
+QUALIFY COUNT(*) OVER (PARTITION BY depname, salary) = 1;
+
+SELECT *,
+ RANK() OVER (ORDER BY salary DESC) as rank
+FROM empsalary
+QUALIFY rank = 2;
+
+SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) <= 3;
+
+SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ORDER BY depname, salary DESC;
+
+SELECT *,
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ AND enroll_date > DATE '2007-01-01';
+
+SELECT *,
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+ RANK() OVER (PARTITION BY depname ORDER BY salary DESC) = 1
+ OR salary < 4000;
+
-- cleanup
DROP TABLE empsalary;
--
2.39.5 (Apple Git-154)
