Re: looking for a faster way to do that

At 08:04 25/09/2011, hamann(dot)w(at)t-online(dot)de wrote:

> >> select * from items where regexp_matches(items.code,'(ABC) (DE1)
> >> (any_substring)')<>{};
> >>
>
>Hi Eduardo,
>
>it is clear that scanning the table once with a list of matches will
>outperform
>rescanning the table for every string wanted. Now, my problem is
>that the patterns are
>dynamic as well. So if I could translate a table with one
>column and a few thousand rows
>into something like
>regexp_matches(code,'string1|string2|.....string2781')
>would ideally be a performant query. Unfortunately I have no idea
>how I could achieve this
>transformation inside the database. Doing it externally fails,
>because any single query cannot
>be more than so few characters.

You can create a plsql function and pass a setof text that do it.
Sorry but instead saying you What Must You Type, WMYT(c), i prefer
the How Should You Do way, HSYD(c). Note that you can get the same
results using other approachs (f.ex. using FTS described in chapter 12)

Check this topics:

Function
Creation http://www.postgresql.org/docs/9.0/static/sql-createfunction.html

Tutorial about Function
Creation http://www.adderpit.com/practical-postgresql/x10374.htm

HTH