Re: Prevent characters not transposable to LATIN9

On Thu, Jul 01, 2010 at 04:53:51PM +0200, Arnaud Lesauvage wrote:
> Le 1/07/2010 16:48, Sam Mason a écrit :
>> How about using the built in character conversion routines. Something
>> like:
>>
>> col = convert_from(convert_to(col, 'LATIN9'),'LATIN9')
>>
>> as the check constraint, or its inverse as the where clause for the
>> erroneous rows?
>
> What happens then for a character that does not have an equivalent in
> LATIN9 ?
> If an error is raised in the check constraint, does it look like a
> normal check error ?

Yoik, didn't think about how it would actually handle the conversion!
It appears to throw an exception, so you probably want to bundle it up
in a pl/pgsql function that catches it and does the "right thing" for
you. Maybe something like:

CREATE FUNCTION isstringrepresentable(str TEXT, charset TEXT) RETURNS BOOLEAN AS $$
BEGIN
RETURN str = convert_from(convert_to(str, charset),charset);
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END
$$ LANGUAGE plpgsql IMMUTABLE;

I'm not sure which exception it should be catching, "OTHERS" is a catch
all which is a bit cheesy but should do the right thing most of the
time.

--
Sam http://samason.me.uk/