Re: Type coercion on column in a query

Thanks Richard,

Do you know if there's a way to access the same method that Postgres 
uses for determining cast-ability?

I don't mind writing a new function in plperl.  I am concerned not so 
much about the difficulty of the numeric type, but as I move into other 
types, such as timestamp, I don't want to re-implement half-assedly the 
heuristics for date determination when those are already pretty 
bulletproof in Postgres.

Best,

Randall


On Wednesday, May 14, 2003, at 12:32 PM, Richard Huxton wrote:

> On Wednesday 14 May 2003 5:10 pm, Randall Lucas wrote:
>> Along the same lines, is it possible to tell whether a column may be
>> cast to a given type (e.g. numeric) without throwing an error?
>>
>> Specifically, I have some values that will probably be numbers but
>> might have some free text or other gobbledygook in there.  I want to
>> take the sum of all the numeric values, and maybe the average as well.
>> So what I envision is:
>>
>> table dirty_data (
>>       id                 int,
>>       dirty_numbers         text
>> );
>
>> select sum( safe_numeric_cast(dirty_numbers) ) from dirty_data where
>> safe_numeric_cast(dirty_numbers) is not null;
>
> You'd have to write "safe_numeric_cast()" in plperl, or possibly pltcl 
> (sorry,
> I don't use TCL). There's no exception handling in Postgresql (nor 
> will there
> be in the next few months).
>
> Alternatively, you could conceivably write a bunch of is_numeric(), 
> is_int()
> etc functions and test first.
>
> -- 
>   Richard Huxton
>