Apply all regexp_replaces from table sequentally to one string

Question

I need to sequentially apply all regex_replace's from the table to one given string. How could I do that with one SQL request?

PostgreSQL is 11.2.

xbox=> \d presence_regex
           Table "public.presence_regex"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 match   | text    |           |          |
 replace | text    |           |          |

xbox=> select regexp_replace('Playing FIFA 19 Pro Clubs 2-1 LIL V BVB, 1st Half', 
                                match, replace) from presence_regex;

Select above performs replace to the original string and returns number of strings but I need the only one: the result after applying all the regex_replaces from the table.

Is it possible?

Answer 1

You need dynamic SQL for this. Meaning, you have to first build your SQL statement using data in table presence_regex, then execute it in the next step. You can wrap it up in a plpgsql function as single step.

Assuming

• there is a column to indicate the order in which replacements shall be applied (which is meaningful).

• you want to replace all occurrences, not just the first.

This function would achieve it:

CREATE OR REPLACE FUNCTION f_serial_regexp(_string text, OUT _result text) AS
$func$
BEGIN
   EXECUTE (
   SELECT 'SELECT '
       || string_agg('regexp_replace(', '')
       || '$1'
       || string_agg(format(', %L, %L, ''g'')', match, replace), '')
   FROM   (
      SELECT match, replace
      FROM   presence_regex
      ORDER  BY order_id    -- column indicating the order
      ) sub
   )
   USING $1
   INTO _result;
END
$func$  LANGUAGE plpgsql;

It builds and executes a SELECT query of the form (example for two rows in the table):

SELECT regexp_replace(regexp_replace($1, 'FIFA \d+', 'bab', 'g'), 'bab', 'XXX', 'g')

Remove the 4th parameter 'g' from regexp_replace() to only replace the first occurrence.

Call:

SELECT f_serial_regexp('Playing FIFA 19 Pro Clubs 2-1 LIL V BVB, 1st Half');

db<>fiddle here

Closely related, with more explanation:

• Store a formula in a table and use the formula in a function

Answer 2

based on Erwin Answer, a little bit of refactcoring. Since too much select a little bit of confusing. (for me personally)

create or replace function f_serial_regexp(_string text)
returns setof text language plpgsql as
$$
    begin
    RETURN QUERY 
    execute format('select regexp_replace($1 , match, replace, ''g'') '
    || 'from  presence_regex order by order_id')  using _string;
    end
$$;

obviously, pre-conditioin is the table presence_regex, column: match, replace must be there.