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'), '"', '"') ELSE null END; END; $$ language 'plpgsql';