Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Randall Lucas <rlucas@tercent.net>
Subject: Re: Type coercion on column in a query
Date: Wed, 14 May 2003 19:01:51 +0100
User-Agent: KMail/1.4.3
Cc: "Kenny Mok" <kenny@vis-ken.com>, <pgsql-sql@postgresql.org>
References: <ACED086A-862B-11D7-8D9A-000A957653D6@tercent.net>
In-Reply-To: <ACED086A-862B-11D7-8D9A-000A957653D6@tercent.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200305141901.51714.dev@archonet.com>

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=20
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=20
text=3D>timestamp where the text contains garbage. I don't think there's mu=
ch=20
the developers can do about this - if you want to be able to add new types=
=20
simply, they need to be fairly self contained.=20

Now it shouldn't be beyond the wit of man to be able to produce an=20
is_castable(value, destination_type) function that calls these conversion=
=20
functions and traps the error.  In this particular case there shouldn't* be=
=20
any side effects to worry about. However, that's probably more work than=20
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=20
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
--=20
  Richard Huxton