SELECT xmlpretty('<foo>')::xml;
^
-DETAIL: line 1: chunk is not well balanced
+DETAIL: line 1: Premature end of data in tag foo line 1Test removed in v2. On 02.02.23 21:35, Jim Jones wrote:
Hi,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.postgres=# 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) The patch also contains regression tests and documentation. Feedback is very welcome! Jim
From ced9fccddc033de98709a6e93dc6530ce68149db 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 v2 1/2] 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 | 30 +++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/xml.out | 107 ++++++++++++++++++++++++++++++
src/test/regress/sql/xml.sql | 34 ++++++++++
5 files changed, 208 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..6409133137 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -473,6 +473,36 @@ xmlBuffer_to_xmltype(xmlBufferPtr buf)
}
#endif
+Datum
+xmlpretty(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+
+ xmlDocPtr doc;
+ xmlChar *buf = NULL;
+ text *arg = PG_GETARG_TEXT_PP(0);
+
+ doc = xml_parse(arg, XMLOPTION_DOCUMENT, false, GetDatabaseEncoding(), NULL);
+
+ /**
+ * xmlDocDumpFormatMemory (
+ * xmlDocPtr doc, # the XML document.
+ * xmlChar ** buf, # buffer where the formatted XML document will be stored.
+ * int *size, # this could store the size of the created buffer
+ * but as we do not need it, we can leave it NULL.
+ * int format) # 1 = node indenting.
+ */
+ xmlDocDumpFormatMemory(doc, &buf, NULL, 1);
+
+ xmlFreeDoc(doc);
+ PG_RETURN_XML_P(cstring_to_xmltype((char*)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..98a338ad8d 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1599,3 +1599,110 @@ 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: invalid XML string (not well balanced)
+SELECT xmlpretty('<foo>')::xml;
+ERROR: invalid XML content
+LINE 1: SELECT xmlpretty('<foo>')::xml;
+ ^
+DETAIL: line 1: chunk is not well balanced
+<foo>
+ ^
+-- XML pretty print: invalid parameter
+SELECT xmlpretty(42)::xml;
+ERROR: function xmlpretty(integer) does not exist
+LINE 1: SELECT xmlpretty(42)::xml;
+ ^
+HINT: No function matches the given name and argument types. You might need to add explicit type casts.
+-- 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..2b40c90966 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -624,3 +624,37 @@ 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: invalid XML string (not well balanced)
+SELECT xmlpretty('<foo>')::xml;
+
+-- XML pretty print: invalid parameter
+SELECT xmlpretty(42)::xml;
+
+-- XML pretty print: NULL parameter
+SELECT xmlpretty(NULL)::xml;
+
--
2.25.1
From ceb24fcbc55e94a69968432f7a0d93e9e240cd2d Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Fri, 3 Feb 2023 07:48:42 +0100
Subject: [PATCH v2 2/2] Remove unecessary regression tests
The removed removed tests (corner cases) were unnecessray and were
causing the cfbot to fail, as the system is delivering different
error messages in linux (chunk is not well balanced) and windows /
macos (Premature end of data in tag foo line 1).
---
src/test/regress/expected/xml.out | 14 --------------
src/test/regress/sql/xml.sql | 9 +--------
2 files changed, 1 insertion(+), 22 deletions(-)
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 98a338ad8d..afaa83941b 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1685,20 +1685,6 @@ SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xm
(1 row)
--- XML pretty print: invalid XML string (not well balanced)
-SELECT xmlpretty('<foo>')::xml;
-ERROR: invalid XML content
-LINE 1: SELECT xmlpretty('<foo>')::xml;
- ^
-DETAIL: line 1: chunk is not well balanced
-<foo>
- ^
--- XML pretty print: invalid parameter
-SELECT xmlpretty(42)::xml;
-ERROR: function xmlpretty(integer) does not exist
-LINE 1: SELECT xmlpretty(42)::xml;
- ^
-HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-- XML pretty print: NULL parameter
SELECT xmlpretty(NULL)::xml;
xmlpretty
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 2b40c90966..6e9a7b2295 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -649,12 +649,5 @@ SELECT xmlpretty('<meal:breakfast_menu xmlns:meal="http://fancycafe.im/meal/"; xm
<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: invalid XML string (not well balanced)
-SELECT xmlpretty('<foo>')::xml;
-
--- XML pretty print: invalid parameter
-SELECT xmlpretty(42)::xml;
-
-- XML pretty print: NULL parameter
-SELECT xmlpretty(NULL)::xml;
-
+SELECT xmlpretty(NULL)::xml;
\ No newline at end of file
--
2.25.1
smime.p7s
Description: S/MIME Cryptographic Signature
