Making substrings uppercase

Oliver Kohll - Mailing Lists wrote

select regexp_replace(sentence,'\*(.*?)\*','' || upper('\1'),'g') from
sentences;

Yeah, you cannot embed a function-call result in the "replace with" section;
it has to be a literal (with the group insertion meta-sequences allowed of
course).

I see two possible approaches.

1) Use pl/perl (or some variant thereof) which has facilities to do just
this.
2) Use regexp_matches(,,'g') to explode the input string into its components
parts. You can explode it so every character of the original string is in
the output with the different columns containing the "raw" and "to modify"
parts of each match. This would be done in a sub-query and then in the
parent query you would "string_agg(...)" the matches back together while
manipulating the columns needed "i.e., string_agg(c1 || upper(c3))"

HTH

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Making-substrings-uppercase-tp5770096p5770108.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 9 Sep 2013, at 14:41, David Johnston <polobo@yahoo.com> wrote:

Oliver Kohll - Mailing Lists wrote

select regexp_replace(sentence,'\*(.*?)\*','' || upper('\1'),'g') from
sentences;

Yeah, you cannot embed a function-call result in the "replace with" section;
it has to be a literal (with the group insertion meta-sequences allowed of
course).

I see two possible approaches.

1) Use pl/perl (or some variant thereof) which has facilities to do just
this.
2) Use regexp_matches(,,'g') to explode the input string into its components
parts. You can explode it so every character of the original string is in
the output with the different columns containing the "raw" and "to modify"
parts of each match. This would be done in a sub-query and then in the
parent query you would "string_agg(...)" the matches back together while
manipulating the columns needed "i.e., string_agg(c1 || upper(c3))"

HTH

David J.

I see, I'm going with Perl, thanks.

Oliver

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

For special testing reason, I am trying to restore PG from a backup that the basebase is from Standby and WAL files are from Master. During recovery phase, for every WAL file process, it returned 'invalid resource manager ID in primary checkpoint record' and paused, I had to manually run "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set PG to ignore this kind of errors.

LOG: database system was shut down at 2013-09-10 03:06:29 : starting archive recovery
LOG: restored log file "00000001000000B9000000CA" from archive
LOG: invalid resource manager ID in primary checkpoint record
LOG: invalid secondary checkpoint link in control file
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 17969) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
.
LOG: restored log file "00000001000000B9000000CB" from archive
LOG: invalid resource manager ID in primary checkpoint record
LOG: invalid secondary checkpoint link in control file
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 17981) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
.
LOG: restored log file "00000001000000B9000000CC" from archive
.
LOG: restored log file "00000001000000B9000000CD" from archive

Please advise.
regards

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Oliver Kohll - Mailing Lists wrote:

Hello,

Given a string with certain words surrounded by stars, e.g.

The *quick* *brown* fox jumped over the *lazy* dog

can you transform the words surrounded by stars with uppercase versions, i.e.

Maybe you can turn that into a resultset, then uppercase individual
words, then join them back into a string. Something like

select string_agg(case when words like '*%*' then upper(btrim(words, '*')) else words end, ' ')
from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy* dog', ' ') as words;

string_agg
----------------------------------------------
The QUICK BROWN fox jumped over the LAZY dog
(1 fila)

This is a bit simplistic, but hopefully you get the idea.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi, any idea? can you please advise?

On 10 Sep 2013, at 3:22 AM, ascot.moss@gmail.com wrote:

Hi,

For special testing reason, I am trying to restore PG from a backup that the basebase is from Standby and WAL files are from Master. During recovery phase, for every WAL file process, it returned 'invalid resource manager ID in primary checkpoint record' and paused, I had to manually run "$PGBIN/pg_resetxlog -f $PG_DATA" many times, is there a way to set PG to ignore this kind of errors.

LOG: database system was shut down at 2013-09-10 03:06:29 : starting archive recovery
LOG: restored log file "00000001000000B9000000CA" from archive
LOG: invalid resource manager ID in primary checkpoint record
LOG: invalid secondary checkpoint link in control file
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 17969) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
.
LOG: restored log file "00000001000000B9000000CB" from archive
LOG: invalid resource manager ID in primary checkpoint record
LOG: invalid secondary checkpoint link in control file
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 17981) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
.
LOG: restored log file "00000001000000B9000000CC" from archive
.
LOG: restored log file "00000001000000B9000000CD" from archive

Please advise.
regards

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 9 Sep 2013, at 21:03, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

select string_agg(case when words like '*%*' then upper(btrim(words, '*')) else words end, ' ')
from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy* dog', ' ') as words;

string_agg
----------------------------------------------
The QUICK BROWN fox jumped over the LAZY dog

That's quite elegant. In the end I exported and used PERL, as some of my 'words' had spaces (they were ingredients like monosodium glutamate), but you could probably do a more complex regex in regexp_split_to_table to cope with that, or use pl/perl as previously suggested.

Thanks
Oliver
www.agilebase.co.uk

On Tue, Sep 10, 2013 at 5:51 AM, Oliver Kohll - Mailing Lists
<oliver.lists@gtwm.co.uk> wrote:

On 9 Sep 2013, at 21:03, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

select string_agg(case when words like '*%*' then upper(btrim(words, '*'))
else words end, ' ')
from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy*
dog', ' ') as words;

string_agg
----------------------------------------------
The QUICK BROWN fox jumped over the LAZY dog

That's quite elegant. In the end I exported and used PERL, as some of my
'words' had spaces (they were ingredients like monosodium glutamate), but
you could probably do a more complex regex in regexp_split_to_table to cope
with that, or use pl/perl as previously suggested.

IMO, pl/perl is the way to go. Being able to use postgres functions
to transform matched regex expressions would be just wonderful
although I wonder how fast it would be or if it's even possible.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and pg-Slave with streaming replication.

The archive_command is enabled and the "rsync" is used in pg-Master to save all its archived WAL files to the 3rd machine for backup purpose, by default, both fsync and wal_sync_method are commented out in postgresql.conf:

archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq %p pg@pg_slave:/usr/pg_arcxlog/%f'
#fsync = on # turns forced synchronization on or off
#wal_sync_method = fsync # the default is the first option

Thus I think the latest WAL might not be flushed to disk from time to time in pg-Master, therefore the WAL saved in the 3rd server might not contain the latest WAL. In order to avoid possible data lost, should 'fsync" be turned ON in pg-Master and pg-Slave? if yes what would be the best settings of "wal_sync_method" for these two servers respectively?

Please advise.
regards

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Sep 11, 2013 at 6:11 PM, ascot.moss@gmail.com
<ascot.moss@gmail.com>wrote:

Hi,

I am using pg 9.2.4 and ubuntu 12.04 to set up a pair of pg-Master and
pg-Slave with streaming replication.

The archive_command is enabled and the "rsync" is used in pg-Master to
save all its archived WAL files to the 3rd machine for backup purpose, by
default, both fsync and wal_sync_method are commented out in
postgresql.conf:

archive_command = '(cp %p /usr/pg_arcxlog/%f && rsync -aq %p pg@pg_slave
:/usr/pg_arcxlog/%f'
#fsync = on # turns forced
synchronization on or off
#wal_sync_method = fsync # the default is the first option

Thus I think the latest WAL might not be flushed to disk from time to time
in pg-Master, therefore the WAL saved in the 3rd server might not contain
the latest WAL. In order to avoid possible data lost, should 'fsync" be
turned ON in pg-Master and pg-Slave? if yes what would be the best
settings of "wal_sync_method" for these two servers respectively?

Please advise.
regards

I think you are confusing fsync and switching WAL segments for archiving
purposes. Here's a link to the docs to get you started:
http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT

fsync is enabled unless you disable it, the fact that it is commented out
means that it is set to its default ("on"). wal_sync_method at its default
is probably fine, but if you are interested, look at the pg_test_fsync
tool: http://www.postgresql.org/docs/9.2/static/pgtestfsync.html