pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedregexp_replace grief
6+ messages / 4 participants
[nested] [flat]
* regexp_replace grief
@ 2013-04-10 23:59 Armin Resch <reschab@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Armin Resch @ 2013-04-10 23:59 UTC (permalink / raw)
To: pgsql-admin
Not sure this is the right list to vent about this but here you go:
I) select regexp_replace('BEFORE.AFTER','(.*)\..*','\1','g') "Substring"
II) select regexp_replace('BEFORE.AFTER','(.*)\\..*','\\1','g') "Substring"
Executing (II) against pg 8.4.4 or 9.0.4 yields 'BEFORE', but in order for
9.1.7 to yield the same one has to execute (I) .. bummer
-ar
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: regexp_replace grief
@ 2013-04-11 01:08 Craig James <cjames@emolecules.com>
parent: Armin Resch <reschab@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Craig James @ 2013-04-11 01:08 UTC (permalink / raw)
To: Armin Resch <reschab@gmail.com>; pgsql-admin
On Wed, Apr 10, 2013 at 4:59 PM, Armin Resch <reschab@gmail.com> wrote:
> Not sure this is the right list to vent about this but here you go:
>
> I) select regexp_replace('BEFORE.AFTER','(.*)\..*','\1','g') "Substring"
> II) select regexp_replace('BEFORE.AFTER','(.*)\\..*','\\1','g') "Substring"
>
> Executing (II) against pg 8.4.4 or 9.0.4 yields 'BEFORE', but in order for
> 9.1.7 to yield the same one has to execute (I) .. bummer
>
This has nothing to do with regexp's. It's a change in how '\' is
interpreted in any quoted string. The change came with Postgres 9.x and is
documented in the release notes. It brings Postgres into compliance with
the SQL standard.
In Perl, I do something like this:
my $pg_bs_char;
if ($dbh->get_info($GetInfoType{SQL_DBMS_VER}) gt "09.00.00") {
$pg_bs_char = "\\"; # a single '\' for PG 9.1 and higher
} else {
$pg_bs_char = "\\\\"; # a double '\\' for PG up to 9.0
}
You can also revert to the old 8.x interpretation; see
http://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html
Craig
>
> -ar
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: regexp_replace grief
@ 2013-04-11 01:22 Armin Resch <reschab@gmail.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 6+ messages in thread
From: Armin Resch @ 2013-04-11 01:22 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
Thx for clarification, Craig. Your Perl snippet comes in handy, too.
-ar
On Apr 10, 2013, at 8:08 PM, Craig James <cjames@emolecules.com> wrote:
> On Wed, Apr 10, 2013 at 4:59 PM, Armin Resch <reschab@gmail.com> wrote:
>> Not sure this is the right list to vent about this but here you go:
>>
>> I) select regexp_replace('BEFORE.AFTER','(.*)\..*','\1','g') "Substring"
>> II) select regexp_replace('BEFORE.AFTER','(.*)\\..*','\\1','g') "Substring"
>>
>> Executing (II) against pg 8.4.4 or 9.0.4 yields 'BEFORE', but in order for 9.1.7 to yield the same one has to execute (I) .. bummer
>
> This has nothing to do with regexp's. It's a change in how '\' is interpreted in any quoted string. The change came with Postgres 9.x and is documented in the release notes. It brings Postgres into compliance with the SQL standard.
>
> In Perl, I do something like this:
>
> my $pg_bs_char;
> if ($dbh->get_info($GetInfoType{SQL_DBMS_VER}) gt "09.00.00") {
> $pg_bs_char = "\\"; # a single '\' for PG 9.1 and higher
> } else {
> $pg_bs_char = "\\\\"; # a double '\\' for PG up to 9.0
> }
>
> You can also revert to the old 8.x interpretation; see
>
> http://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html
>
> Craig
>
>>
>> -ar
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: regexp_replace grief
@ 2013-04-12 10:16 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Armin Resch <reschab@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Albe Laurenz @ 2013-04-12 10:16 UTC (permalink / raw)
To: Armin Resch *EXTERN* <reschab@gmail.com>; pgsql-admin
Armin Resch wrote:
> Not sure this is the right list to vent about this but here you go:
>
> I) select regexp_replace('BEFORE.AFTER','(.*)\..*','\1','g') "Substring"
> II) select regexp_replace('BEFORE.AFTER','(.*)\\..*','\\1','g') "Substring"
>
> Executing (II) against pg 8.4.4 or 9.0.4 yields 'BEFORE', but in order for 9.1.7 to yield the same one
> has to execute (I) .. bummer
To be immune against different settings of standard_conforming_strings,
use the extended string literal syntax:
select regexp_replace('BEFORE.AFTER',E'(.*)\\..*',E'\\1','g') "Substring";
That will work in all versions.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: regexp_replace grief
@ 2013-04-12 18:12 Armin Resch <reschab@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 6+ messages in thread
From: Armin Resch @ 2013-04-12 18:12 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
Thx, Albe. I tested both proposals, and I gravitate now more towards the
E'' nomenclature since it avoids something like this:
$pg_bs_char = ( $dbh->{pg_server_version} >= 90100 ) ? "\\" : "\\\\";
Hoping for a long half life of the E'' nomenclature ...
-ar
On Fri, Apr 12, 2013 at 5:16 AM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
> Armin Resch wrote:
> > Not sure this is the right list to vent about this but here you go:
> >
> > I) select regexp_replace('BEFORE.AFTER','(.*)\..*','\1','g') "Substring"
> > II) select regexp_replace('BEFORE.AFTER','(.*)\\..*','\\1','g')
> "Substring"
> >
> > Executing (II) against pg 8.4.4 or 9.0.4 yields 'BEFORE', but in order
> for 9.1.7 to yield the same one
> > has to execute (I) .. bummer
>
> To be immune against different settings of standard_conforming_strings,
> use the extended string literal syntax:
>
> select regexp_replace('BEFORE.AFTER',E'(.*)\\..*',E'\\1','g') "Substring";
>
> That will work in all versions.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: regexp_replace grief
@ 2013-04-12 18:16 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Armin Resch <reschab@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2013-04-12 18:16 UTC (permalink / raw)
To: Armin Resch <reschab@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
Armin Resch escribió:
> Thx, Albe. I tested both proposals, and I gravitate now more towards the
> E'' nomenclature since it avoids something like this:
>
> $pg_bs_char = ( $dbh->{pg_server_version} >= 90100 ) ? "\\" : "\\\\";
>
> Hoping for a long half life of the E'' nomenclature ...
E'' is very unlikely to ever go away; its main problem is that it's not
standards-conformant.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-04-12 18:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-10 23:59 regexp_replace grief Armin Resch <reschab@gmail.com>
2013-04-11 01:08 ` Craig James <cjames@emolecules.com>
2013-04-11 01:22 ` Armin Resch <reschab@gmail.com>
2013-04-12 10:16 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-04-12 18:12 ` Armin Resch <reschab@gmail.com>
2013-04-12 18:16 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox