From: David Johnston <polobo@yahoo.com>
To: 'jan zimmek' <jan.zimmek@web.de>
To: pgsql-sql@postgresql.org
Subject: Re: replace text occurrences loaded from table
Date: Tue, 30 Oct 2012 11:08:46 -0400
Message-ID: <00b001cdb6b0$75c2c350$614849f0$@yahoo.com> (raw)
In-Reply-To: <47098D72-27B8-4634-8010-066B4765FE91@web.de>
References: <47098D72-27B8-4634-8010-066B4765FE91@web.de>
> -----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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, jan.zimmek@web.de
Subject: Re: replace text occurrences loaded from table
In-Reply-To: <00b001cdb6b0$75c2c350$614849f0$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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