Prevent characters not transposable to LATIN9

Le 30/06/2010 2:42, Howard Rogers a �crit :

Something I do in Oracle: do a TRANSLATE on whatever string is being
supplied, converting matching characters to spaces, and measure the length.
If the length is greater than zero, your supplied string has something in it
you're not expecting, at which point you can intervene and warn your users
with some sort of error message. For example:

Hi Howard, sorry for the late reply.
Yes, that could work indeed.
I did not want to take the hassle of having to list all acceptable
characters, but that might be the only way.

Thanks for the hint !

On Tue, Jun 29, 2010 at 04:52:22PM +0200, Arnaud Lesauvage wrote:

We have a database in UTF8, from which we have to export text files in
LATIN9 encoding (or WIN1252, which is almostthe same I believe).

Records are entered via MSAccess forms (on psqlodbc-linked tables).
The problem is that some of the characters input by the users have no
equivalent in LATIN9.

How could I easily write a CONSTRAINT (or RULE) that would check that
everything entered in the fields have an equivalent in my specific
destination encoding ?

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?

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

Le 1/07/2010 16:48, Sam Mason a �crit :

On Tue, Jun 29, 2010 at 04:52:22PM +0200, Arnaud Lesauvage wrote:

We have a database in UTF8, from which we have to export text files in
LATIN9 encoding (or WIN1252, which is almostthe same I believe).

Records are entered via MSAccess forms (on psqlodbc-linked tables).
The problem is that some of the characters input by the users have no
equivalent in LATIN9.

How could I easily write a CONSTRAINT (or RULE) that would check that
everything entered in the fields have an equivalent in my specific
destination encoding ?

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 ?

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/

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.