Thread: 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/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...
lower() and upper() both use it.
David J.
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
<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Tue, Aug 16, 2016 at 9:29 PM, Tom Lane <spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquoteclass="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><spanclass="">Jim Nasby <<a href="mailto:Jim.Nasby@bluetreble.com">Jim.Nasby@bluetreble.com</a>>writes:<br /> > I can't think of any reason you'dwant two different range types on a<br /> > single element type.<br /><br /></span>We would not have built it thatway if there were not clear use-cases.<br /> An easy example is you might want both a continuous timestamp range<br />and one that is quantized to hour boundaries. Primarily what the<br /> range type brings in besides the element type isa canonicalization<br /> function; and we can't guess which one you want.<br /><br /> regards,tom lane<br /><div class=""><div class="h5"><br /><br /> --<br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" rel="noreferrer" target="_blank">http://www.postgresql.org/<wbr/>mailpref/pgsql-hackers</a><br /></div></div></blockquote></div><br /></div><divclass="gmail_extra"><br />Jim,<br /><br /></div><div class="gmail_extra">I wrote a routine that fishes in thedictionary for a suitable range type:<br /><a href="https://github.com/moat/range_partitioning/blob/master/sql/range_partitioning.sql#L459-L485">https://github.com/moat/range_partitioning/blob/master/sql/range_partitioning.sql#L459-L485</a><br /><br/>Obviously, it has the problems when the number of suitable ranges <> 1 as mentioned by Tom.</div><div class="gmail_extra"><br/></div><div class="gmail_extra">You might also find some gleanable gems in:<br /><a href="https://github.com/moat/range_type_functions/blob/master/doc/range_type_functions.md">https://github.com/moat/range_type_functions/blob/master/doc/range_type_functions.md</a><br /></div><divclass="gmail_extra"><br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra"><br /></div><divclass="gmail_extra"><br /></div></div>
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
<div dir="ltr">I'd be happy to roll your code into the extension, and make it marked more stable.</div><div class="gmail_extra"><br/><div class="gmail_quote">On Thu, Aug 18, 2016 at 2:49 PM, Jim Nasby <span dir="ltr"><<a href="mailto:Jim.Nasby@bluetreble.com"target="_blank">Jim.Nasby@bluetreble.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On 8/18/16 1:06 PM,Corey Huinker wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">You might also find some gleanable gems in:<br /><a href="https://github.com/moat/range_type_functions/blob/master/doc/range_type_functions.md"rel="noreferrer" target="_blank">https://github.com/moat/range_<wbr/>type_functions/blob/master/doc<wbr />/range_type_functions.md</a><br/></blockquote><br /></span> Well crap, I searched for range stuff on PGXN before creating<a href="http://pgxn.org/dist/range_tools/" rel="noreferrer" target="_blank">http://pgxn.org/dist/range_too<wbr />ls/</a>and the only thing that came up was your range_partitioning stuff, which AFAICT is unrelated. <a href="http://pgxn.org/dist/range_type_functions/"rel="noreferrer" target="_blank">http://pgxn.org/dist/range_typ<wbr />e_functions/</a>still doesn't show up in search, maybe because it's marked unstable?<br /><br /> Rather frustrating thatI've spent time creating an extension that duplicates your work. :(<div class="HOEnZb"><div class="h5"><br /> -- <br/> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX<br /> Experts in Analytics, Data Architecture and PostgreSQL<br/> Data in Trouble? Get it in Treble! <a href="http://BlueTreble.com" rel="noreferrer" target="_blank">http://BlueTreble.com</a><br/> 855-TREBLE2 <a href="tel:%28855-873-2532" target="_blank" value="+18558732532">(855-873-2532</a>) mobile: <a href="tel:512-569-9461" target="_blank" value="+15125699461">512-569-9461</a><br/></div></div></blockquote></div><br /></div>
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
On Fri, Aug 19, 2016 at 11:40 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
No rush, I'm on vacation. Though I really do appreciate other eyes on the code and other people using it.
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.
No rush, I'm on vacation. Though I really do appreciate other eyes on the code and other people using it.