Thread: [BUGS] BUG #14854: daterange[] is an anyarray or anyrange?
The following bug has been logged on the website: Bug reference: 14854 Logged by: Balazs Szilfai Email address: balazs@obiserver.hu PostgreSQL version: 9.6.5 Operating system: Debian Linux Description: I can't create function with param to accept a pseudo-type to daterange and daterange[] (array of daterange). I tried: CREATE FUNCTION range_overlap_array_any(anyrange, anyarray) RETURNS boolean AS $$SELECT false;$$ LANGUAGE sql IMMUTABLE STRICT; CREATE FUNCTION range_overlap_array_any(anyrange, anyrange) RETURNS boolean AS $$SELECT false;$$ LANGUAGE sql IMMUTABLE STRICT; My queries and the error messages: SELECT range_overlap_array_any(daterange('2017-01-01', '2017-04-01'), array[daterange('2016-12-10', '2016-12-11')]); ERROR: function range_overlap_array_any(daterange, daterange[]) does not exist SELECT range_overlap_array_any(daterange('2017-01-01', '2017-04-01'), array['x'::text]); ERROR: function range_overlap_array_any(daterange, text[]) does not exist What's the mistake? Or did I break something? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
The following bug has been logged on the website:
Bug reference: 14854
Logged by: Balazs Szilfai
Email address: balazs@obiserver.hu
PostgreSQL version: 9.6.5
Operating system: Debian Linux
Description:
I can't create function with param to accept a pseudo-type to daterange and
daterange[] (array of daterange).
I tried:
CREATE FUNCTION range_overlap_array_any(anyrange, anyarray) RETURNS boolean
AS $$SELECT false;$$ LANGUAGE sql IMMUTABLE STRICT;CREATE FUNCTION range_overlap_array_any(anyrange, anyrange) RETURNS boolean
AS $$SELECT false;$$ LANGUAGE sql IMMUTABLE STRICT;
My queries and the error messages:
SELECT range_overlap_array_any(daterange('2017-01-01', '2017-04-01'),
array[daterange('2016-12-10', '2016-12-11')]);
ERROR: function range_overlap_array_any(daterange, daterange[]) does not
exist
SELECT range_overlap_array_any(daterange('2017-01-01', '2017-04-01'),
array['x'::text]);
ERROR: function range_overlap_array_any(daterange, text[]) does not exist
What's the mistake? Or did I break something?
When a pseudo-type is used in a function parameter specification the system enforces the constraint that the same "base" type is used for all arguments during function invocation.
"Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type. [...]" - the rest of that paragraph basically explains with many words that which I summarize above but without the concept of "base type" to ease comprehension.
In this case "date" is your base type so the valid combination of arguments is
(daterange, date[])
Your first invocation (daterange, daterange[]) works if you define your function as "(anyelement, anyarray)"; thus making "daterange" your base type when invoked that way.
The invocation (daterange, text[]) is not a valid combination for any pure pseudo-argument function.
David J.
Thanks a lot! Ok, the (daterange, date[]) invocation is working now with (anyelement, anyarray) param list. But now is working with eg. (date, date[]) invocation. I can't forbid it anyway? Balazs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Thanks a lot!
Ok, the (daterange, date[]) invocation is working now with (anyelement,
anyarray) param list.
I assume you meant (daterate, daterange[])
But now is working with eg. (date, date[]) invocation. I can't forbid it
anyway?
No. It also matches (text, text[]) and "(integer, integer[])". The best you can do is detect non-range values in the first argument and raise an exception. Or define explicitly typed functions for the variants you care about and drop the polymorphism.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Fri, Oct 13, 2017 at 12:45 PM, <balazs@obiserver.hu> wrote: >> CREATE FUNCTION range_overlap_array_any(anyrange, anyarray) RETURNS >> boolean >> AS $$SELECT false;$$ LANGUAGE sql IMMUTABLE STRICT; >> >> SELECT range_overlap_array_any(daterange('2017-01-01', '2017-04-01'), >> array['x'::text]); >> >> ERROR: function range_overlap_array_any(daterange, text[]) does not exist > The invocation (daterange, text[]) is not a valid combination for any pure > pseudo-argument function. Yeah. The expectation with these pseudotypes is that you're trying to declare a function that takes some set of arguments of related types. Back when we first invented the idea of polymorphic pseudotypes, which was a good long time ago now, there was discussion of having a second set of pseudotypes that are tied to a second underlying "base type" type variable, so that while myfunc(anyrange, anyarray) means "a range over some type, and an array over that same type" then you could write, say myfunc(anyrange, anyelement2, anyarray2) to mean "a range over some type A, and a value of some possibly-different type B, and an array over type B". We didn't do it because nobody had a particularly compelling use-case at the time, and also because if we needed 2 sets of pseudotypes then maybe we needed 3, etc; it wasn't clear where to stop. Nearly fifteen years later, we still haven't seen a compelling example for inventing a second set of pseudotypes. If you've got one it would definitely be interesting. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs