Обсуждение: How to check whether a data type can be cast to another

On Fri, Oct 23, 2009 at 4:38 AM, Kareem Sedki <isiscreation@gmail.com> wrote:
> Hello all,
>
> I am trying to write a function that takes one source and one target data
> type identifiers, each as a 'text' variable, as arguments and return true if
> the cast can be made, false otherwise.
>
> I consulted the pg_cast and pg_type tables, however, I found no direct casts
> from 'int4' to 'text'. Even though, you SELECT CAST(4::int4 AS text) The
> possibility of indirect casts that use multiple intermediate casts is slim -
> to me. Is an 'int4' cast to 'text' by means of multiple intermediate casts
> from 'int4' to 'char' and eventually to 'text'?
>
> I would prefer to have a function written in PG/PLSQL for now as we are
> prototyping the system.  Could someone help me write such a function,
> please?

Well, there is always the 'parking in new york' method.  See, in the
big apple while parallel parking, people back up until they hit the
car behind them in order to determine when to move forward.  So, you
could implement a similar method by attempting a cast and using
exception handler to catch the cases that don't work :).  Since casts
are pretty stable it should be quite possible to rig a cache around
this mechanism.

That is, unless someone else can suggest a better way...

merlin

Kareem Sedki <isiscreation@gmail.com> writes:
> I am trying to write a function that takes one source and one target
> data type identifiers, each as a '/text/' variable, as arguments and
> return true if the cast can be made, false otherwise.

I think this is fundamentally the wrong approach, because it ignores the
question of whether a specific value can be cast.  For example, there is
a cast from int4 to int2, but that doesn't mean that 999999::int2 will
succeed.  What I think you should do is just try the cast and catch any
error that occurs.

If you really want to try to do what you're saying, look into
parser/parse_coerce.c.  In particular, can_coerce_type() is the
authoritative answer.

            regards, tom lane

Kareem Sedki <isiscreation@gmail.com> writes: 

I am trying to write a function that takes one source and one target
data type identifiers, each as a '/text/' variable, as arguments and
return true if the cast can be made, false otherwise.   

I think this is fundamentally the wrong approach, because it ignores the
question of whether a specific value can be cast.  For example, there is
a cast from int4 to int2, but that doesn't mean that 999999::int2 will
succeed.  What I think you should do is just try the cast and catch any
error that occurs.

If you really want to try to do what you're saying, look into
parser/parse_coerce.c.  In particular, can_coerce_type() is the
authoritative answer.
		regards, tom lane 

Kareem Sedki <isiscreation@gmail.com> writes:
> The problem I faced is that the type 'any' is not permitted in
> procedural languages. If it were permitted, then we could have a
> function like is_castable( value ANY, target_type TEXT) and then we
> would catch exceptions if they are thrown.

ANYELEMENT maybe?

> Here is what we have, there is a field in which we should store
> different data types and another field in the same row to store the
> identifier of the original data type of the first field. The first field
> should be polymorphic.

Although actually I think you should stop right here and rethink what
you are doing.  It sounds a whole lot to me like you are trying to build
an EAV store, and that seldom leads to anything good.  It is much better
to spend the necessary effort up-front to work out a concrete
fully-typed schema for your database.

            regards, tom lane

Kareem Sedki escribió:
> Thank you Merlin and Tom. I appreciate your answers.
>
> I have tried to follow that approach before I go this way. So, I checked
> the appendix  of the documentation for error conditions. Searching for
> cast-related conditions, I found INVALID_CHARACTER_VALUE_FOR_CAST. Would
> that be the exception to catch? It doesn't sound right.

TIAS -- http://www.fetter.org/tias.html

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Oct 23, 2009 at 11:14 AM, Kareem Sedki <isiscreation@gmail.com> wrote:
> Thank you Merlin and Tom. I appreciate your answers.
>
> I have tried to follow that approach before I go this way. So, I checked the
> appendix  of the documentation for error conditions. Searching for
> cast-related conditions, I found INVALID_CHARACTER_VALUE_FOR_CAST. Would
> that be the exception to catch? It doesn't sound right.
>
> The problem I faced is that the type 'any' is not permitted in procedural
> languages. If it were permitted, then we could have a function like
> is_castable( value ANY, target_type TEXT) and then we would catch exceptions
> if they are thrown. We didn't want to begin working on C functions before
> the prototype is complete.  However, if this is the only way, C is the way
> to go for now then.
>
> If we can, programmatically, figure out whether a type can be cast to
> another, we can then check each type's limits or valid values to consider
> the valid.
>
> Here is what we have, there is a field in which we should store different
> data types and another field in the same row to store the identifier of the
> original data type of the first field. The first field should be
> polymorphic. Since I couldn't find how to make a field polymorphic, I
> resorted to a 'text' field so that different data types can be stored in the
> field after being cast to 'text'. When retrieving a field, its original
> state should be cast back from 'text'.
>
> Do you think of a better strategy?

I think Tom is probably giving you the right advice here.   However,
if you decide to implement something around my 'catch the error'
approach (i was half joking when I suggested it), it should be a
simple matter to test a few and print out (raise notice) the sqlstate
variable inside the exception handler.

merlin

Kareem Sedki <isiscreation@gmail.com> writes: 

The problem I faced is that the type 'any' is not permitted in
procedural languages. If it were permitted, then we could have a
function like is_castable( value ANY, target_type TEXT) and then we
would catch exceptions if they are thrown.   

ANYELEMENT maybe?
 

Here is what we have, there is a field in which we should store
different data types and another field in the same row to store the
identifier of the original data type of the first field. The first field
should be polymorphic.   

Although actually I think you should stop right here and rethink what
you are doing.  It sounds a whole lot to me like you are trying to build
an EAV store, and that seldom leads to anything good.  It is much better
to spend the necessary effort up-front to work out a concrete
fully-typed schema for your database.
		regards, tom lane