Re: looking for a faster way to do that
От | Eduardo Morras |
---|---|
Тема | Re: looking for a faster way to do that |
Дата | |
Msg-id | 4E5EF2B00065F6FC@ обсуждение исходный текст |
Ответ на | Re: looking for a faster way to do that (hamann.w@t-online.de) |
Список | pgsql-general |
At 08:04 25/09/2011, hamann.w@t-online.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
В списке pgsql-general по дате отправления: