Обсуждение: Resolving polymorphic functions with related datatypes
I'm using the nvl() function from the orafce package. It is defined as a polymorphic function so its function signature is nvl(anyelement, anyelement) Now if I try to use the function in this very typical way nvl(numeric_col, 0) we get ERROR: function nvl(numeric, integer) does not exist The same error occurs if we have nvl(smallint, integer) etc This is a real shame 'cos polymorphic functions ought to be a great way of saving development time and catalog space, yet they seem to fall down a hole without implicit casting. What I'd like it to do is to recognise that the 0 should be cast implicitly to another datatype within the same family. I want and expectnvl(char_column, 0) to fail, but I expect the various numeric/integer types we have to play nicely together without tears. If we can do it for indexes, can we do it for polymorphic functions also when there is no matching function? -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
2008/7/3 Simon Riggs <simon@2ndquadrant.com>: > I'm using the nvl() function from the orafce package. It is defined as a > polymorphic function so its function signature is > nvl(anyelement, anyelement) > > Now if I try to use the function in this very typical way > nvl(numeric_col, 0) > > we get > > ERROR: function nvl(numeric, integer) does not exist > > The same error occurs if we have nvl(smallint, integer) etc > > This is a real shame 'cos polymorphic functions ought to be a great way > of saving development time and catalog space, yet they seem to fall down > a hole without implicit casting. > > What I'd like it to do is to recognise that the 0 should be cast > implicitly to another datatype within the same family. I want and expect > nvl(char_column, 0) > to fail, but I expect the various numeric/integer types we have to play > nicely together without tears. > > If we can do it for indexes, can we do it for polymorphic functions also > when there is no matching function? > +1 there is similar problem with literal constant. Pavel Stehule > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Thu, 2008-07-03 at 12:22 +0200, Pavel Stehule wrote: > 2008/7/3 Simon Riggs <simon@2ndquadrant.com>: > > I'm using the nvl() function from the orafce package. It is defined as a > > polymorphic function so its function signature is > > nvl(anyelement, anyelement) > > > > Now if I try to use the function in this very typical way > > nvl(numeric_col, 0) > > > > we get > > > > ERROR: function nvl(numeric, integer) does not exist > > > > The same error occurs if we have nvl(smallint, integer) etc > > > > This is a real shame 'cos polymorphic functions ought to be a great way > > of saving development time and catalog space, yet they seem to fall down > > a hole without implicit casting. > > > > What I'd like it to do is to recognise that the 0 should be cast > > implicitly to another datatype within the same family. I want and expect > > nvl(char_column, 0) > > to fail, but I expect the various numeric/integer types we have to play > > nicely together without tears. > > > > If we can do it for indexes, can we do it for polymorphic functions also > > when there is no matching function? > > > > +1 > > there is similar problem with literal constant. as well as NULL itself, which doesn't have a type when attempting to resolve to anyelement. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Simon Riggs wrote:
> I'm using the nvl() function from the orafce package. It is defined as a
> polymorphic function so its function signature is
> nvl(anyelement, anyelement)
>
> Now if I try to use the function in this very typical way
> nvl(numeric_col, 0)
>
> we get
>
> ERROR: function nvl(numeric, integer) does not exist
>
> The same error occurs if we have nvl(smallint, integer) etc
>
> This is a real shame 'cos polymorphic functions ought to be a great way
> of saving development time and catalog space, yet they seem to fall down
> a hole without implicit casting.
>
> What I'd like it to do is to recognise that the 0 should be cast
> implicitly to another datatype within the same family. I want and expect
> nvl(char_column, 0)
> to fail, but I expect the various numeric/integer types we have to play
> nicely together without tears.
So, it would be analogous to the 'unknown' type, but for numeric
literals instead of text literals. Seems reasonable. It still wouldn't
allow nvl(1::bigint, 2::int4), though, just as the unknown type doesn't
help with nvl('foo'::text, 'bar'::varchar).
> If we can do it for indexes, can we do it for polymorphic functions also
> when there is no matching function?
Umm, what do indexes have to do with this?
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Thu, 2008-07-03 at 14:11 +0300, Heikki Linnakangas wrote:
> > What I'd like it to do is to recognise that the 0 should be cast
> > implicitly to another datatype within the same family. I want and expect
> > nvl(char_column, 0)
> > to fail, but I expect the various numeric/integer types we have to play
> > nicely together without tears.
>
> So, it would be analogous to the 'unknown' type, but for numeric
> literals instead of text literals. Seems reasonable. It still wouldn't
> allow nvl(1::bigint, 2::int4), though, just as the unknown type doesn't
> help with nvl('foo'::text, 'bar'::varchar).
Well, it would be nice if we could work with the unknown type also, but
I don't expect that's meaningful.
Yetfunc(column_of_typeX, constant)
ought to be able to resolve correctly when
* no function exists with signaturefunc(typeX, typeY)
* yet there exists a functionfunc(anyelement, anyelement)
* and an implicit cast exists typeY => typeX
(assuming constant is normally resolved to typeY)
> > If we can do it for indexes, can we do it for polymorphic functions also
> > when there is no matching function?
>
> Umm, what do indexes have to do with this?
Nothing, except that we solved implicit casting for that situation, so
perhaps it is possible for this situation...
Anyway, just posting for reference. Workarounds exist, just wanted to
make sure the issue was mentioned.
-- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Thu, 2008-07-03 at 14:11 +0300, Heikki Linnakangas wrote:
>
>> > What I'd like it to do is to recognise that the 0 should be cast
>> > implicitly to another datatype within the same family. I want and expect
>> > nvl(char_column, 0)
>> > to fail, but I expect the various numeric/integer types we have to play
>> > nicely together without tears.
>>
>> So, it would be analogous to the 'unknown' type, but for numeric
>> literals instead of text literals. Seems reasonable. It still wouldn't
>> allow nvl(1::bigint, 2::int4), though, just as the unknown type doesn't
>> help with nvl('foo'::text, 'bar'::varchar).
>
> Well, it would be nice if we could work with the unknown type also, but
> I don't expect that's meaningful.
Postgres's way of spelling constants of unknown type is to put them in single
quotes. That is, 'foo' isn't a character string in Postgres, it's *any* kind
of constant with an unknown type. So this would work:
nvl(numeric_column, '0')
I think what you're suggesting is making integer and floating point constants
like 0 and 0.1 be treated as "unknown" or perhaps a different kind of unknown,
"unknown integral type" and "unknown numeric type".
Personally I think the way it works now is weird too, but it's been that way
forever and changing it would be a pretty massive behaviour change.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
On Thu, 2008-07-03 at 13:54 +0100, Gregory Stark wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
>
> > On Thu, 2008-07-03 at 14:11 +0300, Heikki Linnakangas wrote:
> >
> >> > What I'd like it to do is to recognise that the 0 should be cast
> >> > implicitly to another datatype within the same family. I want and expect
> >> > nvl(char_column, 0)
> >> > to fail, but I expect the various numeric/integer types we have to play
> >> > nicely together without tears.
> >>
> >> So, it would be analogous to the 'unknown' type, but for numeric
> >> literals instead of text literals. Seems reasonable. It still wouldn't
> >> allow nvl(1::bigint, 2::int4), though, just as the unknown type doesn't
> >> help with nvl('foo'::text, 'bar'::varchar).
> >
> > Well, it would be nice if we could work with the unknown type also, but
> > I don't expect that's meaningful.
>
> Postgres's way of spelling constants of unknown type is to put them in single
> quotes. That is, 'foo' isn't a character string in Postgres, it's *any* kind
> of constant with an unknown type. So this would work:
>
> nvl(numeric_column, '0')
>
> I think what you're suggesting is making integer and floating point constants
> like 0 and 0.1 be treated as "unknown" or perhaps a different kind of unknown,
> "unknown integral type" and "unknown numeric type".
>
> Personally I think the way it works now is weird too, but it's been that way
> forever and changing it would be a pretty massive behaviour change.
Well, I can workaround the problem, it just seems like there shouldn't
be one.
I'm OK with massive behaviour change (like 8.3) as long as its a
controllable option.
By far the biggest behaviour change is to get the rest of the world to
work the way we do. People write (and *have written*) SQL that doesn't
work this way.
-- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Gregory Stark <stark@enterprisedb.com> writes:
> I think what you're suggesting is making integer and floating point constants
> like 0 and 0.1 be treated as "unknown" or perhaps a different kind of unknown,
> "unknown integral type" and "unknown numeric type".
No, that would be a pretty dangerous way to go about it, because it
would have side-effects on all sorts of queries whether or not they
made any use of polymorphic functions. Plus, it would only fix the
issue for numeric-group types, but the same thing would come up if
you had, say, NVL(text, varchar).
What I'd be inclined to think about is making
check_generic_type_consistency and related functions allow the
arguments matched to ANYELEMENT to be of different actual types
so long as select_common_type could determine a unique type to
coerce them all to. It'd take some refactoring (notably, because
select_common_type wants to throw error on failure, and because
there'd have to be a way to pass back the type that was selected
for use later).
regards, tom lane
> > What I'd be inclined to think about is making > check_generic_type_consistency and related functions allow the > arguments matched to ANYELEMENT to be of different actual types > so long as select_common_type could determine a unique type to > coerce them all to. It'd take some refactoring (notably, because > select_common_type wants to throw error on failure, and because > there'd have to be a way to pass back the type that was selected > for use later). +1 it's same like current implementation coalesce, least, greatest functions, thats works well. And with this change and with variatic functions we can move these functions from parser. Regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >