From: Richard Huxton <dev@archonet.com>
To: Randall Lucas <rlucas@tercent.net>
Cc: Kenny Mok <kenny@vis-ken.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Type coercion on column in a query
Date: Wed, 14 May 2003 19:01:51 +0100
Message-ID: <200305141901.51714.dev@archonet.com> (raw)
In-Reply-To: <ACED086A-862B-11D7-8D9A-000A957653D6@tercent.net>
References: <ACED086A-862B-11D7-8D9A-000A957653D6@tercent.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, rlucas@tercent.net, kenny@vis-ken.com
Subject: Re: Type coercion on column in a query
In-Reply-To: <200305141901.51714.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox