I have made lpad/rpad/btrim/ltrim/rtirm/translate functions multibyte
aware. I think we could mark following item as "done".

* Make functions more multi-byte aware, e.g. trim()

Anything I forgot to make multibyte aware?
--
Tatsuo Ishii

Sun, 23 Sep 2001 20:11:08 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
mutibyte aware functions
> I have made lpad/rpad/btrim/ltrim/rtirm/translate functions multibyte
> aware. I think we could mark following item as "done".
> 
> * Make functions more multi-byte aware, e.g. trim()

Done.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 23 Sep 2001 09:55:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: mutibyte aware functions
Hello,

I have set up a UNICODE database in PostgreSQL 7.1.2 and use psql for 
querying (\ENCODING > UNICODE).
To perform tests, I downloaded code charts from http://www.unicode.org/charts/

1) UTF-8
http://www.postgresql.org/idocs/index.php?app-psql.html explains
"Anything contained in single quotes is furthermore subject to C-like 
substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits 
(the character with the given decimal, octal, or hexadecimal code)."

To start, I would like to store/display a simple 'A' letter in psql, number 
0041, with the following queries:

 > 'INSERT INTO TABLE table_name VALUES (column-name) VALUES ( ' \0041' );
    and then SELECT * FROM table_name. It does not work.
 > Or simply SELECT '\0041'; which does not return 'A'.

Do I miss something?

2) Japanese coding
Do you recommend EUC_JP or UNICODE for storing Japanese text in PostgreSQL?
This is for use in PHP (both for input and display, no recode needed).

3) Is there a way to query available encodings in PostgreSQL for display in 
pgAdmin.
Is it a planned feature in PostgreSQL 7.2? This would be nice if it existed.
Example: function pg_available_encodings -> SQL-ASCII;UNICODE;EUC-JP etc...

Thank you in advance,
Jean-Michel POURE
pgAdmin Team
http://pgadmin.postgresql.org

Sun, 23 Sep 2001 16:18:36 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
UTF-8 support
> 1) UTF-8
> http://www.postgresql.org/idocs/index.php?app-psql.html explains
> "Anything contained in single quotes is furthermore subject to C-like 
> substitutions for \n (new line), \t (tab), \digits, \0digits, and \0xdigits 
> (the character with the given decimal, octal, or hexadecimal code)."
> 
> To start, I would like to store/display a simple 'A' letter in psql, number 
> 0041, with the following queries:
> 
>  > 'INSERT INTO TABLE table_name VALUES (column-name) VALUES ( ' \0041' );
>     and then SELECT * FROM table_name. It does not work.
>  > Or simply SELECT '\0041'; which does not return 'A'.

Try:

INSERT INTO TABLE table_name VALUES (column-name) VALUES ( ' \101' );

I don't know why the docs claim so, '\OCTAL_NUMBER' seems to work
anyway.

BTW, 'A' is not 041 in octal, it is 101.

> 2) Japanese coding
> Do you recommend EUC_JP or UNICODE for storing Japanese text in PostgreSQL?
> This is for use in PHP (both for input and display, no recode needed).

If you are going to use Japanese only, EUC_JP will take less storage
space. So, in general EUC_JP is recommended.

> 3) Is there a way to query available encodings in PostgreSQL for display in 
> pgAdmin.
> Is it a planned feature in PostgreSQL 7.2? This would be nice if it existed.
> Example: function pg_available_encodings -> SQL-ASCII;UNICODE;EUC-JP etc...

Currently no. But it would be easy to implement such a function. What
comes in mind is:

pg_available_encodings([INTEGER how]) RETURNS setof TEXT

where how is

      0(or omitted): returns all available encodings
      1: returns encodings in backend
      2: returns encodings in frontend

Comments?
--
Tatsuo Ishii

Mon, 24 Sep 2001 08:58:22 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 support
----- Original Message ----- 
From: Tatsuo Ishii <t-ishii@sra.co.jp>
Sent: Sunday, September 23, 2001 7:58 PM

> > 3) Is there a way to query available encodings in PostgreSQL for display in 
> > pgAdmin.
> > Is it a planned feature in PostgreSQL 7.2? This would be nice if it existed.
> > Example: function pg_available_encodings -> SQL-ASCII;UNICODE;EUC-JP etc...
> 
> Currently no. But it would be easy to implement such a function. What
> comes in mind is:
> 
> pg_available_encodings([INTEGER how]) RETURNS setof TEXT
> 
> where how is
> 
>       0(or omitted): returns all available encodings
>       1: returns encodings in backend
>       2: returns encodings in frontend
> 
> Comments?

        3: returns encodings of both backend and frontend

Why both? To compare and match upon the need.
If by 0 (ALL) you meant the same, then please ignore my comment.

My question is now how many BE's/FE's would you return encodings for?

S.


Sun, 23 Sep 2001 22:15:26 -0400
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: UTF-8 support
> > pg_available_encodings([INTEGER how]) RETURNS setof TEXT
> > 
> > where how is
> > 
> >       0(or omitted): returns all available encodings
> >       1: returns encodings in backend
> >       2: returns encodings in frontend
> > 
> > Comments?
> 
>         3: returns encodings of both backend and frontend
> 
> Why both? To compare and match upon the need.
> If by 0 (ALL) you meant the same, then please ignore my comment.

You are correct. We don't need how=3.

> My question is now how many BE's/FE's would you return encodings for?

I don't quite understand your question. What I thought were something
like this:

SELECT pg_available_encodings();
pg_available_encodings
----------------------
SQL_ASCII
EUC_JP
EUC_CN
EUC_KR
EUC_TW
UNICODE
MULE_INTERNAL
LATIN1
LATIN2
LATIN3
LATIN4
LATIN5
KOI8
WIN
ALT
SJIS
BIG5
WIN1250

BTW, another question comes to my mind. Why don't we make available
this kind of information by ordinaly tables or views, rather than by
functions?  It would be more flexible and easy to use.
--
Tatsuo Ishii

Mon, 24 Sep 2001 11:47:31 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 support
----- Original Message ----- 
From: Tatsuo Ishii <t-ishii@sra.co.jp>
Sent: Sunday, September 23, 2001 10:47 PM

> > My question is now how many BE's/FE's would you return encodings for?
> 
> I don't quite understand your question. What I thought were something
> like this:
> 
> SELECT pg_available_encodings();
> pg_available_encodings
> ----------------------
> SQL_ASCII
> EUC_JP
> EUC_CN
> EUC_KR
> EUC_TW
> UNICODE
> MULE_INTERNAL
> LATIN1
> LATIN2
> LATIN3
> LATIN4
> LATIN5
> KOI8
> WIN
> ALT
> SJIS
> BIG5
> WIN1250

Which ones belong to the backend and which ones to the frontend?
Or even more: which ones belong to the backend, which ones
to the frontend #1, which ones to the frontend #2, etc...

For examle, I have two fronends:

FE1: UNICODE,  WIN1251
FE2: KOI8, UNICODE
BE: UNICODE, LATIN1, ALT

Which ones SELECT pg_available_encodings(); will show?
The ones of the BE and the FE making the request?

In case I need to communicate with BE using one common
encoding between the two if it is available.

> BTW, another question comes to my mind. Why don't we make available
> this kind of information by ordinaly tables or views, rather than by
> functions?  It would be more flexible and easy to use.

Sounds like a good idea, make another system table for encodings
and NLS stuff...

S.


Sun, 23 Sep 2001 23:05:17 -0400
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: UTF-8 support
Jean-Michel POURE wrote:
> 
> 3) Is there a way to query available encodings in PostgreSQL for display in
> pgAdmin.

Could pgAdmin display multibyte chars in the first place ?

regards,
Hiroshi Inoue

Mon, 24 Sep 2001 14:25:49 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: [ODBC] UTF-8 support
> Which ones belong to the backend and which ones to the frontend?
> Or even more: which ones belong to the backend, which ones
> to the frontend #1, which ones to the frontend #2, etc...
> 
> For examle, I have two fronends:
> 
> FE1: UNICODE,  WIN1251
> FE2: KOI8, UNICODE
> BE: UNICODE, LATIN1, ALT
> 
> Which ones SELECT pg_available_encodings(); will show?
> The ones of the BE and the FE making the request?
> 
> In case I need to communicate with BE using one common
> encoding between the two if it is available.

I'm confused.

What do you mean by BE? BE's encoding is determined by the database
that FE chooses. If you just want to know what kind encodings are
there in the database, why not use:

SELECT DISTINCT ON (encoding) pg_encoding_to_char(encoding) AS
encoding FROM pg_database;

Also, FE's encoding could be any valid encoding that FE chooses,
i.e. it' not BE's choice.

Can you show me more concrete examples showing what you actually want
to do?

>> 3) Is there a way to query available encodings in PostgreSQL for display in
>> pgAdmin.
>
> Could pgAdmin display multibyte chars in the first place ?

Wao. If pgAdmin could not display multibyte chars, all discussions
here are meaningless:-<
--
Tatsuo Ishii

Mon, 24 Sep 2001 16:12:59 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 support
Hello Tatsuo & all,

First of all, I would like to thank all the ODBC team for their work.
I have been using various databases quite extensively and PostgreSQL ranks 
among the best for ODBC support.

>Try:
>INSERT INTO TABLE table_name VALUES (column-name) VALUES ( ' \101' );
>I don't know why the docs claim so, '\OCTAL_NUMBER' seems to work
>anyway.BTW, 'A' is not 041 in octal, it is 101.

Would it be possible to use the hexadecimal \u000 notation in 7.2? Is there 
a built-in function to convert hexadecimal into octal?
This would be an interesting feature as it seems to be a standard notation 
for UNICODE values (http://www.unicode.org/charts/).
Also, Java has the ability to display UNICODE using \u000 notation. I think 
it is the same with PHP although I am not sure.

> > 2) Japanese coding
> > Do you recommend EUC_JP or UNICODE for storing Japanese text in PostgreSQL?
> > This is for use in PHP (both for input and display, no recode needed).
>
>If you are going to use Japanese only, EUC_JP will take less storage
>space. So, in general EUC_JP is recommended.

Are some Japanese fonts designed to work only for EUC-JP or UNICODE?
Can all Japanese fonts be mapped from EUC-JP to UNICODE and conversly?

> > Can you show me more concrete examples showing what you actually want 
> to do?
Yes, I am very interested in testing double-byte display in pgAdmin II.
pgAdmin I and II are developed in Visual Basic which supports double-byte 
Unicode forms since SP4.

How about ODBC double-byte support?
Is any translation performed between back-end and front-end?
Are there special ODBC settings to display Japanese?
Are there limitations due to Windows (95/98/NT)?
I heard 95 Japanese support was catastrophic.

Presently, I have to store Japanese text in PostgreSQL 7.1 with PHP.
I will do my best to display Japanese text in pgAdmin II.

Greetings from Paris,
Jean-Michel POURE

Mon, 24 Sep 2001 10:53:31 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [HACKERS] UTF-8 support
Jean-Michel POURE wrote:
> 
> Hello Tatsuo & all,
> 
> 
> How about ODBC double-byte support?

If you use 'EUC_xx' or 'UTF-8' as the client encoding
you don't need the multibyte version of psqlodbc driver
but if you use 'SJIS' or 'BIG5' you need the multibyte
version introduced by Eiji Tokuya.

> Is any translation performed between back-end and front-end?

You have to set the connect settings Datasource option like
  set client_encoding to 'SJIS'
if you use the different client encoding from that of
server. psqlodbc driver issues above command immediately
after the connection was successful and could use conversion
functionality of PostgreSQL backends.

> Are there special ODBC settings to display Japanese?

pgAdmin.exe doesn't display Japanese characters here
but I can see Japanese characters using pgAdmin source.
There seems to be .. e.g. font problem ...

> Are there limitations due to Windows (95/98/NT)?
> I heard 95 Japanese support was catastrophic.

I've ever used win9X little and don't know the
details.

regards,
Hiroshi Inoue

Tue, 25 Sep 2001 09:41:27 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: [HACKERS] UTF-8 support
Hello,

Are there built-in functions to convert UTF-8 string values into 
hexadecimal \uxxxx and octal values and conversely?
If yes, can I parse any UTF-8 string safely with PL/pgSQL to return \uxxxx 
and octal values?

Best regards,
Jean-Michel POURE


Tue, 25 Sep 2001 09:25:14 +0200
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [ODBC] UTF-8 support
----- Original Message ----- 
From: Tatsuo Ishii <t-ishii@sra.co.jp>
Sent: Monday, September 24, 2001 3:12 AM

> > Which ones belong to the backend and which ones to the frontend?
> > Or even more: which ones belong to the backend, which ones
> > to the frontend #1, which ones to the frontend #2, etc...
> > 
> > For examle, I have two fronends:
> > 
> > FE1: UNICODE,  WIN1251
> > FE2: KOI8, UNICODE
> > BE: UNICODE, LATIN1, ALT
> > 
> > Which ones SELECT pg_available_encodings(); will show?
> > The ones of the BE and the FE making the request?
> > 
> > In case I need to communicate with BE using one common
> > encoding between the two if it is available.
> 
> I'm confused.

Sorry, I was confused myself about how the mechanics of
of it works and confused you. :)

> What do you mean by BE? BE's encoding is determined by the database
> that FE chooses. If you just want to know what kind encodings are
> there in the database, why not use:
> 
> SELECT DISTINCT ON (encoding) pg_encoding_to_char(encoding) AS
> encoding FROM pg_database;
> 
> Also, FE's encoding could be any valid encoding that FE chooses,
> i.e. it' not BE's choice.

True. I gotta look at that.

> Can you show me more concrete examples showing what you actually want
> to do?

Once I have them completed and when per-columnt encoding support
will be available. Basically, it's gonna be
one BE supporting various encodings, and different kinds of clients
connecting to it, including Windows client as well as Linux. The database
will have text desriptions of some things in various languages,
(for now only languages I can communicate on: English, Spanish, French and
Russian, but they gonna be more in the future), and it would be nice to
know in advance what encoding is used in so appropriate conversion
is done before messages are retunred to clients, so I don't loose accents
like in French or Spanish or they don't get converted to some cyrillic
characters at the FE side (French accented characters tend to do that).

Anyway, when it gets to more concrete details and the project
becomes more tangible, I might come back with my questions :)

> >> 3) Is there a way to query available encodings in PostgreSQL for display in
> >> pgAdmin.
> >
> > Could pgAdmin display multibyte chars in the first place ?
> 
> Wao. If pgAdmin could not display multibyte chars, all discussions
> here are meaningless:-<

The discussion aren't meaningless here,
and the pgAdmin team is working now on pgAdmin II,
which I hope will support multibyte characters.

--
S.


Sun, 7 Oct 2001 14:41:37 -0400
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: UTF-8 support