Subject: [PATCH] Add XMLText function (SQL/XML X038)

From: Jim Jones <[email-masked]>
Date: Sat, 25 Mar 2023 12:49:33 +0100

Hi,

This small patch proposes the implementation of the standard SQL/XML 
function XMLText (X038). It basically converts a text parameter into an 
xml text node. It uses the libxml2 function xmlEncodeSpecialChars[1] to 
escape possible predefined entities.

This patch also contains documentation and regression tests.

Any thoughts?

Best, Jim

1 - 
https://gnome.pages.gitlab.gnome.org/libxml2/devhelp/libxml2-entities.html#xmlEncodeSpecialChars
From 84d6e026724d7e3869f28b09c686e2fc4da67873 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Sat, 25 Mar 2023 12:24:49 +0100
Subject: [PATCH v1] Add XMLText function (SQL/XML X038)

This function implements the standard XMLTest function, which
converts text into xml text nodes. It uses the libxml2 function
xmlEncodeSpecialChars to escape predifined entites (&"<>), so
that those do not cause any conflict when concatenating the text
node output with existing xml documents.

This patch includes also documentation and regression tests.
---
 doc/src/sgml/func.sgml               | 30 +++++++++++++++++++++++
 src/backend/catalog/sql_features.txt |  2 +-
 src/backend/utils/adt/xml.c          | 30 +++++++++++++++++++++++
 src/include/catalog/pg_proc.dat      |  3 +++
 src/test/regress/expected/xml.out    | 36 ++++++++++++++++++++++++++++
 src/test/regress/expected/xml_1.out  | 23 ++++++++++++++++++
 src/test/regress/expected/xml_2.out  | 36 ++++++++++++++++++++++++++++
 src/test/regress/sql/xml.sql         |  7 ++++++
 8 files changed, 166 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a3a13b895f..45195cf05b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14001,6 +14001,36 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
     documents for processing in client applications.
    </para>
 
+  <sect3 id="functions-producing-xml-xmltext">
+    <title><literal>xmltext</literal></title>
+
+    <indexterm>
+     <primary>xmltext</primary>
+    </indexterm>
+
+<synopsis>
+<function>xmltext</function> ( <type>text</type> ) <returnvalue>xml</returnvalue>
+</synopsis>
+
+    <para>
+     The function <function>xmltext</function> returns an XML value with a single
+     text node containing the input argument as its content. Predefined entities
+     like ampersand (<literal><![CDATA[&]]></literal>), left and right angle brackets
+     (<literal><![CDATA[< >]]></literal>), and quotation marks (<literal><![CDATA[""]]></literal>)
+     are escaped.
+    </para>
+
+    <para>
+     Example:
+<screen><![CDATA[
+SELECT xmltext('< foo & bar >');
+         xmltext
+-------------------------
+ &lt; foo &amp; bar &gt;
+]]></screen>
+    </para>
+   </sect3>
+
    <sect3 id="functions-producing-xml-xmlcomment">
     <title><literal>xmlcomment</literal></title>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index bb4c135a7f..82e6ab8258 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -595,7 +595,7 @@ X034	XMLAgg			YES
 X035	XMLAgg: ORDER BY option			YES	
 X036	XMLComment			YES	
 X037	XMLPI			YES	
-X038	XMLText			NO	
+X038	XMLText			YES	
 X040	Basic table mapping			YES	
 X041	Basic table mapping: null absent			YES	
 X042	Basic table mapping: null as nil			YES	
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 15adbd6a01..6621d69219 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -47,6 +47,7 @@
 
 #ifdef USE_LIBXML
 #include <libxml/chvalid.h>
+#include <libxml/entities.h>
 #include <libxml/parser.h>
 #include <libxml/parserInternals.h>
 #include <libxml/tree.h>
@@ -505,6 +506,10 @@ xmlcomment(PG_FUNCTION_ARGS)
 	appendStringInfoText(&buf, arg);
 	appendStringInfoString(&buf, "-->");
 
+
+
+
+
 	PG_RETURN_XML_P(stringinfo_to_xmltype(&buf));
 #else
 	NO_XML_SUPPORT();
@@ -5006,3 +5011,28 @@ XmlTableDestroyOpaque(TableFuncScanState *state)
 	NO_XML_SUPPORT();
 #endif							/* not USE_LIBXML */
 }
+
+Datum
+xmltext(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+
+	text	   *arg = PG_GETARG_TEXT_PP(0);
+	text 	   *result;
+	xmlChar    *xmlbuf = NULL;
+
+	xmlbuf = xmlEncodeSpecialChars(NULL,xml_text2xmlChar(arg));
+
+	Assert(xmlbuf);
+
+	result = cstring_to_text_with_len((const char *) xmlbuf, xmlStrlen(xmlbuf));
+
+	xmlFree(xmlbuf);
+
+	PG_RETURN_XML_P(result);
+
+#else
+	NO_XML_SUPPORT();
+	return 0;
+#endif
+}
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf87aeb2c..293ae66adc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8772,6 +8772,9 @@
 { oid => '2922', descr => 'serialize an XML value to a character string',
   proname => 'text', prorettype => 'text', proargtypes => 'xml',
   prosrc => 'xmltotext' },
+{ oid => '3813', descr => 'generate XML text node',
+  proname => 'xmltext', proisstrict => 't', prorettype => 'xml',
+  proargtypes => 'text', prosrc => 'xmltext' },
 
 { oid => '2923', descr => 'map table contents to XML',
   proname => 'table_to_xml', procost => '100', provolatile => 's',
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 398345ca67..93cbe4edf6 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1785,3 +1785,39 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH
  <foo/> | &lt;foo/&gt;
 (1 row)
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
+SELECT xmltext('foo ´`$_-+?=*^%!|/\()[]{}°');
+          xmltext           
+----------------------------
+ foo ´`$_-+?=*^%!|/\()[]{}°
+(1 row)
+
+SELECT xmltext('foo & <"bar">');
+              xmltext              
+-----------------------------------
+ foo &amp; &lt;&quot;bar&quot;&gt;
+(1 row)
+
+SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+             xmltext             
+---------------------------------
+ x&lt;P&gt;73&lt;/P&gt;0.42truej
+(1 row)
+
diff --git a/src/test/regress/expected/xml_1.out b/src/test/regress/expected/xml_1.out
index 63b779470f..904c665b54 100644
--- a/src/test/regress/expected/xml_1.out
+++ b/src/test/regress/expected/xml_1.out
@@ -1402,3 +1402,26 @@ 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.
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT xmltext('  ');
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT xmltext('foo ´`$_-+?=*^%!|/\()[]{}°');
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT xmltext('foo & <"bar">');
+ERROR:  unsupported XML feature
+DETAIL:  This functionality requires the server to be built with libxml support.
+SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+ERROR:  unsupported XML feature
+LINE 1: SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j':...
+                             ^
+DETAIL:  This functionality requires the server to be built with libxml support.
diff --git a/src/test/regress/expected/xml_2.out b/src/test/regress/expected/xml_2.out
index 43c2558352..0af16dcca4 100644
--- a/src/test/regress/expected/xml_2.out
+++ b/src/test/regress/expected/xml_2.out
@@ -1765,3 +1765,39 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH
  <foo/> | &lt;foo/&gt;
 (1 row)
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
+SELECT xmltext('foo ´`$_-+?=*^%!|/\()[]{}°');
+          xmltext           
+----------------------------
+ foo ´`$_-+?=*^%!|/\()[]{}°
+(1 row)
+
+SELECT xmltext('foo & <"bar">');
+              xmltext              
+-----------------------------------
+ foo &amp; &lt;&quot;bar&quot;&gt;
+(1 row)
+
+SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+             xmltext             
+---------------------------------
+ x&lt;P&gt;73&lt;/P&gt;0.42truej
+(1 row)
+
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index a591eea2e5..c189ba6531 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -660,3 +660,10 @@ 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/>"');
+
+SELECT xmltext(NULL);
+SELECT xmltext('');
+SELECT xmltext('  ');
+SELECT xmltext('foo ´`$_-+?=*^%!|/\()[]{}°');
+SELECT xmltext('foo & <"bar">');
+SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
\ No newline at end of file
-- 
2.25.1

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 25 Mar 2023 12:53:10 +0100

> This patch also contains documentation and regression tests.

+1

Pavel

---

From: Jim Jones <[email-masked]>
Date: Sat, 25 Mar 2023 13:25:23 +0100

On 25.03.23 12:53, Pavel Stehule wrote:
>
> so 25. 3. 2023 v 12:49 odesílatel Jim Jones 
> <[email-masked]> napsal:
>
>     Hi,
>

Thanks!

I just realized that I forgot to add a few examples to my last message :D

postgres=# SELECT xmltext('foo ´/[({bar?})]\`');
       xmltext
--------------------
  foo ´/[({bar?})]\`
(1 row)

postgres=# SELECT xmltext('foo & <bar>');
         xmltext
-----------------------
  foo &amp; &lt;bar&gt;
(1 row)

---

From: Jim Jones <[email-masked]>
Date: Fri, 25 Aug 2023 10:27:08 +0200

> postgres=# SELECT xmltext('foo ´/[({bar?})]\`');
It seems that an encoding issue appears in the regression tests on 
Debian + Meson, 32 bit.

´ > Â´
° > Â°

v2 attached updates the regression tests to fix it.

Jim

From 348e8952de0939c34e40283c7860aa44b66182f5 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Fri, 25 Aug 2023 10:14:24 +0200
Subject: [PATCH v2] Add XMLText function (SQL/XML X038)

 .gitignore                           |  5 ++++
 9 files changed, 171 insertions(+), 1 deletion(-)

diff --git a/.gitignore b/.gitignore
index 4e911395fe..e724ef42a5 100644
--- a/.gitignore
+++ b/.gitignore
@@ -43,3 +43,8 @@ lib*.pc
 /Release/
 /tmp_install/
 /portlock/
+
+
+
+.vscode/
+redeploy-testdb.sh
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7a0d4b9134..2f01a2c25d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14058,6 +14058,36 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
     documents for processing in client applications.
    </para>

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b33065d7bf..680d541673 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -633,7 +633,7 @@ X034	XMLAgg			YES
 X035	XMLAgg: ORDER BY option			YES	
 X036	XMLComment			YES	
 X037	XMLPI			YES	
-X038	XMLText			NO	
+X038	XMLText			YES	
 X040	Basic table mapping			YES	
 X041	Basic table mapping: null absent			YES	
 X042	Basic table mapping: null as nil			YES	
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 866d0d649a..592b804e36 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -47,6 +47,7 @@
 
 #ifdef USE_LIBXML
 
+
+
+
+
index 9805bc6118..ff00c6365d 100644
@@ -8789,6 +8789,9 @@
 
index 398345ca67..13e4296bf8 100644
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
+SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
+         xmltext          
+--------------------------
+ foo `$_-+?=*^%!|/\()[]{}
+(1 row)
+
index 63b779470f..eb9c6f2ed4 100644
+SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
index 43c2558352..c8ed8e0cfa 100644
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
+SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
+         xmltext          
+--------------------------
+ foo `$_-+?=*^%!|/\()[]{}
+(1 row)
+
+SELECT xmltext('foo & <"bar">');
+              xmltext              
+-----------------------------------
+ foo &amp; &lt;&quot;bar&quot;&gt;
+(1 row)
+
+SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+             xmltext             
+---------------------------------
+ x&lt;P&gt;73&lt;/P&gt;0.42truej
+(1 row)
+
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index a591eea2e5..bd4a4e7acd 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -660,3 +660,10 @@ SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n
 \x
 
+SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
2.34.1

---

From: Vik Fearing <[email-masked]>
Date: Fri, 25 Aug 2023 12:05:15 +0200

> This patch also contains documentation and regression tests.

I am replying to this email, but my comments are based on the v2 patch.

Thank you for working on this, and I think this is a valuable addition. 
However, I have two issues with it.

1) There seems to be several spurious blank lines added that I do not 
think are warranted.

2) This patch does nothing to address the <XML returning clause> so we 
can't claim to implement X038 without a disclaimer.  Upon further 
review, the same is true of XMLCOMMENT() so maybe that is okay for this 
patch, and a more comprehensive patch for our xml features is necessary.
-- 
Vik Fearing

---

From: Jim Jones <[email-masked]>
Date: Fri, 25 Aug 2023 14:42:35 +0200

Hi Vik

Thanks for reviewing my patch!

On 25.08.23 12:05, Vik Fearing wrote:
> Thank you for working on this, and I think this is a valuable 
> addition. However, I have two issues with it.
>

I tried to copy the aesthetics of other functions, but it seems I failed 
:) I removed a few blank lines. I hope it's fine now.

Is there any tool like pgindent to take care of it automatically?

> review, the same is true of XMLCOMMENT() so maybe that is okay for 
> this patch, and a more comprehensive patch for our xml features is 
> necessary.

If we decide to not address this point here, I can take a look at it and 
work in a separated patch.

v3 attached.

Thanks

Jim

From 5a5302c8c8a16bf7cf26ade70a40f9e016d23bbf Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Fri, 25 Aug 2023 14:05:39 +0200
Subject: [PATCH v3] Add XMLText function (SQL/XML X038)

 src/backend/utils/adt/xml.c          | 26 ++++++++++++++++++++
 8 files changed, 162 insertions(+), 1 deletion(-)

 
index 866d0d649a..dd8b453b71 100644
 
 #ifdef USE_LIBXML
 
+
+
+
+
@@ -5006,3 +5011,24 @@ XmlTableDestroyOpaque(TableFuncScanState *state)

+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+

---

From: Daniel Gustafsson <[email-masked]>
Date: Fri, 25 Aug 2023 14:44:50 +0200

> On 25 Aug 2023, at 14:42, Jim Jones <[email-masked]> wrote:

> Is there any tool like pgindent to take care of it automatically?

No, pgindent doesn't address whitespace, only indentation of non-whitespace.

--
Daniel Gustafsson

---

From: Vik Fearing <[email-masked]>
Date: Fri, 25 Aug 2023 16:49:50 +0200

> Thanks for reviewing my patch!

Thank you for writing it!

> :) I removed a few blank lines. I hope it's fine now.

I am talking specifically about this:

+
+
+
+

> work in a separated patch.

I do not think this should be addressed in this patch because there are 
quite a lot of functions that need to handle this.
-- 
Vik Fearing

---

From: Jim Jones <[email-masked]>
Date: Fri, 25 Aug 2023 17:40:28 +0200

>      PG_RETURN_XML_P(stringinfo_to_xmltype(&buf));

I have no idea how xmlcomment() got changed in this patch :D nice catch!

>
> I do not think this should be addressed in this patch because there 
> are quite a lot of functions that need to handle this.

v4 attached.

Jim
From ea812791ffdce22d5f5615da361f67c801089f91 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Fri, 25 Aug 2023 14:05:39 +0200
Subject: [PATCH v4] Add XMLText function (SQL/XML X038)

 src/backend/utils/adt/xml.c          | 22 +++++++++++++++++
 8 files changed, 158 insertions(+), 1 deletion(-)

 
index 866d0d649a..7f6984dd88 100644
 
 #ifdef USE_LIBXML
@@ -5006,3 +5007,24 @@ XmlTableDestroyOpaque(TableFuncScanState *state)

+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+

---

From: Chapman Flack <[email-masked]>
Date: Fri, 25 Aug 2023 11:56:40 -0400

On 2023-08-25 10:49, Vik Fearing wrote:
> I do not think this should be addressed in this patch because
> there are quite a lot of functions that need to handle this.

Indeed, as described in [0], we still largely provide the SQL/XML:2003
notion of a single XML datatype, not the distinguishable XML(DOCUMENT),
XML(CONTENT), XML(SEQUENCE) types from :2006 and later, which has a
number of adverse consequences for developers[1], and that wiki page
proposed a couple possible ways forward[2].

Regards,
-Chap

[0] https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards
[1] 
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Obstacles_to_improving_conformance
[2] 
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards#Possible_ways_forward

---

From: Vik Fearing <[email-masked]>
Date: Fri, 25 Aug 2023 18:40:43 +0200

> [0] https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL/XML_Standards

I was not aware of this page.  What a wealth of information!
-- 
Vik Fearing

---

From: Alvaro Herrera <[email-masked]>
Date: Sat, 26 Aug 2023 19:02:02 +0200

> proposed a couple possible ways forward[2].

Sadly, all the projects seem to have been pretty much abandoned in the
meantime.  Zorba has been dead for 9 years, xqilla for 6.  Even XQC, the
API they claim to implement, is dead.

It sounds unlikely that there is *any* way forward here.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"El Maquinismo fue proscrito so pena de cosquilleo hasta la muerte"
(Ijon Tichy en Viajes, Stanislaw Lem)

---

From: Daniel Gustafsson <[email-masked]>
Date: Fri, 3 Nov 2023 16:30:17 +0100

> On 25 Aug 2023, at 17:40, Jim Jones <[email-masked]> wrote:

>> I do not think this should be addressed in this patch because there are quite a lot of functions that need to handle this.
> 
> v4 attached.

I had a look at v4 of this patch and apart from pgindenting and moving the
function within xml.c next to xmlcomment() I think this is ready.

Just like Vik says upthread we can't really claim X038 conformance without a
disclaimer, so I've added a 0002 which adds this to the XML spec conformance
page in the docs.

The attached v5 contains the above mentioned changes.  I've marked this ready
for committer in the CF app as well.

--
Daniel Gustafsson

---

From: Vik Fearing <[email-masked]>
Date: Fri, 3 Nov 2023 16:45:03 +0100

> disclaimer, so I've added a 0002 which adds this to the XML spec conformance

We should put a short version of the disclaimer in sql_features.txt as well.
-- 
Vik Fearing

---

From: Jim Jones <[email-masked]>
Date: Fri, 3 Nov 2023 17:14:47 +0100

Hi Daniel, hi Vik,

Thanks a lot for the review!

> We should put a short version of the disclaimer in sql_features.txt as
You mean to add a disclaimer in the X038 entry? Something along these
lines perhaps?

X038    XMLText            YES     It does not address the <literal><XML
returning clause></literal>, as it is not supported in
<productname>PostgreSQL</productname>.

Jim

---

From: Vik Fearing <[email-masked]>
Date: Fri, 3 Nov 2023 19:05:22 +0100

> <productname>PostgreSQL</productname>.

I was thinking of something much shorter than that.  Such as

     X038    XMLText         YES supported except for RETURNING
-- 
Vik Fearing

---

From: Jim Jones <[email-masked]>
Date: Fri, 3 Nov 2023 21:28:21 +0100

>     X038    XMLText         YES supported except for RETURNING

v6 attached includes this change and the doc addition from Daniel.

Thanks!

--
Jim

From 703c882c254826f10f7bc076a2071741d086e8f6 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Fri, 3 Nov 2023 21:15:21 +0100
Subject: [PATCH v6] Add XMLText function (SQL/XML X038)

This also adds a note in  features.sgml about not supporting
XML(SEQUENCE). The SQL specification defines a RETURNING clause
to a set of XML functions, where RETURNING CONTENT or RETURNING
SEQUENCE can be defined. Since PostgreSQL doesn't support
XML(SEQUENCE) all of these functions operate with an
implicit RETURNING CONTENT.

Author: Jim Jones <[email-masked]>
Reviewed-by: Daniel Gustafsson <[email-masked]>
Reviewed-by: Vik Fearing <[email-masked]>
Discussion: https://postgr.es/m/[email-masked]
---
 doc/src/sgml/features.sgml           |  9 +++++++
 doc/src/sgml/func.sgml               | 30 +++++++++++++++++++++++
 src/backend/catalog/sql_features.txt |  2 +-
 src/backend/utils/adt/xml.c          | 22 +++++++++++++++++
 src/include/catalog/pg_proc.dat      |  3 +++
 src/test/regress/expected/xml.out    | 36 ++++++++++++++++++++++++++++
 src/test/regress/expected/xml_1.out  | 23 ++++++++++++++++++
 src/test/regress/expected/xml_2.out  | 36 ++++++++++++++++++++++++++++
 src/test/regress/sql/xml.sql         |  7 ++++++
 9 files changed, 167 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/features.sgml b/doc/src/sgml/features.sgml
index 575afa3476..966fd39882 100644
--- a/doc/src/sgml/features.sgml
+++ b/doc/src/sgml/features.sgml
@@ -199,6 +199,15 @@
        standard.
       </para>
      </listitem>
+
+     <listitem>
+      <para>
+       <productname>PostgreSQL</productname> does not support the
+       <literal>RETURNING CONTENT</literal> or <literal>RETURNING SEQUENCE</literal>
+       clauses, functions which are defined to have these in the specification
+       are implicitly returning content.
+      </para>
+     </listitem>
     </itemizedlist>
    </para>
 
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a6fcac0824..d963f0a0a0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14180,6 +14180,36 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
     documents for processing in client applications.
    </para>

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b33065d7bf..80c40eaf57 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -633,7 +633,7 @@ X034	XMLAgg			YES
 X035	XMLAgg: ORDER BY option			YES	
 X036	XMLComment			YES	
 X037	XMLPI			YES	
-X038	XMLText			NO	
+X038	XMLText			YES	supported except for RETURNING
 X040	Basic table mapping			YES	
 X041	Basic table mapping: null absent			YES	
 X042	Basic table mapping: null as nil			YES	
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index 2300c7ebf3..c401e7b821 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -47,6 +47,7 @@
 
 #ifdef USE_LIBXML
@@ -513,6 +514,27 @@ xmlcomment(PG_FUNCTION_ARGS)
 }

+Datum
+xmltext(PG_FUNCTION_ARGS)
+{
+#ifdef USE_LIBXML
+	text	   *arg = PG_GETARG_TEXT_PP(0);
+	text	   *result;
+	xmlChar    *xmlbuf = NULL;
+
+	xmlbuf = xmlEncodeSpecialChars(NULL, xml_text2xmlChar(arg));
+
+	Assert(xmlbuf);
+
+	result = cstring_to_text_with_len((const char *) xmlbuf, xmlStrlen(xmlbuf));
+	xmlFree(xmlbuf);
+	PG_RETURN_XML_P(result);
+#else
+	NO_XML_SUPPORT();
+	return 0;
+#endif							/* not USE_LIBXML */
+}
+
 
 /*
  * TODO: xmlconcat needs to merge the notations and unparsed entities
index 091f7e343c..f14aed422a 100644
@@ -8793,6 +8793,9 @@

+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+
 
+SELECT xmltext(NULL);
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('');
+ xmltext 
+---------
+ 
+(1 row)
+
+SELECT xmltext('  ');
+ xmltext 
+---------
+   
+(1 row)
+

---

From: Vik Fearing <[email-masked]>
Date: Sat, 4 Nov 2023 15:01:34 +0100

> v6 attached includes this change and the doc addition from Daniel.

There are some typos in the commit message, but otherwise this looks 
commitable to me.
-- 
Vik Fearing

---

From: Daniel Gustafsson <[email-masked]>
Date: Mon, 6 Nov 2023 11:49:28 +0100

> On 4 Nov 2023, at 15:01, Vik Fearing <[email-masked]> wrote:
> 
> There are some typos in the commit message, but otherwise this looks commitable to me.

I took another look at this today, fixes the above mentioned typos and some
tiny cosmetic things and pushed it.

--
Daniel Gustafsson

---

From: Jim Jones <[email-masked]>
Date: Mon, 6 Nov 2023 12:15:58 +0100

> tiny cosmetic things and pushed it.
Awesome! Thanks Daniel and Vik for reviewing and pushing this patch :)

-- 
Jim