Обсуждение: 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