Re: problem with regexp_matches in nested funcion

Thank you for your feedback.

Finally I have solved nesting the regexp_matches functions inside
sub-select and selecting the first row with [1]

It works for me.

select message as before,
replace(message,
(select
regexp_matches(message,'\\$\\$.*?\\{tabular\\}.*?\\$\\$','n'))[1],
replace((select
regexp_matches(message,'\\$\\$.*?\\{tabular\\}.*?\\$\\$','n'))[1],'{tabular}','{array}'))
as later
from mdl_forum_posts
where message ~ '\\$\\$.*?\\{tabular\\}.*?\\$\\$';

2015-09-22 14:36 GMT+02:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

> On Tuesday, September 22, 2015, Isaac Marco Blancas <isaac(dot)marco(at)udima(dot)es>
> wrote:
>
>> I get this error:
>>
>> ERROR: functions and operators can take at most one set argument
>> SQL state: 0A000
>>
>> When I run this:
>>
>> select message as before,
>> replace(message,
>>
>> array_to_string(regexp_matches(message,'\\$\\$.*?\\{tabular\\}.*?\\$\\$'),
>> '#'),
>>
>> replace(array_to_string(regexp_matches(message,'\\$\\$.*?\\{tabular\\}.*?\\$\\$'),
>> '#'),'tabular','array')) as later
>> from mdl_forum_posts
>> where message ~ '\\$\\$.*?\\{tabular\\}.*?\\$\\$';
>>
>> I know the problem is in the second array_to_string. Some idea?
>>
>>
> Regexp_matches returns a set of arrays, not one-and-only-one. You need to
> decide on a strategy to pass only single rows into the array and replace
> functions. I personally wrote a regexp_matches_single function that wraps
> the and use that in almost all situations. It wraps the Mai. Function
> but is defined to always return a single array.
>
> David J.
>

--
Isaac Marco Blancas
Universidad a Distancia de Madrid (UDIMA)
902 02 00 03