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.