Обсуждение: anyelement -> anyrange

Any reason why we can create a function that accepts anyelement and 
returns anyarray, but can't do the same with anyrange? Could we attempt 
to match each range subtype looking for a match?

create function range__create(anyelement,anyelement,text = '[]') RETURNS 
anyrange LANGUAGE plpgsql AS $body$
BEGIN
RETURN int4range($1,$2,$3)
END$body$;
ERROR:  42P13: cannot determine result data type
DETAIL:  A function returning "anyrange" must have at least one 
"anyrange" argument.

create function array__create(anyelement,anyelement) RETURNS anyarray 
LANGUAGE plpgsql AS $body$
BEGIN
RETURN array[$1,$2];
END$body$;
CREATE FUNCTION
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> Any reason why we can create a function that accepts anyelement and 
> returns anyarray, but can't do the same with anyrange?

Because there can be more than one range type over the same element
type, so we couldn't deduce which one should be used for anyrange.

The other direction (inferring anyelement from anyrange) does work.
        regards, tom lane

On 8/15/16 10:12 PM, Tom Lane wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> Any reason why we can create a function that accepts anyelement and
>> returns anyarray, but can't do the same with anyrange?
>
> Because there can be more than one range type over the same element
> type, so we couldn't deduce which one should be used for anyrange.
>
> The other direction (inferring anyelement from anyrange) does work.

Is there an actual use case for that? I'm not seeing what it would be...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 8/16/16 6:56 PM, David G. Johnston wrote:
> On Tue, Aug 16, 2016 at 7:47 PM, Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>>wrote:
>
>     On 8/15/16 10:12 PM, Tom Lane wrote:
>
>         Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>
>             Any reason why we can create a function that accepts
>             anyelement and
>             returns anyarray, but can't do the same with anyrange?
>
>
>         Because there can be more than one range type over the same element
>         type, so we couldn't deduce which one should be used for anyrange.
>
>         The other direction (inferring anyelement from anyrange) does work.
>
>
>     Is there an actual use case for that? I'm not seeing what it would be...
>
>
> ​https://www.postgresql.org/docs/9.5/static/functions-range.html
>
> lower() and upper() both use it.

Nothing built in uses what Tom mentioned: having multiple *range types* 
for a single base type. lower() and upper() use *anyrange*, which is a 
completely different animal.

I can't think of any reason you'd want two different range types on a 
single element type. If we made that a constraint, we could resolve an 
anyrange from an anyelement. That would be very useful in some cases 
(one example being the range_from_array() functions I just created).

BTW, another option would be to allow marking a specific range type as 
being "primary", so if you did need to define some other variation on 
int4range you could do so, but you'd have to decide whether it or 
int4range was the primary one that anyelement->anyrange would use.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Jim Nasby <Jim.Nasby@bluetreble.com> writes:
> I can't think of any reason you'd want two different range types on a 
> single element type.

We would not have built it that way if there were not clear use-cases.
An easy example is you might want both a continuous timestamp range
and one that is quantized to hour boundaries.  Primarily what the
range type brings in besides the element type is a canonicalization
function; and we can't guess which one you want.
        regards, tom lane

On 8/18/16 1:06 PM, Corey Huinker wrote:
> You might also find some gleanable gems in:
> https://github.com/moat/range_type_functions/blob/master/doc/range_type_functions.md

Well crap, I searched for range stuff on PGXN before creating 
http://pgxn.org/dist/range_tools/ and the only thing that came up was 
your range_partitioning stuff, which AFAICT is unrelated. 
http://pgxn.org/dist/range_type_functions/ still doesn't show up in 
search, maybe because it's marked unstable?

Rather frustrating that I've spent time creating an extension that 
duplicates your work. :(
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Aug 18, 2016, at 11:49 AM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:

> Well crap, I searched for range stuff on PGXN before creating http://pgxn.org/dist/range_tools/ and the only thing
thatcame up was your range_partitioning stuff, which AFAICT is unrelated.
http://pgxn.org/dist/range_type_functions/stilldoesn't show up in search, maybe because it's marked unstable? 

Yep. https://github.com/pgxn/pgxn-api/issues/2

David

On 8/18/16 6:02 PM, Corey Huinker wrote:
> I'd be happy to roll your code into the extension, and make it marked
> more stable.

Yeah, I've been meaning to look at submitting a pull request; hopefully 
will get to it today.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461