Do we support using agg or window functions in delete statement?

From: 高增琦 <pgf00a(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Do we support using agg or window functions in delete statement?
Date: 2017-01-11 07:46:48
Message-ID: CAFmBtr1XmDki9caowb9GQHqxaxH4EgVf5YiTSqMj1kPA7Rn-2w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/parser/analyze.c;h=504e557abee8651596a9219dca069fcd20ecdaac;hb=d31084e9d1118b25fd16580d9d8c2924b5740dff

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.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=4e30d2b96f3f58176f74aa0061660f47ca0b6426;hp=566c9a0488df68c94effea9e3f59d82da930eb18;hb=1f5ca045a435bc6aa9c98d7296973925c5208add;hpb=e256bafaa2aec06dd9dc9493c4e600319ab11525

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.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=4f7001b6f1ac4fdd9fe65cba835eeb2513f8ad06;hp=b086afe8ca25a8d91314d352b847c47f3a05d32e;hb=9caafda579f699b43fa4c89bf13a2331ef00611e;hpb=d307c428cbb7c426e40163d234d993e644bbcc6b

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)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=70688655cce18ac317faeafa2b51225f320fe493;hp=cdac02b71db69399e00b4a63eefe0d2f9f481ad0;hb=95b07bc7f5010233f52f9d11da74e2e5b653b0a7;hpb=38e9348282e9d078487147ba8a85aebec54e3a08

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.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/parser/analyze.c;h=6c3d89a14f6b1f19176864af4a0ea18eebd9f4bd;hp=93ef724ffff6c51850aca1292e9d6388a0f97a0b;hb=eaccfded98a9c677d3a2e849c1747ec90e8596a6;hpb=b3055ab4fb5839a872bfe354b2b5ac31e6903ed6

Thanks

--
GaoZengqi
pgf00a(at)gmail(dot)com
zengqigao(at)gmail(dot)com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2017-01-11 07:50:13 Re: [PATCH] Refactor "if(strspn(str, ...) == strlen(str)" code
Previous Message Etsuro Fujita 2017-01-11 07:45:12 Re: postgres_fdw bug in 9.6