Gleaned somewhere off the internet (sorry, my searching is now coming up blank), this function is the best way to encode HTML in PostgreSQL:

-- A useful function for HTML-encoding a string
create or replace function encodehtml(str text) returns text AS
$$
BEGIN
    return
      CASE WHEN str is not null THEN
        regexp_replace(xmlelement(name x, str, null)::text, '</?x>', '', 'g')
      ELSE
        null
      END;
END; $$ language 'plpgsql';

If we also want to encode quotes, eg. if embedding the result in a HTML attribute:

create or replace function encodehtmlattr(str text) returns text AS
$$
BEGIN
    return
      CASE WHEN str is not null THEN
        replace(regexp_replace(xmlelement(name x, str, null)::text, '</?x>', '', 'g'), '"', '&quot;')
      ELSE
        null
      END;
END; $$ language 'plpgsql';
  • No labels