Prev: Attaching and using the Postgres shared memory segment
Next: Command execution
From: Simon Riggs on 3 Jul 2008 06:51

On Thu, 2008-07-03 at 12:22 +0200, Pavel Stehule wrote:
> 2008/7/3 Simon Riggs <simon(a)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.com
PostgreSQL Training, Services and Support


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Simon Riggs on 3 Jul 2008 08:34

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.

Yet
func(column_of_typeX, constant)
ought to be able to resolve correctly when
* no function exists with signature
func(typeX, typeY)
* yet there exists a function
func(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.com
PostgreSQL Training, Services and Support


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Gregory Stark on 3 Jul 2008 08:54
"Simon Riggs" <simon(a)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!

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Simon Riggs on 3 Jul 2008 09:24

On Thu, 2008-07-03 at 13:54 +0100, Gregory Stark wrote:
> "Simon Riggs" <simon(a)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.com
PostgreSQL Training, Services and Support


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 3 Jul 2008 12:01
Gregory Stark <stark(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 |  Next  |  Last
Pages: 1 2
Prev: Attaching and using the Postgres shared memory segment
Next: Command execution