Re: How to check whether a data type can be cast to another

On Fri, Oct 23, 2009 at 4:38 AM, Kareem Sedki <isiscreation(at)gmail(dot)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