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
>