I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim
is giving me an incorrect result:

db01=# SELECT tablename FROM pg_tables WHERE tablename LIKE '%_opto' AND

tablename NOT LIKE 'pg%'  ORDER BY tablename ASC ;
    tablename
-----------------
 center_out_opto
 circles_opto
 ellipse_opto
 ex_ellipse_opto
 figure8_opto
 ro_ellipse_opto
(6 rows)

Now I want to return the same thing only with the trailing '_opto'
removed:


db01=# SELECT rtrim(tablename, '_opto') FROM pg_tables WHERE tablename
LIKE '%_opto' AND tablename NOT LIKE 'pg%'  ORDER BY tablename ASC ;
   rtrim
------------
 center_ou                                     <=======================
NOTE: the trailing 't' is missing
 circles
 ellipse
 ex_ellipse
 figure8
 ro_ellipse
(6 rows)

However, as you can see, the 'center_out' table is missing the last 't'.
If I exclude the '_':

db01=# SELECT rtrim(tablename, 'opto') FROM pg_tables WHERE tablename
LIKE '%_opto' AND tablename NOT LIKE 'pg%'  ORDER BY tablename ASC ;
    rtrim
-------------
 center_out_
<=======================   't' shows up again
 circles_
 ellipse_
 ex_ellipse_
 figure8_
 ro_ellipse_
(6 rows)

The 't' is back.

Is there something that I'm doing wrong with my query here?

Thanks.
-Tony






Wed, 14 Mar 2001 18:14:12 -0800
"G. Anthony Reina" <reina@nsi.edu>
rtrim giving weird result
The second parameter to "rtrim" is interpreted as a set of characters and
rtrim:
"Returns string with final characters removed after the last character not
in set"

So rtrim("center_out_opto", "_opto") returns
    "center_ou"
because "u" is not in the set {o, p, t, _} but all the characters after it
are.
rtrim("center_out_opto", "pot_") will produce the same thing.


----- Original Message -----
From: "G. Anthony Reina" <reina@nsi.edu>
To: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgresql.org>
Sent: Wednesday, March 14, 2001 9:14 PM
Subject: [HACKERS] rtrim giving weird result


> I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim
> is giving me an incorrect result:
>
> db01=# SELECT tablename FROM pg_tables WHERE tablename LIKE '%_opto' AND
>
> tablename NOT LIKE 'pg%'  ORDER BY tablename ASC ;
>     tablename
> -----------------
>  center_out_opto
>  circles_opto
>  ellipse_opto
>  ex_ellipse_opto
>  figure8_opto
>  ro_ellipse_opto
> (6 rows)
>
> Now I want to return the same thing only with the trailing '_opto'
> removed:
>
>
> db01=# SELECT rtrim(tablename, '_opto') FROM pg_tables WHERE tablename
> LIKE '%_opto' AND tablename NOT LIKE 'pg%'  ORDER BY tablename ASC ;
>    rtrim
> ------------
>  center_ou                                     <=======================
> NOTE: the trailing 't' is missing
>  circles
>  ellipse
>  ex_ellipse
>  figure8
>  ro_ellipse
> (6 rows)
>
> However, as you can see, the 'center_out' table is missing the last 't'.
> If I exclude the '_':
>
> db01=# SELECT rtrim(tablename, 'opto') FROM pg_tables WHERE tablename
> LIKE '%_opto' AND tablename NOT LIKE 'pg%'  ORDER BY tablename ASC ;
>     rtrim
> -------------
>  center_out_
> <=======================   't' shows up again
>  circles_
>  ellipse_
>  ex_ellipse_
>  figure8_
>  ro_ellipse_
> (6 rows)
>
> The 't' is back.
>
> Is there something that I'm doing wrong with my query here?
>
> Thanks.
> -Tony
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Wed, 14 Mar 2001 22:36:29 -0500
"Ken Hirsch" <kenhirsch@myself.com>
Re: rtrim giving weird result
"G. Anthony Reina" <reina@nsi.edu> writes:
> I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim
> is giving me an incorrect result:

No, you have an incorrect understanding of rtrim.  The second argument
is a set of removable characters, not a string to be matched.

AFAIK we are following Oracle in defining it that way ...

			regards, tom lane

Wed, 14 Mar 2001 23:10:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: rtrim giving weird result 
Hi, all,

Could somebody tell me if there is a work around to 
create "union on view" (which seems not implemented
in the postgres yet) ?

Also, is there any alternative query that can do:

select * from (select * from table);

I could not find an answer from the old archieve,
and sorry if this has been answered previously.
(I am new here :)

Regards,
Jae







Wed, 14 Mar 2001 20:53:02 -0800
"Jae-Woong Hwnag" <jaewh@email.com>
Union on view and..

If you're willing to wait or use the betas, 7.1 
should probably do both of these.  (Won't 
quite make toast though).

[Although I believe the second'll be something
like: select * from (select * from table) alias;]

On Wed, 14 Mar 2001, Jae-Woong Hwnag wrote:

> Hi, all,
> 
> Could somebody tell me if there is a work around to 
> create "union on view" (which seems not implemented
> in the postgres yet) ?
> 
> Also, is there any alternative query that can do:
> 
> select * from (select * from table);
> 
> I could not find an answer from the old archieve,
> and sorry if this has been answered previously.
> (I am new here :)


Wed, 14 Mar 2001 21:09:52 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Union on view and..
Ken Hirsch wrote:

> So rtrim("center_out_opto", "_opto") returns
>     "center_ou"
> because "u" is not in the set {o, p, t, _} but all the characters after it
> are.
> rtrim("center_out_opto", "pot_") will produce the same thing.
>

That seems like an odd definition (although as Tom points out, it is
consistent with Oracle).

Is there a way to just remove the "_opto" from the end of the string?

-Tony



Thu, 15 Mar 2001 09:34:04 -0800
"G. Anthony Reina" <reina@nsi.edu>
Re: rtrim giving weird result
On Thu, Mar 15, 2001 at 09:34:04AM -0800, G. Anthony Reina wrote:
> Ken Hirsch wrote:
> 
> > So rtrim("center_out_opto", "_opto") returns
> >     "center_ou"
> > because "u" is not in the set {o, p, t, _} but all the characters after it
> > are.
> > rtrim("center_out_opto", "pot_") will produce the same thing.
> >

Modulo the correct quoting conventions for strings, of course.

> 
> That seems like an odd definition (although as Tom points out, it is
> consistent with Oracle).

Yup, I got bit by it, trying to remove 'The ' from the front of a set of
words, in order to get an approximation of 'library sort'.

> 
> Is there a way to just remove the "_opto" from the end of the string?

If you have exactly one known string to (optionally) remove, this works
(and even works if the string is missing. Watch out for the early
occurance of substring problem, though!):

test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1)); 
   substr   
------------
 center_out
(1 row)

test=#  select substr('center_out_opto',1,(strpos('center_out_opto','foo')-1));
     substr      
-----------------
 center_out_opto
(1 row)

test=# 

Ross

Thu, 15 Mar 2001 11:53:37 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: rtrim giving weird result
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
>> Is there a way to just remove the "_opto" from the end of the string?

> If you have exactly one known string to (optionally) remove, this works
> (and even works if the string is missing. Watch out for the early
> occurance of substring problem, though!):

> test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1)); 

My first thought for any moderately complicated string-bashing problem
is to write a function in pltcl or plperl ... they are much stronger in
string manipulation than SQL itself is.

			regards, tom lane

Thu, 15 Mar 2001 13:18:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: rtrim giving weird result 
On Thu, Mar 15, 2001 at 01:18:57PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> >> Is there a way to just remove the "_opto" from the end of the string?
> 
> > If you have exactly one known string to (optionally) remove, this works
> > (and even works if the string is missing. Watch out for the early
> > occurance of substring problem, though!):
> 
> > test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1)); 
> 
> My first thought for any moderately complicated string-bashing problem
> is to write a function in pltcl or plperl ... they are much stronger in
> string manipulation than SQL itself is.

Agreed, hence the caveats about 'exactly one string, that you know ahead of
time, and never appears as a substring ...'

But it _can_ be done, it's just not pretty. And it _is_ standard SQL:
here's the SQL92 spelling of the above:

SELECT SUBSTRING ('center_out_opto' FROM 1 FOR (POSITION ('_opto' IN 'center_out_opto') - 1));

Ross

Thu, 15 Mar 2001 12:37:02 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: rtrim giving weird result