How to substitute substring in column A with matching string in map table I want to process foo's Column A to become B, using the replacement patterns found on a separate table bar.

CREATE TABLE foo
AS
  SELECT *
  FROM ( VALUES
    ( 'one, 1, two, 2, three, 3' , 'one apple, two bananas, three strawberries' ),
    ( 'one, 1, four, 4, three, 3', 'one apple, four oranges, three strawberries' ),
    ( 'two, 2, four, 4, three, 3', 'two bananas, four oranges, three strawberries' )
  ) AS t(a,b);

CREATE TABLE bar
AS
  SELECT *
  FROM ( VALUES
    ( 'one, 1'    , 'one apple' ),
    ( 'two, 2'    , 'two bananas' ),
    ( 'three, 3'  , 'three strawberries' ),
    ( 'four, 4'   , 'four oranges' )
  ) AS t(pattern,repl);
I wouldn't do this. This is called "lexicon replacement". It's done using FTS's Thesaurus Dictionary. You can ask another question if you have a problem getting that working and you decide to go down that route.

That said, if you want to do this recursively, you can. It's certainly doable.

WITH RECURSIVE f AS (
  SELECT row_number() OVER (), *
  FROM bar
),
t(orig,working,row) AS (
  SELECT a , a , 0
  FROM foo
  UNION ALL
    SELECT orig, regexp_replace(working,pattern,repl), row+1
    FROM t
    INNER JOIN f
      ON row_number = row+1
)
SELECT orig, working
FROM t
WHERE row = (SELECT count(*) FROM f)
ORDER BY orig;


Output

           orig            |                    working                    
---------------------------+-----------------------------------------------
 one, 1, four, 4, three, 3 | one apple, four oranges, three strawberries
 one, 1, two, 2, three, 3  | one apple, two bananas, three strawberries
 two, 2, four, 4, three, 3 | two bananas, four oranges, three strawberries
(3 rows)


Note if you have a serial on bar then remove CTE f.