Обсуждение: Casts question
Hi all,
I have defined a datatype called "varcharci", shamelessly yanking the
input, output, recv and send functions from varchar. This means (as far
as I understand things) that this type is binary compatible with varchar.
As such, I used the following two lines:
create cast ( varcharci AS varchar ) WITHOUT FUNCTION AS IMPLICIT;
create cast ( varchar AS varcharci ) WITHOUT FUNCTION AS ASSIGNMENT;
I defined two tables. Both have a column called "name". One is a
varchar, and the other is a varcharci. When I try to do the following
select, I get an error:
test=# select test2.id as "id-1", test3.id as "id-2", test2.name from
test2 inner join test3 on test2.name=test3.name;
ERROR: operator does not exist: character varying = varcharci
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
When I add an explicit cast, everything works:
sun=# select test2.id as "id-1", test3.id as "id-2", test2.name from
test2 inner join test3 on test2.name=cast(test3.name as varchar);
results go here
Why is that? Being as it is that no operator = is defined for varcharci,
and that the cast from varchar to varcharci is "as assignment" anyways,
shouldn't postgres be able to do the cast implicitly?
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/
Shachar Shemesh <psql@shemesh.biz> writes:
> I have defined a datatype called "varcharci", shamelessly yanking the
> input, output, recv and send functions from varchar. This means (as far
> as I understand things) that this type is binary compatible with varchar.
Use text, not varchar.
> Why is that? Being as it is that no operator = is defined for varcharci,
> and that the cast from varchar to varcharci is "as assignment" anyways,
> shouldn't postgres be able to do the cast implicitly?
Yes, it can cast to varchar, but that doesn't help because there are no
varchar operators ;-). To resolve the operator, it has to promote both
sides to text, and you didn't offer a cast to text.
regards, tom lane
Tom Lane wrote:
>Shachar Shemesh <psql@shemesh.biz> writes:
>
>
>>I have defined a datatype called "varcharci", shamelessly yanking the
>>input, output, recv and send functions from varchar. This means (as far
>>as I understand things) that this type is binary compatible with varchar.
>>
>>
>
>Use text, not varchar.
>
>
>
>>Why is that? Being as it is that no operator = is defined for varcharci,
>>and that the cast from varchar to varcharci is "as assignment" anyways,
>>shouldn't postgres be able to do the cast implicitly?
>>
>>
>
>Yes, it can cast to varchar, but that doesn't help because there are no
>varchar operators ;-). To resolve the operator, it has to promote both
>sides to text, and you didn't offer a cast to text.
>
> regards, tom lane
>
>
>
I don't get it. The cast from varchar to text is using a "no function"
cast - i.e. - they are binary compatible. And yet, there are two
seperate functions for receiving from text and from binary
representation. Why not use the same function?
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/
Shachar Shemesh <psql@shemesh.biz> writes:
> Tom Lane wrote:
>> Yes, it can cast to varchar, but that doesn't help because there are no
>> varchar operators ;-). To resolve the operator, it has to promote both
>> sides to text, and you didn't offer a cast to text.
>>
> I don't get it.
When we look to see whether we can cast from type X to type Y, we look
to see whether there is a pg_cast entry from type X to type Y. We do
not look to see if we could get there by casting X to some other type Z
and thence to Y (much less more-than-2-step sequences). There are a
number of good reasons for this, but I'll just mention speed and
surprise factors. Doing so would make for an exponential increase in
the number of potential cast paths, thereby probably paralyzing the
operator selection code instead of making it more useful. (Too many
alternatives are as bad as none.)
regards, tom lane
Tom Lane wrote:
>Shachar Shemesh <psql@shemesh.biz> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>Yes, it can cast to varchar, but that doesn't help because there are no
>>>varchar operators ;-). To resolve the operator, it has to promote both
>>>sides to text, and you didn't offer a cast to text.
>>>
>>>
>>>
>>I don't get it.
>>
>>
>
>When we look to see whether we can cast from type X to type Y, we look
>to see whether there is a pg_cast entry from type X to type Y. We do
>not look to see if we could get there by casting X to some other type Z
>and thence to Y (much less more-than-2-step sequences). There are a
>number of good reasons for this, but I'll just mention speed and
>surprise factors. Doing so would make for an exponential increase in
>the number of potential cast paths, thereby probably paralyzing the
>operator selection code instead of making it more useful. (Too many
>alternatives are as bad as none.)
>
> regards, tom lane
>
>
That I got.
What I don't understand is this. The cast from varchar to text is a
no-function one. I.e. - they are defined to be memory-represented the
same. If that is the case, one would expect them to also share the input
and ouput functions. When looking at the catalog, however, it appears
that textin is used to input text, while varcharin is used for varchars.
Did I miss anything here?
It gets wierder. The binary receive function for varchar (varcharrecv)
is different than the one for text (textrecv), but the C implementation
of the former simply calls the C implementation of the later. Why not
define them to be the same at the SQL level?
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/
Shachar Shemesh <psql@shemesh.biz> writes:
> What I don't understand is this. The cast from varchar to text is a
> no-function one. I.e. - they are defined to be memory-represented the
> same. If that is the case, one would expect them to also share the input
> and ouput functions. When looking at the catalog, however, it appears
> that textin is used to input text, while varcharin is used for varchars.
Sure. Identical internal representation does not necessarily imply
identical functionality. varcharin needs to enforce a maximum length
(and now that I think of it, varcharrecv does too; that's an oversight).
Another example is that abstime is binary-compatible to integer. Should
they have the same input function?
> It gets wierder. The binary receive function for varchar (varcharrecv)
> is different than the one for text (textrecv), but the C implementation
> of the former simply calls the C implementation of the later. Why not
> define them to be the same at the SQL level?
So we can fix bugs like the one above without forcing initdb.
regards, tom lane