Re: [BUGS] BUG #8335: trim() un-document behaviour
Finally could make it using
regexp_replace (my_field_to_trim, '.+my_triming_string' , '') for the leading
case
regexp_replace (my_field_to_trim, 'my_triming_string(.+)' , '') for the
trailing case
And both of them in this order a for the both one.
I don't know why, but could not use '*?'
Since in no instance my_triming_string was at the extremlity of the sting, I
did not need it, but I guess something like '(|.+)my_triming_string'could have
worked
De : Bruce Momjian [via PostgreSQL] ml-node+s1045698n5766983...@n5.nabble.com
À : Romain Billon-Grand romainbillongr...@yahoo.fr
Envoyé le : Vendredi 9 août 2013 18h24
Objet : Re: BUG #8335: trim() un-document behaviour
On Fri, Aug 9, 2013 at 11:06:15AM -0400, Tom Lane wrote:
Bruce Momjian [hidden email] writes:
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
This patch is completely unsafe. It will break stored rules, which may
contain calls using the existing argument order (which will be dumped
without any of the SQL-spec syntactic sugar). To say nothing of existing
applications that may be relying on calling the underlying functions with
their existing argument order.
The inconsistency in argument order is unfortunate but we're long since
stuck with it, I'm afraid.
Yes, I have thought about this some more and another problem is that
rtrim/btrim/ltrim() use the source string first, so having trim() have
the source string second when using a comma is very confusing, e.g.:
-- with patch
SELECT trim('x', 'xabcx');
btrim
---
abc
-- btrim
SELECT btrim('xabcx', 'x');
btrim
---
abc
I think we can either document what we have, or remove the ability to
use comma with trim(). If we go with documentation, it is going to look
confusing as the optional modifier is going to be on the source string,
e.g.:
SELECT trim(both 'xabcx', 'x');
btrim
---
abc
We could modify the grammar to force the modifier on the second
argument, but that is more parser states for limited value.
--
Bruce Momjian [hidden email] http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
If you reply to this email, your message will be added to the discussion
below:http://postgresql.1045698.n5.nabble.com/BUG-8335-trim-un-document-behaviour-tp5765221p5766983.html
To unsubscribe from BUG #8335: trim() un-document behaviour, click here.
NAML
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-8335-trim-un-document-behaviour-tp5765221p5767148.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
Re: [BUGS] BUG #8335: trim() un-document behaviour
Running postgres 9.1.4 using pgAdmin3 on a macbook air intel OS 10.6 I just had this bug, using comma: SELECT trim(trailing texte_natif , 'DATEDENAISSANCE' ) FROM CROP returned the whole field, including the string 'DATEDENAISSANCE', but except the very first character, the column being called rtrim. Tryed to chang it to SELECT trim(trailing texte_natif from 'DATEDENAISSANCE' ) FROM CROP returned nothing ( as a dummy in postgres I can't say if it was null fields or empty string...) CROP.texte_natif is a quite long texte field,in which the STRING 'DATEDENAISSANCE can be present 0 to 3 times, and never before 20th character bests Romain -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8335-trim-un-document-behaviour-tp5765221p5766930.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8335: trim() un-document behaviour
Bruce Momjian br...@momjian.us writes: The attached patch swaps the arguments in the parser, and allows your expected behavior: This patch is completely unsafe. It will break stored rules, which may contain calls using the existing argument order (which will be dumped without any of the SQL-spec syntactic sugar). To say nothing of existing applications that may be relying on calling the underlying functions with their existing argument order. The inconsistency in argument order is unfortunate but we're long since stuck with it, I'm afraid. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8335: trim() un-document behaviour
On Fri, Aug 9, 2013 at 11:06:15AM -0400, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
This patch is completely unsafe. It will break stored rules, which may
contain calls using the existing argument order (which will be dumped
without any of the SQL-spec syntactic sugar). To say nothing of existing
applications that may be relying on calling the underlying functions with
their existing argument order.
The inconsistency in argument order is unfortunate but we're long since
stuck with it, I'm afraid.
Yes, I have thought about this some more and another problem is that
rtrim/btrim/ltrim() use the source string first, so having trim() have
the source string second when using a comma is very confusing, e.g.:
-- with patch
SELECT trim('x', 'xabcx');
btrim
---
abc
-- btrim
SELECT btrim('xabcx', 'x');
btrim
---
abc
I think we can either document what we have, or remove the ability to
use comma with trim(). If we go with documentation, it is going to look
confusing as the optional modifier is going to be on the source string,
e.g.:
SELECT trim(both 'xabcx', 'x');
btrim
---
abc
We could modify the grammar to force the modifier on the second
argument, but that is more parser states for limited value.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8335: trim() un-document behaviour
On Fri, Jul 26, 2013 at 02:23:10AM +, am...@amutu.com wrote:
in the postgresql doc 9.4,I find the trim() function like this:
trim([leading | trailing | both] [characters] from string)
so the trim should be pass only one argument with some optional prefix ---
but I
find the following calls with two argument is successfull but the results is
unexpected and wired:
##first call
postgres=# select trim(trailing '/, 'fasd/');
rtrim
--
(1 row)
-!!!note: it return titile is rtrim
## second call
postgres=# select trim('/', 'fasd/')
;
btrim
-
(1 row)
-!!!note: it return titile is btrim
it seems trim is transform to rtrim internal but the above call should
return error or it may produce un-expect results
(I have cleaned up this posting because single-quotes were converted to
Unicode forward-backward quotes):
What is happening is that TRIM() is converted by the parser to calls to
base functions, e.g.
\df *trim*
List of functions
Schema | Name | Result data type | Argument data types | Type
+---+--+-+
pg_catalog | btrim | bytea| bytea, bytea| normal
pg_catalog | btrim | text | text| normal
pg_catalog | btrim | text | text, text | normal
pg_catalog | ltrim | text | text| normal
pg_catalog | ltrim | text | text, text | normal
pg_catalog | rtrim | text | text| normal
pg_catalog | rtrim | text | text, text | normal
That is why the headings don't say 'trim', but 'btrim', or similar ---
not sure we can easily improve that, and you can change the label with
AS.
The larger problem is the use of ',' instead of FROM, and the backwards
interpretation of the arguments. The query:
SELECT trim('/' FROM 'fasd/')
is internally converted to:
SELECT btrim('fasd/', '/')
Note the arguments are reversed. The comma syntax does not reverse the
arguments:
SELECT trim('/', 'fasd/')
is internally converted to:
SELECT btrim('/', 'fasd/')
You can even use modifiers like TRAILING with comma syntax:
SELECT trim(TRAILING '/', 'fasd/');
and that uses 'rtrim', but of course the behavior is still reverse of
expected.
Basically the odd comma behavior is because without a FROM, the
arguments are passed directly to btrim/rtrim/ltrim, and these functions
take the origin string first, then the string of characters to remove.
You are right this is undocumented.
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
SELECT trim('x', 'xfasdx');
btrim
---
fasd
Another option would be to change the C API for the b/r/ltrim functions,
or disallow the use of the comma TRIM syntax in the parser.
I am a little worried people might be relying on the trim/comma syntax
somewhere.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 22e82ba..8419559
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*** substr_for: FOR a_expr{ $$ = $2;
*** 11993,11999
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | expr_list{ $$ = $1; }
;
in_expr: select_with_parens
--- 11993,12000
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | a_expr ',' a_expr { $$ = list_make2($3, $1); }
! | a_expr{ $$ = list_make1($1); }
;
in_expr: select_with_parens
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8335: trim() un-document behaviour
The following bug has been logged on the website: Bug reference: 8335 Logged by: Jov Email address: am...@amutu.com PostgreSQL version: 9.2.4 Operating system: suse 10 linux 64 Description: in the postgresql doc 9.4,I find the trim() function like this: trim([leading | trailing | both] [characters] from string) so the trim should be pass only one argument with some optional prefix。but I find the following calls with two argument is successfull but the results is unexpected and wired： ##first call postgres=# select trim(trailing ‘/’, ‘fasd/’); rtrim ——- (1 row) -!!!note: it return titile is rtrim ## second call postgres=# select trim(‘/’, ‘fasd/’) ; btrim ——- (1 row) -!!!note: it return titile is btrim it seems trim is transform to r、b、l trim internal，but the above call should return error or it may produce un-expect results -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
