pcre.index throwing TypeException:user error after using

Question

I'm trying to get the PCRE module working with my monetDB server.

I run the following:

CREATE OR REPLACE FUNCTION pcre_index(s string, pattern string) RETURNS integer EXTERNAL NAME pcre."index";

But when I run the function:

select pcre_index('this is a long string', '\\s');

I get the following error:

TypeException:user.main[6]:'pcre.index' undefined in:     X_0:int := pcre.index(X_1:str, X_2:str);

I know other PCRE functions are working because when I run:

CREATE OR REPLACE FUNCTION pcre_imatch(s string, pattern string) RETURNS boolean EXTERNAL NAME pcre."imatch";
select pcre_imatch('this is a long string', '\\b\\s');

I get the following:

+-------+
| %2    |
+=======+
| true  |
+-------+

I'm using this link to derive the function that I should write.

Please let me know what I am doing wrong. I am using MacOS and have installed monetdb via homebrew.

Thank you for your time and help.

Answer 1

You created your pcre_index in a correct way. The problem is that pcre.index requires a type pcre as its first parameter.

Use pcre.patindex instead:

sql>CREATE OR REPLACE FUNCTION pcre_patindex(pat string, s string) RETURNS integer EXTERNAL NAME pcre."patindex";
operation successful
sql>select pcre_patindex('s', 'this is a long string');
+------+
| %2   |
+======+
|    4 |
+------+
1 tuple
sql>select pcre_patindex(R'\s', 'this is a long string');
+------+
| %2   |
+======+
|    0 |
+------+
1 tuple

NB1: with pcre.index and pcre.patindex, the pattern comes first.

NB2 the R'' syntax is preferable over the backslashes

NB3 pcre.(pat)index uses an SQL pattern to try to match the string, not a PCRE regular expression. That's why all those attempts with \s and \b fail: they're not SQL patterns. The interface to produce a PCRE value that could be used in pcre.(pat)index was removed in 2014.