In postgreSQL (9.5), PgAdmin III, I would like to generalize this POSIX statement for two words:
This works for the words 'new' and 'intermediate' with word boundaries:
select * from cpt where cdesc ~* '^(?=.*\mnew\M)(?=.*\mintermediate\M)'
This fails ( the "where" argument is seen as a text string):
select * from cpt where cdesc ~* '^(?=.*\m'||'new'||'\M)(?=.*\mintermediate\M)'
How can this be written for a generlized function, e.g.:
CREATE OR REPLACE FUNCTION getDesc(string1 text, string2 text)
RETURNS SETOF cpt AS
$BODY$
select * from cpt where cdesc ~* '^(?=.*\m$1\M)(?=.*\m$2\M)'
$BODY$
LANGUAGE sql VOLATILE;
(where $1 is string1 and $2 is string2)
TIA
Edit. Match stings in cdesc would be:
Non-match would be:
Please note the order of the words is not important as long as both are present. Also, either word may have a punctuation mark -- (comma or period)--immediately following the word (no space).
My first suggestion would be to split the expensive regex into two SQL WHERE clauses and:
As for the regex you are using:
~* ( '^(?=.*\m' || 'new' || '\M)(?=.*\mintermediate\M)' )