pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreplace text occurrences loaded from table
3+ messages / 2 participants
[nested] [flat]
* replace text occurrences loaded from table
@ 2012-10-30 11:45 jan zimmek <jan.zimmek@web.de>
2012-10-30 15:08 ` Re: replace text occurrences loaded from table David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: jan zimmek @ 2012-10-30 11:45 UTC (permalink / raw)
To: pgsql-sql
hello,
i am actually trying to replace all occurences in a text column with some value, but the occurrences to replace are defined in a table. this is a simplified version of my schema:
create temporary table tmp_vars as select var from (values('ABC'),('XYZ'),('VAR123')) entries (var);
create temporary table tmp_messages as select message from (values('my ABC is XYZ'),('the XYZ is very VAR123')) messages (message);
select * from tmp_messages;
my ABC is XYZ -- row 1
the XYZ is very VAR123 -- row 2
now i need to somehow update the rows in tmp_messages, so that after the update i get the following:
select * from tmp_messages;
my XXX is XXX -- row 1
the XXX is very XXX -- row 2
i have implemented a solution in plpgsql by doing a nested for-loop over tmp_vars and tmp_messages, but i would like to know if there is a more efficient way to solve this problem ?
best regards
jan
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: replace text occurrences loaded from table
2012-10-30 11:45 replace text occurrences loaded from table jan zimmek <jan.zimmek@web.de>
@ 2012-10-30 15:08 ` David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Johnston @ 2012-10-30 15:08 UTC (permalink / raw)
To: 'jan zimmek' <jan.zimmek@web.de>; pgsql-sql
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of jan zimmek
> Sent: Tuesday, October 30, 2012 7:45 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] replace text occurrences loaded from table
>
> hello,
>
> i am actually trying to replace all occurences in a text column with some
> value, but the occurrences to replace are defined in a table. this is a
> simplified version of my schema:
>
> create temporary table tmp_vars as select var from
> (values('ABC'),('XYZ'),('VAR123')) entries (var); create temporary table
> tmp_messages as select message from (values('my ABC is XYZ'),('the XYZ is
> very VAR123')) messages (message);
>
> select * from tmp_messages;
>
> my ABC is XYZ -- row 1
> the XYZ is very VAR123 -- row 2
>
> now i need to somehow update the rows in tmp_messages, so that after the
> update i get the following:
>
> select * from tmp_messages;
>
> my XXX is XXX -- row 1
> the XXX is very XXX -- row 2
>
> i have implemented a solution in plpgsql by doing a nested for-loop over
> tmp_vars and tmp_messages, but i would like to know if there is a more
> efficient way to solve this problem ?
>
You may want to consider creating an alternating regular expression and
using "regexp_replace(...)" one time per message instead of "replace(...)"
three times
Not Tested: regexp_replace(message, 'ABC|XYZ|VAR123', 'XXX', 'g')
This should at least reduce the amount of overhead checking each expression
against each message would incur.
If you need even better performance you would need to find some way to
"index" the message contents so that for each expression the index can be
used to quickly identify the subset of messages that are going to be
altered. The full-text-search capabilities of PostgreSQL will probably help
here though I am not familiar with them personally.
Since you have not shared the true context of your request no alternatives
can be suggested. Also, your ability to implement certain algorithms is
influenced by the version of PostgreSQL that you are running and which you
have also not provided.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Fwd: replace text occurrences loaded from table
@ 2012-10-30 15:15 jan zimmek <jan.zimmek@web.de>
0 siblings, 0 replies; 3+ messages in thread
From: jan zimmek @ 2012-10-30 15:15 UTC (permalink / raw)
To: pgsql-sql
thanks igor, the combination of regexp_replace and string_agg works great for me.
the query runs a lot faster than my previous solution and is way easier to grasp.
Anfang der weitergeleiteten Nachricht:
> Von: Igor Romanchenko <igor.a.romanchenko@gmail.com>
> Betreff: Aw: [SQL] replace text occurrences loaded from table
> Datum: 30. Oktober 2012 14:38:57 MEZ
> An: jan zimmek <jan.zimmek@web.de>
>
> Hello,
> you can try somethig like
>
> UPDATE tmp_messages
> SET message = regexp_replace(
> message,
> (SELECT string_agg(var,'|') FROM tmp_vars),
> 'XXX',
> 'g')
>
> The idea is to form a single replacement string and to do all the replacements in one go.
> 1 scan to form the replacement string (something like 'ABC|XYZ|VAR123').
> 1 scan and update do all the changes.
>
> On Tue, Oct 30, 2012 at 1:45 PM, jan zimmek <jan.zimmek@web.de> wrote:
> hello,
>
> i am actually trying to replace all occurences in a text column with some value, but the occurrences to replace are defined in a table. this is a simplified version of my schema:
>
> create temporary table tmp_vars as select var from (values('ABC'),('XYZ'),('VAR123')) entries (var);
> create temporary table tmp_messages as select message from (values('my ABC is XYZ'),('the XYZ is very VAR123')) messages (message);
>
> select * from tmp_messages;
>
> my ABC is XYZ -- row 1
> the XYZ is very VAR123 -- row 2
>
> now i need to somehow update the rows in tmp_messages, so that after the update i get the following:
>
> select * from tmp_messages;
>
> my XXX is XXX -- row 1
> the XXX is very XXX -- row 2
>
> i have implemented a solution in plpgsql by doing a nested for-loop over tmp_vars and tmp_messages, but i would like to know if there is a more efficient way to solve this problem ?
>
>
> best regards
> jan
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-10-30 15:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-30 11:45 replace text occurrences loaded from table jan zimmek <jan.zimmek@web.de>
2012-10-30 15:08 ` David Johnston <polobo@yahoo.com>
2012-10-30 15:15 Fwd: replace text occurrences loaded from table jan zimmek <jan.zimmek@web.de>
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