By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE OR REPLACE FUNCTION escape_html(p_input text)
RETURNS text AS
$$
DECLARE
l_clean_value text;
l_to_replace text[];
l_tag text;
BEGIN
-- ersetze alle "einfach" < und > zuerst
l_clean_value := regexp_replace(p_input, '(\s+)<(\s+)', '\1<\2', 'g');
l_clean_value := regexp_replace(l_clean_value, '(\s+)>(\s+)', '\1>\2', 'g');
-- finde alle tags die nicht geschlossen werden
select coalesce(array_agg(tag), '{}')
into l_to_replace
from (
select translate(x2.open_tag, '<>', '') as tag
from regexp_matches(l_clean_value, '<\w+>', 'g') as x1(open_tags),
unnest(x1.open_tags) as x2(open_tag)
except
select translate(x3.close_tag, '</>', '') as close_tag
from regexp_matches(l_clean_value, '</\w+>', 'g') as x2(close_tags),
unnest(x2.close_tags) as x3(close_tag)
) x;
-- in dem array l_to_replace stehen jetzt alle tags die kein schliessendes Tag haben
-- diese können direkt ersetzt werden
foreach l_tag in array l_to_replace loop
l_clean_value := replace(l_clean_value, concat('<', l_tag, '>'), concat('<', l_tag, '>'));
end loop;
return l_clean_value;
END;
$$
LANGUAGE plpgsql;
with input (doc) as (
values
('<table><tr><td><nobr>Daten1</nobr></td><td>
<nobr>Daten2</nobr>
</td></tr>
<tr><td>Daten3</td></tr>
<tr><td>Daten4</td></tr>
<tr><td>Daten5</td></tr>
<tr><td></td></tr>
<tr><td>DatenText</td></tr>
</table>')
)
select escape_html(doc)
from input
| escape_html |
|---|
| <table><tr><td><nobr>Daten1</nobr></td><td> <nobr>Daten2</nobr> </td></tr> <tr><td>Daten3</td></tr> <tr><td>Daten4</td></tr> <tr><td>Daten5</td></tr> <tr><td></td></tr> <tr><td>DatenText</td></tr> </table> |