Do we support using agg or window functions in delete statement?
Hi,
In transformDeleteStmt:
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasAggs = pstate->p_hasAggs;
if (pstate->p_hasAggs)
parseCheckAggregates(pstate, qry);
These code set agg and window function status for delete query,
but there is no similar code in transformInsertStmt and
transformUpdateStmt.
Do we support using agg or window function in delete statement?
Or, this code should be removed?
Some history of these code:
1. 1996-7-9 "Postgres95 1.01 Distribution - Virgin Sources":
Introduce agg check for insert/update/delete.
in transformDeleteStmt:
/* make sure we don't have aggregates in the where clause */
if (pstate->p_numAgg > 0)
parseCheckAggregates(pstate, qry);
in transformInsertStmt:
if (pstate->p_numAgg > 0)
finalizeAggregates(pstate, qry);
in transformUpdateStmt:
/* make sure we don't have aggregates in the where clause */
if (pstate->p_numAgg > 0)
parseCheckAggregates(pstate, qry);
2. 2006-6-21 "Disallow aggregate functions in UPDATE commands (unless
within a sub-SELECT)":
Change parseCheckAggregates to ereport in transformUpdateStmt.
in transformUpdateStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("cannot use aggregate function in UPDATE")));
3. 2006-8-2 "Add support for multi-row VALUES clauses as part of INSERT
statements":
Change parseCheckAggregates in insert to ereport.
in transformInsertStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("cannot use aggregate function in VALUES")));
4. 2008-12-28 "Support window functions a la SQL:2008.":
Add window function related check for insert/update/delete.
(use the same style as agg)
in transformDeleteStmt:
qry->hasAggs = pstate->p_hasAggs;
if (pstate->p_hasAggs)
parseCheckAggregates(pstate, qry);
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
if (pstate->p_hasWindowFuncs)
parseCheckWindowFuncs(pstate, qry);
in transformInsertStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("cannot use aggregate function in VALUES"),
parser_errposition(pstate,
locate_agg_of_level((Node *) qry, 0))));
if (pstate->p_hasWindowFuncs)
ereport(ERROR,
(errcode(ERRCODE_WINDOWING_ERROR),
errmsg("cannot use window function in VALUES"),
parser_errposition(pstate,
locate_windowfunc((Node *) qry))));
in transformUpdateStmt:
if (pstate->p_hasAggs)
ereport(ERROR,
(errcode(ERRCODE_GROUPING_ERROR),
errmsg("cannot use aggregate function in UPDATE"),
parser_errposition(pstate,
locate_agg_of_level((Node *) qry, 0))));
if (pstate->p_hasWindowFuncs)
ereport(ERROR,
(errcode(ERRCODE_WINDOWING_ERROR),
errmsg("cannot use window function in UPDATE"),
parser_errposition(pstate,
locate_windowfunc((Node *) qry))));
5. 2012-8-10 "Centralize the logic for detecting misplaced aggregates,
window funcs, etc.":
Remove ereport in update/insert.
Thanks
--
GaoZengqi
pgf00a@gmail.com
zengqigao@gmail.com
=?UTF-8?B?6auY5aKe55Cm?= <pgf00a@gmail.com> writes:
In transformDeleteStmt:
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasAggs = pstate->p_hasAggs;
if (pstate->p_hasAggs)
parseCheckAggregates(pstate, qry);
Do we support using agg or window function in delete statement?
Or, this code should be removed?
I think it's dead code given the syntactic limitations on DELETE,
but I would not be in favor of removing it. Better to have it there to
keep transformDeleteStmt looking as much as possible like the other ones.
It's not like that's either expensive or a lot of code.
An example of why this would be penny-wise and pound-foolish is that
we might choose to apply the check that you can't write aggregates in
DELETE inside parseCheckAggregates. (We don't, but it's not an impossible
future restructuring.)
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Thanks a lot for reply.
2017-01-11 20:46 GMT+08:00 Tom Lane <tgl@sss.pgh.pa.us>:
=?UTF-8?B?6auY5aKe55Cm?= <pgf00a@gmail.com> writes:
In transformDeleteStmt:
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
qry->hasAggs = pstate->p_hasAggs;
if (pstate->p_hasAggs)
parseCheckAggregates(pstate, qry);Do we support using agg or window function in delete statement?
Or, this code should be removed?I think it's dead code given the syntactic limitations on DELETE,
but I would not be in favor of removing it. Better to have it there to
keep transformDeleteStmt looking as much as possible like the other ones.
It's not like that's either expensive or a lot of code.
At present, only transformSelectStmt and transformSetOperationStmt
has parseCheckAggregates. All other transformXXXStmt don't contain it.
This inconsistency makes me have the question at the first mail.
I think it maybe better to do something.
An example of why this would be penny-wise and pound-foolish is that
we might choose to apply the check that you can't write aggregates in
DELETE inside parseCheckAggregates. (We don't, but it's not an impossible
future restructuring.)regards, tom lane
--
GaoZengqi
pgf00a@gmail.com
zengqigao@gmail.com