Thread: Re: Va: Va: [BUGS] Bug #519: Bug in order b y clausule

On Tue, 27 Nov 2001, Sandor Vig wrote:

>
> > This doesn't seem to entirely square with the results you two get,
> > but it sure does look like "CS" sorts in non-ASCII order in HU locale.
>
> I've tried to understand the "bug" and I think I have something for you:
>
> The Hungarian ABC (traditional old style) looks like:
> A,A',B,C,CS,D,E,E',F,G.......Q,R,S,T,TY,....
> (Yes, there are members with two characters!)
> This is how we learned it in the elementary school... But nowdays
> mainly the computers don't care about the two character members
> of our ABC this means, that there is no "CS","TY","DZ","DZS" etc...
> only "C" and "T" and so on...
>
> But let's say that the Red Hat uses the old style locale sort order,
> but int this case should the "CS" after the "C" standing, and the
> "TY" after the "T".

Actually I think CS would be after C* for any C? that doesn't have
a separate collation order since the additional characters don't
matter.  C<CS, CS<D: CT would be less than CS since C<CS, etc...
which would explain
CA
..
CY
CS
CZ
D
If CZ also has a special collation value (greater than CS presumably
but also less that D?)  Otherwise I'd expect
CY
CZ
CS
D


> So, it is still a mystery for me....
You probably need the locale for sorting the single character letters
but you don't want the collation values of the multiple character ones.
I think you're probably going to need to get an alternate locale
file but I'm not sure what's involved in that outside of postgres.
For postgres you'd need to dump, initdb under the new locale and restore
probably.

Hello All,

 Why not exists functions for convert any type to text?
 I can't find text(bool), text(numeric) ...
 May be it posiible in 7.2?

Victor

On Wed, 2001-11-28 at 11:24, Vicktor wrote:
>  Why not exists functions for convert any type to text?
>  I can't find text(bool), text(numeric) ...

use type casting:=20

select column::text from table

after :: comes the type you want.

Markus Bertheau

On 28 Nov 2001 12:00:44 +0100
Markus Bertheau <twanger@bluetwanger.de> wrote:

It's not work when column is bool or numeric type !

> On Wed, 2001-11-28 at 11:24, Vicktor wrote:
> >  Why not exists functions for convert any type to text?
> >  I can't find text(bool), text(numeric) ...
>
> use type casting:
>
> select column::text from table
>
> after :: comes the type you want.
>
> Markus Bertheau
>
>
>
>

Victor

On Wed, Nov 28, 2001 at 12:00:44PM +0100, Markus Bertheau wrote:
> On Wed, 2001-11-28 at 11:24, Vicktor wrote:
> >  Why not exists functions for convert any type to text?
> >  I can't find text(bool), text(numeric) ...
>
> use type casting:
>
> select column::text from table
>
> after :: comes the type you want.

 I mean original questioner has other problem:

t=# SELECT 't'::bool::text;
ERROR:  Cannot cast type 'boolean' to 'text'
test=# SELECT '123'::numeric::text;
ERROR:  Cannot cast type 'numeric' to 'text'
test=#

        Karel
--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

On Wed, 28 Nov 2001, Vicktor wrote:

> Hello All,
>
>  Why not exists functions for convert any type to text?
>  I can't find text(bool), text(numeric) ...
>  May be it posiible in 7.2?

Don't think so.  You need those functions and noone has written
them officially.

The former is easy, something like:

create function text(bool) returns text as 'select case when $1 then
''true'' else ''false'' end;' language 'sql';

should suffice.


I'm not entirely sure this is entirely safe for the latter, but you can
try :)

 create function numericout(numeric) returns int as 'numeric_out' language
'internal';
 create function text(numeric) returns text as 'select
textin(numericout($1));' language 'sql';

On Wed, 28 Nov 2001 08:35:00 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

 Ok, thank !

Vicktor

> On Wed, 28 Nov 2001, Vicktor wrote:
>
> > Hello All,
> >
> >  Why not exists functions for convert any type to text?
> >  I can't find text(bool), text(numeric) ...
> >  May be it posiible in 7.2?
>
> Don't think so.  You need those functions and noone has written
> them officially.
>
> The former is easy, something like:
>
> create function text(bool) returns text as 'select case when $1 then
> ''true'' else ''false'' end;' language 'sql';
>
> should suffice.
>
>
> I'm not entirely sure this is entirely safe for the latter, but you can
> try :)
>
>  create function numericout(numeric) returns int as 'numeric_out' language
> 'internal';
>  create function text(numeric) returns text as 'select
> textin(numericout($1));' language 'sql';
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Vicktor writes:

>  Why not exists functions for convert any type to text?
>  I can't find text(bool), text(numeric) ...

Just because *some* mapping between data types exists it doesn't mean it's
the sound, well-defined, be-all-end-all.  For instance, one might think
that TRUE => 't' and FALSE => 'f' is a reasonable bool=>text conversion,
but the first person you'll run into will claim that 1 and 0 is better,
and the second person will say that the answer should be locale-dependent.
So instead of bothering with this you get to make your own choice with

    CASE WHEN value THEN 'value if true' ELSE 'value if false' END

The issues are similar with numeric=>text, but the to_char() family of
functions should be able to solve all your problems in this area.

--
Peter Eisentraut   peter_e@gmx.net