Re: problem with regexp_matches in nested funcion - Mailing list pgsql-sql

On Tuesday, September 22, 2015, Isaac Marco Blancas <isaac.marco@udima.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.