title: How to use regexp_matches() in an UPDATE statement?
body: <p>I am trying to clean up a table that has a very messy varchar column, with entries of the sorts:</p>
  
  <pre><code>&lt;u&gt;&lt;font color="#0000FF"&gt;&lt;a href="http://virginialidar.com/index-3.html#.VgLbFPm6e73" target="_blank"&gt;VA Lidar&lt;/a&gt;&lt;/font&gt;&lt;/u&gt; OR &lt;u&gt;&lt;font color="#0000FF"&gt;&lt;a href="https://inport.nmfs.noaa.gov/inport/item/50122" target="_blank"&gt;InPort Metadata&lt;/a&gt;&lt;/font&gt;&lt;/u&gt;
  </code></pre>
  
  <p>I would like to update the column by keeping only the html links, and separating them with a coma if there are more than one. Ideally I would do something like this:</p>
  
  <pre><code>UPDATE mytable
  SET column = array_to_string(regexp_matches(column,'(?&lt;=href=").+?(?=\")','g') , ',');
  </code></pre>
  
  <p>But unfortunately this returns an error in Postgres 10:</p>
  
  <pre><code>ERROR: set-returning functions are not allowed in UPDATE
  </code></pre>
  
  <p>I assume <code>regexp_matches()</code> is the said set-returning function. Any ideas on how I can achieve this?</p>