Page tree
Skip to end of metadata
Go to start of metadata

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