Re: looking for a faster way to do that - Mailing list pgsql-general

From Eduardo Morras
Subject Re: looking for a faster way to do that
Date
Msg-id 4E5EF2B00065F6FC@
Whole thread Raw
In response to Re: looking for a faster way to do that  (hamann.w@t-online.de)
List 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 by date:

Previous
From: Tom Lane
Date:
Subject: Re: New feature: accumulative functions.
Next
From: Neil Tiffin
Date:
Subject: Re: Mac OS X shared_buffers not same as postgresql.conf file