On 15.02.23 02:09, Peter Smith wrote:
With v8, in my environment, in psql I see something slightly different:
test_pub=# SET XML OPTION CONTENT;
SET
test_pub=# SELECT xmlformat('');
ERROR: invalid XML document
DETAIL: line 1: switching encoding : no input
line 1: Document is empty
test_pub=# SET XML OPTION DOCUMENT;
SET
test_pub=# SELECT xmlformat('');
ERROR: invalid XML document
LINE 1: SELECT xmlformat('');
^
DETAIL: line 1: switching encoding : no input
line 1: Document is empty
~~
test_pub=# SET XML OPTION CONTENT;
SET
test_pub=# INSERT INTO xmltest VALUES (3, '<wrong');
ERROR: relation "xmltest" does not exist
LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
^
test_pub=# SET XML OPTION DOCUMENT;
SET
test_pub=# INSERT INTO xmltest VALUES (3, '<wrong');
ERROR: relation "xmltest" does not exist
LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
^
~~
Yes... a cfbot also complained about the same thing.
Setting the VERBOSITY to terse might solve this issue:
postgres=# \set VERBOSITY terse
postgres=# SELECT xmlformat('');
ERROR: invalid XML document
postgres=# \set VERBOSITY default
postgres=# SELECT xmlformat('');
ERROR: invalid XML document
DETAIL: line 1: switching encoding : no input
^
line 1: Document is empty
^
v9 wraps the corner test cases with VERBOSITY terse to reduce the error
message output.
Thanks!
Best, Jim
From 2545406a1494e71ca14dbad4ee6fca10e1668754 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 v9] 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 | 45 ++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/xml.out | 102 ++++++++++++++++++++++++++++
src/test/regress/expected/xml_1.out | 54 +++++++++++++++
src/test/regress/sql/xml.sql | 40 +++++++++++
6 files changed, 278 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e09e289a43..a621192425 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14861,6 +14861,40 @@ SELECT xmltable.*
]]></screen>
</para>
</sect3>
+
+ <sect3 id="functions-xml-xmlformat">
+ <title><literal>xmlformat</literal></title>
+
+ <indexterm>
+ <primary>xmlformat</primary>
+ </indexterm>
+
+<synopsis>
+<function>xmlformat</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 xmlformat('<foo id="x"><bar id="y"><var id="z">42</var></bar></foo>');
+ xmlformat
+--------------------------
+ <foo id="x">
+ <bar id="y">
+ <var id="z">42</var>
+ </bar>
+ </foo>
+
+(1 row)
+
+]]></screen>
+ </para>
+ </sect3>
+
</sect2>
<sect2 id="functions-xml-mapping">
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 079bcb1208..ec12707b5c 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -473,6 +473,51 @@ xmlBuffer_to_xmltype(xmlBufferPtr buf)
}
#endif
+Datum
+xmlformat(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+
+ xmlDocPtr doc;
+ xmlChar *xmlbuf = NULL;
+ text *arg = PG_GETARG_TEXT_PP(0);
+ StringInfoData buf;
+ int nbytes;
+
+ doc = xml_parse(arg, XMLOPTION_DOCUMENT, false, GetDatabaseEncoding(), NULL);
+
+ if(!doc)
+ elog(ERROR, "could not parse the given XML document");
+
+ /**
+ * 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);
+
+ xmlFreeDoc(doc);
+
+ if(!nbytes)
+ elog(ERROR, "could not indent the given XML document");
+
+ initStringInfo(&buf);
+ appendStringInfoString(&buf, (const char *)xmlbuf);
+
+ xmlFree(xmlbuf);
+
+ 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..54e8a6262a 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 => 'xmlformat', prorettype => 'xml',
+ proargtypes => 'xml', prosrc => 'xmlformat' },
# json
{ oid => '321', descr => 'I/O',
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 3c357a9c7e..3bc5f40142 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1599,3 +1599,105 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH
<foo/> | <foo/>
(1 row)
+SET XML OPTION DOCUMENT;
+-- XML format: single line XML string
+SELECT xmlformat('<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>');
+ xmlformat
+--------------------------------------------------------------------------------------------------
+ <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 format: XML string with space, tabs and newline between nodes
+SELECT xmlformat('<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> ');
+ xmlformat
+--------------------------------------------------------------------------------------------------
+ <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 format: XML string with space, tabs and newline between nodes, using a namespace
+SELECT xmlformat('<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>');
+ xmlformat
+------------------------------------------------------------------------------------------------------------
+ <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 format: XML string with space, tabs and newline between nodes, using multiple namespaces and a comment
+SELECT xmlformat('<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>');
+ xmlformat
+-------------------------------------------------------------------------------------------------------------
+ <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 format: XML string with space, tabs and newline between nodes, using multiple namespaces and CDATA
+SELECT xmlformat('<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>');
+ xmlformat
+-------------------------------------------------------------------------------------------------------------
+ <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 format: NULL parameter
+SELECT xmlformat(NULL);
+ xmlformat
+-----------
+
+(1 row)
+
+\set VERBOSITY terse
+-- XML format: empty string
+SELECT xmlformat('');
+ERROR: invalid XML document at character 18
+-- XML format: invalid string (whitespaces)
+SELECT xmlformat(' ');
+ERROR: invalid XML document at character 18
+\set VERBOSITY default
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index 378b412db0..e18de278f8 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1268,3 +1268,57 @@ 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.
+SET XML OPTION DOCUMENT;
+-- XML format: single line XML string
+SELECT xmlformat('<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>');
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlformat('<breakfast_menu id="42"><food type="discou...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML format: XML string with space, tabs and newline between nodes
+SELECT xmlformat('<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> ');
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlformat('<breakfast_menu id="73"> <food type="organ...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML format: XML string with space, tabs and newline between nodes, using a namespace
+SELECT xmlformat('<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>');
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlformat('<meal:breakfast_menu xmlns:meal="http://fa...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML format: XML string with space, tabs and newline between nodes, using multiple namespaces and a comment
+SELECT xmlformat('<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>');
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlformat('<meal:breakfast_menu xmlns:meal="http://fa...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML format: XML string with space, tabs and newline between nodes, using multiple namespaces and CDATA
+SELECT xmlformat('<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>');
+ERROR: unsupported XML feature
+LINE 1: SELECT xmlformat('<meal:breakfast_menu xmlns:meal="http://fa...
+ ^
+DETAIL: This functionality requires the server to be built with libxml support.
+-- XML format: NULL parameter
+SELECT xmlformat(NULL);
+ xmlformat
+-----------
+
+(1 row)
+
+\set VERBOSITY terse
+-- XML format: empty string
+SELECT xmlformat('');
+ERROR: unsupported XML feature at character 18
+-- XML format: invalid string (whitespaces)
+SELECT xmlformat(' ');
+ERROR: unsupported XML feature at character 18
+\set VERBOSITY default
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index ddff459297..d2072be4b8 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -624,3 +624,43 @@ 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/>"');
+
+SET XML OPTION DOCUMENT;
+
+-- XML format: single line XML string
+SELECT xmlformat('<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 format: XML string with space, tabs and newline between nodes
+SELECT xmlformat('<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 format: XML string with space, tabs and newline between nodes, using a namespace
+SELECT xmlformat('<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 format: XML string with space, tabs and newline between nodes, using multiple namespaces and a comment
+SELECT xmlformat('<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 format: XML string with space, tabs and newline between nodes, using multiple namespaces and CDATA
+SELECT xmlformat('<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 format: NULL parameter
+SELECT xmlformat(NULL);
+
+
+
+\set VERBOSITY terse
+
+-- XML format: empty string
+SELECT xmlformat('');
+
+-- XML format: invalid string (whitespaces)
+SELECT xmlformat(' ');
+
+\set VERBOSITY default
--
2.25.1
