[HACKERS] Do we support using agg or window functions in delete statement? - Mailing list 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
   

--