Re: Type coercion on column in a query

On Wednesday 14 May 2003 5:46 pm, Randall Lucas wrote:
> 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 *think* so (bear in mind I'm not a developer, just a user). My
understanding is that PG does the following:
1. Identify types involved
2. Look up the function required to convert
3. Execute the function

It's the function itself that raises an error in the case of say
text=>timestamp where the text contains garbage. I don't think there's much
the developers can do about this - if you want to be able to add new types
simply, they need to be fairly self contained.

Now it shouldn't be beyond the wit of man to be able to produce an
is_castable(value, destination_type) function that calls these conversion
functions and traps the error. In this particular case there shouldn't* be
any side effects to worry about. However, that's probably more work than
you're interested in.

> 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.

I think you're out of luck here. The "proper" way to do this is:
1. try to insert values into columns of correct type
2. when it fails, handle error in application

* - that's shouldn't as in I can't think of a good reason why right now
--
Richard Huxton