Re: Emulating flexible regex replace

Hi:

On Fri, Oct 24, 2014 at 8:24 AM, twoflower <standa(dot)kurik(at)gmail(dot)com> wrote:

> Thank you Francisco, that's a clever idea. However, I don't think this
> would
> reduce the complexity since the target pattern can contain
>
> 1) regular back-references (referencing to matches of its own)
> 2) the special source text references I mentioned
>

Well, if I had this problem I would consider solving part of it in the
application, not in the database ( like rewriting two patterns into one on
the app ).

>
> Obviously, these will have to be written in a different way and this I
> believe brings me back to start (or in other words, it's not a silver
> bullet
> obviating the need to rewrite the target pattern manually).
>

Yeah, I would solve it renumbering backreferences, but it will be a genuine
PITA, specially if you want to allow an arbitrary number of them.

> I will probably end up writing a function in PL/Perl which Tom Lane
> suggested since I'm apparently not skilled in SQL enough to be able to do
> it
> using a single query without using custom functions.
>

You can do two things with a pl/perl function, one is write and use it in
the condition of a general query ( where supermatch(s,t,s_p,t_p) ) or,
depending on your query, you may write a table return function ( select
whatever from my_fancy_func(s_p, t_p, whatever_else ). And, IMO, trying to
solve everything in SQL is not always TRTTD. There are a lot of special
apps which needs to be solved in a mix of code, and in problems as complex
as the one you are proposing ( like, when filtering CDRs by esoteric
criteria in my apps ) I've many times found that the easier ( and faster )
way is let the db do it's stuff in sql and postfilter it in the app ( in my
case, let the db do time range, duration and number prefix filtering, which
it's really good at, reducing the dataset from billions to tens of
thousands of records which it streams really fast into a perl app which
then does the later fancy conditions in a breeze ).

Regards.
Francisco Olarte.