while working on another item of the TODO list I realized that I should
be using a PG_TRY() block in he xmlDocDumpFormatMemory call.
Fixed in v5.
Best regards, Jim
From f503b25c7fd8d984d29536e78577741e5e7c5e9f Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Thu, 2 Feb 2023 21:27:16 +0100
Subject: [PATCH v5] Add pretty-printed XML output option
This small patch introduces a XML pretty print function.
It basically takes advantage of the indentation feature
of xmlDocDumpFormatMemory from libxml2 to format XML strings.
---
doc/src/sgml/func.sgml | 34 +++++++++++
src/backend/utils/adt/xml.c | 68 +++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/xml.out | 93 +++++++++++++++++++++++++++++
src/test/regress/expected/xml_1.out | 45 ++++++++++++++
src/test/regress/sql/xml.sql | 27 +++++++++
6 files changed, 270 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e09e289a43..e8b5e581f0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14293,6 +14293,40 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
]]></screen>
</para>
</sect3>
+
+ <sect3 id="functions-xml-xmlpretty">
+ <title><literal>xmlpretty</literal></title>
+
+ <indexterm>
+ <primary>xmlpretty</primary>
+ </indexterm>
+
+<synopsis>
+<function>xmlpretty</function> ( <type>xml</type> ) <returnvalue>xml</returnvalue>
+</synopsis>
+
+ <para>
+ Converts the given XML value to pretty-printed, indented text.
+ </para>
+
+ <para>
+ Example:
+ <screen><![CDATA[
+SELECT xmlpretty('<foo id="x"><bar id="y"><var id="z">42</var></bar></foo>');
+ xmlpretty
+--------------------------
+ <foo id="x">
+ <bar id="y">
+ <var id="z">42</var>
+ </bar>
+ </foo>
+
+(1 row)
+
+]]></screen>
+ </para>
+ </sect3>
+
</sect2>
<sect2 id="functions-xml-predicates">
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 079bcb1208..9c7f5c85cb 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -473,6 +473,74 @@ xmlBuffer_to_xmltype(xmlBufferPtr buf)
}
#endif
+Datum
+xmlpretty(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+
+ xmlDocPtr doc;
+ xmlChar *xmlbuf = NULL;
+ text *arg = PG_GETARG_TEXT_PP(0);
+ StringInfoData buf;
+ PgXmlErrorContext *xmlerrcxt;
+
+ doc = xml_parse(arg, XMLOPTION_DOCUMENT, false, GetDatabaseEncoding(), NULL);
+
+ xmlerrcxt = pg_xml_init(PG_XML_STRICTNESS_ALL);
+
+ PG_TRY();
+ {
+
+ int nbytes;
+
+ /**
+ * xmlDocDumpFormatMemory (()
+ * xmlDocPtr doc, # the XML document
+ * xmlChar **xmlbuf, # the memory pointer
+ * int *nbytes, # the memory length
+ * int format # 1 = node indenting
+ *)
+ */
+
+ xmlDocDumpFormatMemory(doc, &xmlbuf, &nbytes, 1);
+
+ if(!nbytes || xmlerrcxt->err_occurred) {
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
+ "could not indent the given XML document");
+ }
+
+ initStringInfo(&buf);
+ appendStringInfoString(&buf, (const char *)xmlbuf);
+
+ }
+ PG_CATCH();
+ {
+
+ if(doc!=NULL)
+ xmlFreeDoc(doc);
+ if(xmlbuf!=NULL)
+ xmlFree(xmlbuf);
+
+ pg_xml_done(xmlerrcxt, true);
+
+ PG_RE_THROW();
+
+ }
+ PG_END_TRY();
+
+ xmlFreeDoc(doc);
+ xmlFree(xmlbuf);
+
+ pg_xml_done(xmlerrcxt, false);
+
+ PG_RETURN_XML_P(stringinfo_to_xmltype(&buf));
+
+#else
+ NO_XML_SUPPORT();
+return 0;
+#endif
+}
+
Datum
xmlcomment(PG_FUNCTION_ARGS)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c0f2a8a77c..3224dc3e76 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8842,6 +8842,9 @@
{ oid => '3053', descr => 'determine if a string is well formed XML content',
proname => 'xml_is_well_formed_content', prorettype => 'bool',
proargtypes => 'text', prosrc => 'xml_is_well_formed_content' },
+ { oid => '4642', descr => 'Indented text from xml',
+ proname => 'xmlpretty', prorettype => 'xml',
+ proargtypes => 'xml', prosrc => 'xmlpretty' },
# json
{ oid => '321', descr => 'I/O',
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 3c357a9c7e..afaa83941b 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1599,3 +1599,96 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH
<foo/> | <foo/>
(1 row)
+-- XML pretty print: single line XML string
+SELECT xmlpretty('<breakfast_menu id="42"><food type="discounter"><name>Belgian Waffles</name><price>$5.95</price><description>Two of our famous Belgian Waffles with plenty of real maple syrup</description><calories>650</calories></food></breakfast_menu>')::xml;
+ xmlpretty
+--------------------------------------------------------------------------------------------------
+ <breakfast_menu id="42"> +
+ <food type="discounter"> +
+ <name>Belgian Waffles</name> +
+ <price>$5.95</price> +
+ <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>+
+ <calories>650</calories> +
+ </food> +
+ </breakfast_menu> +
+
+(1 row)
+
+-- XML pretty print: XML string with space, tabs and newline between nodes
+SELECT xmlpretty('<breakfast_menu id="73"> <food type="organic" class="fancy"> <name>Belgian Waffles</name> <price>$15.95</price>
+ <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
+<calories>650</calories> </food> </breakfast_menu> ')::xml;
+ xmlpretty
+--------------------------------------------------------------------------------------------------
+ <breakfast_menu id="73"> +
+ <food type="organic" class="fancy"> +
+ <name>Belgian Waffles</name> +
+ <price>$15.95</price> +
+ <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>+
+ <calories>650</calories> +
+ </food> +
+ </breakfast_menu> +
+
+(1 row)
+
+-- XML pretty print: XML string with space, tabs and newline between nodes, using a namespace
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <meal:price>$15.95</meal:price>
+ <meal:description>Two of our famous Belgian Waffles with plenty of real maple syrup</meal:description>
+<meal:calories>650</meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+ xmlpretty
+------------------------------------------------------------------------------------------------------------
+ <meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; id="73"> +
+ <meal:food type="organic" class="fancy"> +
+ <meal:name>Belgian Waffles</meal:name> +
+ <meal:price>$15.95</meal:price> +
+ <meal:description>Two of our famous Belgian Waffles with plenty of real maple syrup</meal:description>+
+ <meal:calories>650</meal:calories> +
+ </meal:food> +
+ </meal:breakfast_menu> +
+
+(1 row)
+
+-- XML pretty print: XML string with space, tabs and newline between nodes, using multiple namespaces and a comment
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <!-- eat this --> <meal:price>$15.95</meal:price>
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description>
+<meal:calories>650</meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+ xmlpretty
+-------------------------------------------------------------------------------------------------------------
+ <meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73">+
+ <meal:food type="organic" class="fancy"> +
+ <meal:name>Belgian Waffles</meal:name> +
+ <!-- eat this --> +
+ <meal:price>$15.95</meal:price> +
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description> +
+ <meal:calories>650</meal:calories> +
+ </meal:food> +
+ </meal:breakfast_menu> +
+
+(1 row)
+
+-- XML pretty print: XML string with space, tabs and newline between nodes, using multiple namespaces and CDATA
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <meal:price>$15.95</meal:price>
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description>
+<meal:calories><c><![CDATA[<unknown> &"<>!<a>foo</a>]]></c></meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+ xmlpretty
+-------------------------------------------------------------------------------------------------------------
+ <meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73">+
+ <meal:food type="organic" class="fancy"> +
+ <meal:name>Belgian Waffles</meal:name> +
+ <meal:price>$15.95</meal:price> +
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description> +
+ <meal:calories> +
+ <c><![CDATA[<unknown> &"<>!<a>foo</a>]]></c> +
+ </meal:calories> +
+ </meal:food> +
+ </meal:breakfast_menu> +
+
+(1 row)
+
+-- XML pretty print: NULL parameter
+SELECT xmlpretty(NULL)::xml;
+ xmlpretty
+-----------
+
+(1 row)
+
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index 378b412db0..aecec39e05 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1268,3 +1268,48 @@ DETAIL: This functionality requires the server to be built with libxml support.
SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '"<foo/>"', b xml PATH '"<foo/>"');
ERROR: unsupported XML feature
DETAIL: This functionality requires the server to be built with libxml support.
+-- XML pretty print: single line XML string
+SELECT xmlpretty('<breakfast_menu id="42"><food type="discounter"><name>Belgian Waffles</name><price>$5.95</price><description>Two of our famous Belgian Waffles with plenty of real maple syrup</description><calories>650</calories></food></breakfast_menu>')::xml;
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlpretty('<breakfast_menu id="42"><food type="discou...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML pretty print: XML string with space, tabs and newline between nodes
+SELECT xmlpretty('<breakfast_menu id="73"> <food type="organic" class="fancy"> <name>Belgian Waffles</name> <price>$15.95</price>
+ <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
+<calories>650</calories> </food> </breakfast_menu> ')::xml;
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlpretty('<breakfast_menu id="73"> <food type="organ...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML pretty print: XML string with space, tabs and newline between nodes, using a namespace
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <meal:price>$15.95</meal:price>
+ <meal:description>Two of our famous Belgian Waffles with plenty of real maple syrup</meal:description>
+<meal:calories>650</meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fa...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML pretty print: XML string with space, tabs and newline between nodes, using multiple namespaces and a comment
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <!-- eat this --> <meal:price>$15.95</meal:price>
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description>
+<meal:calories>650</meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fa...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML pretty print: XML string with space, tabs and newline between nodes, using multiple namespaces and CDATA
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <meal:price>$15.95</meal:price>
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description>
+<meal:calories><c><![CDATA[<unknown> &"<>!<a>foo</a>]]></c></meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fa...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML pretty print: NULL parameter
+SELECT xmlpretty(NULL)::xml;
+ xmlpretty
+-----------
+
+(1 row)
+
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index ddff459297..6e9a7b2295 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -624,3 +624,30 @@ SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n
\x
SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '"<foo/>"', b xml PATH '"<foo/>"');
+
+
+-- XML pretty print: single line XML string
+SELECT xmlpretty('<breakfast_menu id="42"><food type="discounter"><name>Belgian Waffles</name><price>$5.95</price><description>Two of our famous Belgian Waffles with plenty of real maple syrup</description><calories>650</calories></food></breakfast_menu>')::xml;
+
+-- XML pretty print: XML string with space, tabs and newline between nodes
+SELECT xmlpretty('<breakfast_menu id="73"> <food type="organic" class="fancy"> <name>Belgian Waffles</name> <price>$15.95</price>
+ <description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>
+<calories>650</calories> </food> </breakfast_menu> ')::xml;
+
+-- XML pretty print: XML string with space, tabs and newline between nodes, using a namespace
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <meal:price>$15.95</meal:price>
+ <meal:description>Two of our famous Belgian Waffles with plenty of real maple syrup</meal:description>
+<meal:calories>650</meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+
+-- XML pretty print: XML string with space, tabs and newline between nodes, using multiple namespaces and a comment
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <!-- eat this --> <meal:price>$15.95</meal:price>
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description>
+<meal:calories>650</meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+
+-- XML pretty print: XML string with space, tabs and newline between nodes, using multiple namespaces and CDATA
+SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xmlns:desc="http://fancycafe.mn/meal/"; id="73"> <meal:food type="organic" class="fancy"> <meal:name>Belgian Waffles</meal:name> <meal:price>$15.95</meal:price>
+ <desc:description>Two of our famous Belgian Waffles with plenty of real maple syrup</desc:description>
+<meal:calories><c><![CDATA[<unknown> &"<>!<a>foo</a>]]></c></meal:calories> </meal:food></meal:breakfast_menu>')::xml;
+
+-- XML pretty print: NULL parameter
+SELECT xmlpretty(NULL)::xml;
\ No newline at end of file
--
2.25.1
