Обсуждение: User-defined function with anyrange[]
Hello, I want to make an aggregate function range_agg(anyrange) that returns anyrange[]. But when I try to define it, Postgres tells me it doesn't know what an anyrange[] is. I get this error: ERROR: type anyrange[] does not exist I also tried taking an anyrange and returning an anyarray, which does let me define the function, but upon calling it I get an error. For example: paul=# CREATE OR REPLACE FUNCTION range_agg4(anyrange) RETURNS anyarray AS $$ BEGIN RETURN ARRAY[$1]; END; $$ LANGUAGE plpgsql; paul=# select range_agg4(daterange('2016-05-04', '2016-07-01')); ERROR: invalid input syntax for type date: "[2016-05-04,2016-07-01)" CONTEXT: PL/pgSQL function range_agg4(anyrange) while casting return value to function's return type So I guess it thinks that daterange in means date[] out (not daterange[] out). The best I can think of is to define range_agg(anyelement) that returns anyarray. That actually works how I hope: paul=# CREATE OR REPLACE FUNCTION range_agg3(anyelement) RETURNS anyarray AS $$ BEGIN RETURN ARRAY[$1]; END; $$ LANGUAGE plpgsql; paul=# select range_agg3(daterange('2016-05-04', '2016-07-01')); range_agg3 ----------------------------- {"[2016-05-04,2016-07-01)"} But of course that is not as restricted as I'd like. Are there any better options? Thanks, Paul
Paul A Jungwirth <pj@illuminatedcomputing.com> writes: > I want to make an aggregate function range_agg(anyrange) that returns > anyrange[]. But when I try to define it, Postgres tells me it doesn't > know what an anyrange[] is. I get this error: Yeah, there is no such thing as anyrange[], nor arrays over any other pseudo-type either. > I also tried taking an anyrange and returning an anyarray, which does > let me define the function, but upon calling it I get an error. For > example: > paul=# CREATE OR REPLACE FUNCTION range_agg4(anyrange) > RETURNS anyarray > AS $$ > BEGIN > RETURN ARRAY[$1]; > END; > $$ > LANGUAGE plpgsql; The trouble with this is that anyarray and anyrange are both implicitly related to the "anyelement" pseudo-type, which represents their element type. So if anyrange is associated with daterange in a particular function call, then anyelement is associated with date, and then anyarray is associated with date[] not daterange[]. I don't think there's any way to get what you want using a single polymorphic function, at least not without some sort of extension to the polymorphism rules. However, you can use overloading to define several functions of the same name, and just write out one for each range type you actually need this functionality for. I haven't really seen applications that need so many range types that this'd be intolerable. regards, tom lane
> you can use overloading to define > several functions of the same name, and just write out one for each > range type you actually need this functionality for. Thanks! I was hoping to avoid that, but it's what I wound up doing after all, as you can see here: https://github.com/pjungwir/range_agg In the README I give instructions to create a new aggregate with a custom range type, and it is actually not bad at all, since the same C function can serve as implementation in all cases. Thanks again, Paul