Re: Prevent characters not transposable to LATIN9

Le 1/07/2010 17:12, Sam Mason a écrit :
> 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;

Yep, I'll give it a try as soon as I find some time !

Thanks for the hint.