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

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

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

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