Re: WITHIN GROUP patch - Mailing list pgsql-hackers

>>>>> "Josh" == Josh Berkus <josh@agliodbs.com> writes:
>> Since I don't particularly trust my own judgement on aesthetics, I>> used the feedback I got from others when
decidingwhat to>> do. Frankly I think this one needs wider input than just you and>> me arguing over it.
 
Josh> Can someone paste examples of the two syntax alternatives we'reJosh> talking about here?  I've lost track.

OK. The starting point is that this is the calling syntax for ordered
set funcs, set by the spec:

SELECT func(foo) WITHIN GROUP (ORDER BY bar) FROM ...

where "foo" and "bar" might be fixed types, or polymorphic or variadic.

So we need to define (with no help from the spec) at least these:
 - what syntax is used in CREATE AGGREGATE to specify the number and   types of parameters for a newly defined "func"
 - what syntax is used to refer to the function in a   GRANT ... ON FUNCTION ...  statement, or ALTER ... OWNER TO ...
 - what should ::regprocedure casts accept as input and produce as   output
 - what output is shown in \df and \da when listing the function's   argument types

The patch as submitted answers those questions as follows:

CREATE AGGREGATE func(integer) WITHIN GROUP (text) ...

GRANT ... ON FUNCTION func(integer,text) ...
(there is no GRANT ... ON AGGREGATE ... (yet))

ALTER AGGREGATE func(integer) WITHIN GROUP (text) OWNER TO ...
ALTER AGGREGATE func(integer,text) OWNER TO ...
ALTER FUNCTION func(integer,text) OWNER TO ...
(all three of those are equivalent)

regprocedure outputs 'func(integer,text)' and accepts only that as
input

postgres=# \df func                            List of functionsSchema | Name | Result data type |     Argument data
types      | Type 
 
--------+------+------------------+-------------------------------+------public | func | text             | (integer)
WITHINGROUP (text) | agg
 
(1 row)

If there's also a func() that isn't an ordered set function, you get
output like this (which provoked tom's "schitzophrenic" comment):

postgres=# \df ran[a-z]{1,5}                                  List of functions  Schema   |   Name   | Result data type
|       Argument data types        |  Type  
 
------------+----------+------------------+-----------------------------------+--------pg_catalog | random   | double
precision|                                   | normalpg_catalog | rangesel | double precision | internal, oid,
internal,integer  | normalpg_catalog | rank     | bigint           |                                   |
windowpg_catalog| rank     | bigint           | (VARIADIC "any") WITHIN GROUP (*) | agg
 
(4 rows)

-- 
Andrew (irc:RhodiumToad)