Hi,

Can you tell me trim() spec, please ? (This problem has been 
discussed in pgsql-jp ML. )

In trim(trailing 'abc' from '123cbabc') function, 'abc' means
~'[abc]'. 

pgbash> select trim(trailing 'abc' from '123cbabc');
rtrim
-----
  123   <==== it is not "123cb"!!
(1 row)


In current trim() function, MULTIBYTE string is broken.

pgbash> select trim(trailing '0x8842' from '0xB1428842');
                                --~~            ~~--~~
rtrim
-----
 0xB1   <==== MULTIBYTE string broken (This is a bug.)
(1 row)


If trim(trailing 'abc' from '123cbabc') returns "123cb", current 
trim() spec is broken. However, the spec that 'abc' means ~'[abc]' 
is ugly. It seems that this ugly spec isn't used for any kind of
functions argument and SQL expression except for trim().

How do you think about the trim() spec ?

--
Regards,
SAKAIDA Masaaki  -- Osaka, Japan



Sat, 03 Jun 2000 12:28:56 +0900
SAKAIDA Masaaki <sakaida@psn.co.jp>
trim() spec
> Can you tell me trim() spec, please ? (This problem has been
> discussed in pgsql-jp ML. )
> In trim(trailing 'abc' from '123cbabc') function, 'abc' means
> ~'[abc]'.
> If trim(trailing 'abc' from '123cbabc') returns "123cb", current
> trim() spec is broken. However, the spec that 'abc' means ~'[abc]'
> is ugly. It seems that this ugly spec isn't used for any kind of
> functions argument and SQL expression except for trim().
> How do you think about the trim() spec ?

afaict, the SQL92 spec for trim() requires a single character as the
first argument; allowing a character string is a Postgres extension. On
the surface, istm that this extension is in the spirit of the SQL92
spec, in that it allows trimming several possible characters.

I'm not sure if SQL3/SQL99 has anything extra to say on this.

position() and substring() seem to be able to do what you want;

 select substring('123ab' for position('ab' in '123ab')-1);

gives '123', while

 select substring('123ab' for position('d' in '123ab')-1);

gives '123ab', which seems to be the behavior you might be suggesting
for trim().

                        - Tom

Tue, 13 Jun 2000 01:35:57 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: trim() spec
Can someone comment on this?


> Hi,
> 
> Can you tell me trim() spec, please ? (This problem has been 
> discussed in pgsql-jp ML. )
> 
> In trim(trailing 'abc' from '123cbabc') function, 'abc' means
> ~'[abc]'. 
> 
> pgbash> select trim(trailing 'abc' from '123cbabc');
> rtrim
> -----
>   123   <==== it is not "123cb"!!
> (1 row)
> 
> 
> In current trim() function, MULTIBYTE string is broken.
> 
> pgbash> select trim(trailing '0x8842' from '0xB1428842');
>                                 --~~            ~~--~~
> rtrim
> -----
>  0xB1   <==== MULTIBYTE string broken (This is a bug.)
> (1 row)
> 
> 
> If trim(trailing 'abc' from '123cbabc') returns "123cb", current 
> trim() spec is broken. However, the spec that 'abc' means ~'[abc]' 
> is ugly. It seems that this ugly spec isn't used for any kind of
> functions argument and SQL expression except for trim().
> 
> How do you think about the trim() spec ?
> 
> --
> Regards,
> SAKAIDA Masaaki  -- Osaka, Japan
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  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

Tue, 13 Jun 2000 04:04:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: trim() spec
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> If trim(trailing 'abc' from '123cbabc') returns "123cb", current
>> trim() spec is broken. However, the spec that 'abc' means ~'[abc]'
>> is ugly. It seems that this ugly spec isn't used for any kind of
>> functions argument and SQL expression except for trim().

> afaict, the SQL92 spec for trim() requires a single character as the
> first argument; allowing a character string is a Postgres extension. On
> the surface, istm that this extension is in the spirit of the SQL92
> spec, in that it allows trimming several possible characters.

MySQL's crashme list has some useful information about this: they
indicate whether an implementation considers a multi-char TRIM argument
to be a set (our way) or a substring (MySQL does it that way, for one).
So there's precedent for both sides.

Given that our trim() code claims to exist for Oracle compatibility,
I'd have assumed that its handling of multi-char arguments followed
Oracle.  But the crashme list doesn't show Oracle as supporting either
semantics.  Can someone with access to Oracle check this?

> I'm not sure if SQL3/SQL99 has anything extra to say on this.

The 1994 draft specifies just a single trim character, same as SQL92.
Haven't gotten around to grabbing the 99 draft yet...

			regards, tom lane

Tue, 13 Jun 2000 10:45:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: trim() spec 
On Tue, Jun 13, 2000 at 10:45:07AM -0400, Tom Lane wrote:
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >> If trim(trailing 'abc' from '123cbabc') returns "123cb", current
> >> trim() spec is broken. However, the spec that 'abc' means ~'[abc]'
> >> is ugly. It seems that this ugly spec isn't used for any kind of
> >> functions argument and SQL expression except for trim().
> 
> > afaict, the SQL92 spec for trim() requires a single character as the
> > first argument; allowing a character string is a Postgres extension. On
> > the surface, istm that this extension is in the spirit of the SQL92
> > spec, in that it allows trimming several possible characters.
> 
> MySQL's crashme list has some useful information about this: they
> indicate whether an implementation considers a multi-char TRIM argument
> to be a set (our way) or a substring (MySQL does it that way, for one).
> So there's precedent for both sides.
> 
> Given that our trim() code claims to exist for Oracle compatibility,
> I'd have assumed that its handling of multi-char arguments followed
> Oracle.  But the crashme list doesn't show Oracle as supporting either
> semantics.  Can someone with access to Oracle check this?

Oracle 8i gives you an error if you give a multi-character argument
to TRIM. So anything that worked with Oracle would work the same with
us.

Richard

Tue, 13 Jun 2000 16:25:50 +0100
Richard Poole <richard.poole@vi.net>
Re: trim() spec