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/