public inbox for [email protected]
help / color / mirror / Atom feedFrom: Marcos Magueta <[email protected]>
To: Jim Jones <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: WIP - xmlvalidate implementation from TODO list
Date: Sat, 10 Jan 2026 02:26:11 -0300
Message-ID: <CAN3aFCe_cBshj0rb7J8yoT+fRHOBOZmk-m8V7DMLDe0ZjSgjcA@mail.gmail.com> (raw)
In-Reply-To: <CAN3aFCcXwS7BrU1gHRUEBH3G59EVf_7LUhLeEWqW2Sc9Vk5k-A@mail.gmail.com>
References: <CAN3aFCdx8AapWSVpJ1kaC7OC_v7QwbjgbGw9WfPBBY2GMyOadQ@mail.gmail.com>
<CALdSSPjxLU+zhWx+CgwN+VHoHTso33trY6mse1A6Jks7hWAdrA@mail.gmail.com>
<CAN3aFCesNDiL-iZg4imC0n+NgT3JywqZYkuGH83u8ssLjJ-p5Q@mail.gmail.com>
<CAN3aFCfvVgXr77o=dB_E2kSCY+EgckSQbSBdd_N9n-LauWuQLw@mail.gmail.com>
<CAN3aFCcx_w5Ldb+SYurwd31es9hOJqLuKARQHHDOk7+5iOqBWQ@mail.gmail.com>
<CALdSSPhFzYCp=Aa8AAboz6TQaTmjWciQGfrEJQeOOO+0pD1GGw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAN3aFCfdGp6TGTQNOVO1im1u2vO_E2jnTGVV2xhea7eNY7GtuQ@mail.gmail.com>
<[email protected]>
<CAN3aFCcXwS7BrU1gHRUEBH3G59EVf_7LUhLeEWqW2Sc9Vk5k-A@mail.gmail.com>
Hello there, Postgres hackers!
Since we last talked I tried to actually implement a catalog for XML
schemas. It was a bit challenging personally.
I particularly had an issue with SYSCACHE so I can search the xmlschema
entries. I mostly tried to follow examples around the codebase (specially
when it comes to security, which I mostly mirrored from sequences), so if I
am misusing something, that's likely the problem, so please point that out.
Some highlights of the changes are:
What's implemented so far:
- New pg_xmlschema catalog (OIDs 6434 to 6438) for storing XML schema
definitions
- The validation function itself: XMLVALIDATE(DOCUMENT xml_expr ACCORDING
TO XMLSCHEMA xml_schema_name)
- DDL support: CREATE XMLSCHEMA, DROP XMLSCHEMA, ALTER XMLSCHEMA (with IF
NOT EXISTS, RENAME, SET SCHEMA, OWNER TO)
- Two syscaches (XMLSCHEMAOID and XMLSCHEMANAMENSP) for lookups during
parsing and execution
- Dependency tracking so schemas can't be dropped while views/rules
reference them (I think I even added a test for it, but it's worth checking
more closely)
- Integration with PostgreSQL's object system (event triggers, security
labels, permissions) -- I was mostly following what was in place already
and adding what seemed necessary
- Grants and role permissions
The flow is the following: schemas are registered via CREATE XMLSCHEMA,
then at parse time the schema name gets resolved to an OID (with
dependencies recorded), and finally at execution time the schema is fetched
by OID and used for validation. Please note that I only implemented
DOCUMENT mode for now, as it seemed like the most common use case and kept
things simpler. CONTENT mode by the standard seems to require another
keyword to make things useful (ELEMENT), which is a bit problematic since
it's a fairly used word to conflict on any database (it broke on the
current tests when I ran already, so...). With the ELEMENT we could do
things like: XMLVALIDATE(CONTENT '<author>C. J. Date</author>' ACCORDING TO
XMLSCHEMA book ELEMENT author), which is fairly powerful if you want to
validate just parts of the xml document, but again, it's scrapped from this
version because I wanted to pull my hair out dealing with it.
Following the SQL/XML standard, XMLVALIDATE returns the validated XML, not
a boolean anymore, and raises ERROR on validation failure. So now we can do
things like INSERT INTO r VALUES (XMLVALIDATE(...)) and other cool
compositions.
The documentation is still tracking the old xmlvalidate behavior and
nothing about the catalog changes.I can fix all that before submission if
the overall approach looks reasonable.
Permissions are a bit trickier, I thought of just relying on USAGE, since
the question for an XML validation is "Can I use this XML schema in a
call?". I think FDW works like that currently. I tried adding a bunch of
examples on xml.sql.
One change I specifically wanted feedback on are the changes on
parse_expr.c. Since I need to resolve the name of the schema, I thought
doing so at parsing time was the correct approach. In there I extract
schema name before the ResTarget loop runs, resolve it to OID using
get_xmlschema_oid(), save the string form in newx->name for deparsing (so
views can be displayed correctly) and finally clear x->named_args = NIL so
the ResTarget loop below has nothing to process. This approach reuses the
existing named_args field as temporary storage during parsing, then
converts it properly during transformation. It felt strange, but it avoids
adding another field to the XmlExpr structure just for XMLVALIDATE.
Also, I made the variables that get modified inside of the PG_TRY block
volatile as it seems to be a pattern likely to force the compiler to do
memory access instead of relying on registers during jumps.
I'm attaching the patch for another round of review (please excuse the lack
of the linter, I tried running but had some issues). Any feedback would be
really appreciated, this was quite an adventure.
PS: I noticed now that I haven't copied the changes over to xml1.out nor
xml2.out. But This is just for the feature validation round, so I can fix
that later.
Thanks for taking a look!
Attachments:
[application/octet-stream] xmlschema_xmlvalidate.patch (233.3K, 3-xmlschema_xmlvalidate.patch)
download | inline diff:
From 2cd28c96760de23fad5d1766ead255c1a76cbc43 Mon Sep 17 00:00:00 2001
From: Marcos Magueta <[email protected]>
Date: Sat, 10 Jan 2026 02:05:54 -0300
Subject: [PATCH] pg_xmlschema catalog and xmlvalidate
---
doc/src/sgml/func/func-xml.sgml | 119 +
src/backend/catalog/aclchk.c | 17 +
src/backend/catalog/dependency.c | 31 +
src/backend/catalog/meson.build | 1 +
src/backend/catalog/namespace.c | 56 +
src/backend/catalog/objectaddress.c | 84 +
src/backend/catalog/pg_xmlschema.c | 191 ++
src/backend/commands/alter.c | 8 +
src/backend/commands/dropcmds.c | 7 +
src/backend/commands/event_trigger.c | 2 +
src/backend/commands/meson.build | 1 +
src/backend/commands/seclabel.c | 1 +
src/backend/commands/xmlschemacmds.c | 119 +
src/backend/executor/execExprInterp.c | 27 +
src/backend/parser/gram.y | 83 +-
src/backend/parser/parse_expr.c | 42 +
src/backend/parser/parse_target.c | 3 +
src/backend/tcop/utility.c | 17 +
src/backend/utils/adt/acl.c | 4 +
src/backend/utils/adt/ruleutils.c | 16 +-
src/backend/utils/adt/xml.c | 141 +-
src/include/catalog/meson.build | 1 +
src/include/catalog/namespace.h | 1 +
src/include/catalog/pg_xmlschema.h | 43 +
src/include/commands/xmlschemacmds.h | 10 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/primnodes.h | 1 +
src/include/parser/kwlist.h | 3 +
src/include/tcop/cmdtaglist.h | 3 +
src/include/utils/acl.h | 1 +
src/include/utils/xml.h | 1 +
src/test/regress/expected/xml.out | 4063 +++++++++++++------------
src/test/regress/sql/xml.sql | 274 ++
33 files changed, 3482 insertions(+), 1890 deletions(-)
create mode 100644 src/backend/catalog/pg_xmlschema.c
create mode 100644 src/backend/commands/xmlschemacmds.c
create mode 100644 src/include/catalog/pg_xmlschema.h
create mode 100644 src/include/commands/xmlschemacmds.h
diff --git a/doc/src/sgml/func/func-xml.sgml b/doc/src/sgml/func/func-xml.sgml
index 511bc90852a..b02db6c9b5b 100644
--- a/doc/src/sgml/func/func-xml.sgml
+++ b/doc/src/sgml/func/func-xml.sgml
@@ -1010,6 +1010,125 @@ SELECT xmltable.*
]]></screen>
</para>
</sect3>
+
+ <sect3 id="functions-xml-processing-xmlvalidate">
+ <title><literal>xmlvalidate</literal></title>
+
+ <indexterm>
+ <primary>xmlvalidate</primary>
+ </indexterm>
+
+<synopsis>
+<function>XMLVALIDATE</function> ( {<literal>DOCUMENT</literal>|<literal>CONTENT</literal>} <replaceable>xml_value</replaceable> <literal>ACCORDING TO XMLSCHEMA</literal> <replaceable>schema_text</replaceable> ) <returnvalue>boolean</returnvalue>
+</synopsis>
+
+ <para>
+ The <function>xmlvalidate</function> function validates an XML value
+ against an XML Schema (XSD). It returns <literal>true</literal> if the
+ XML is valid according to the schema, <literal>false</literal> if it is
+ invalid, or <literal>NULL</literal> if either argument is
+ <literal>NULL</literal>.
+ </para>
+
+ <para>
+ The first argument specifies whether to validate the XML as a
+ <literal>DOCUMENT</literal> (a complete XML document with a single root
+ element) or as <literal>CONTENT</literal> (an XML content fragment).
+ </para>
+
+ <para>
+ The <replaceable>schema_text</replaceable> argument should be a
+ <type>text</type> value containing a valid XML Schema Definition (XSD).
+ For security reasons, the schema is treated as plain text and parsed
+ in-memory only. This prevents malicious imports or external file access
+ through schema location references (such as <literal>xs:import</literal>,
+ <literal>xs:include</literal>, or <literal>schemaLocation</literal>
+ attributes). Any attempts to reference external resources will be
+ ignored, resulting in an empty schema reference.
+ </para>
+
+ <para>
+ Examples:
+<screen><![CDATA[
+SELECT xmlvalidate(DOCUMENT '<person><name>John</name><age>30</age></person>'
+ ACCORDING TO XMLSCHEMA '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="person">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="age" type="xs:integer"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>');
+
+ xmlvalidate
+-------------
+ t
+(1 row)
+]]></screen>
+ </para>
+
+ <para>
+ This example shows a validation failure due to a missing required element:
+<screen><![CDATA[
+SELECT xmlvalidate(DOCUMENT '<person><name>John</name></person>'
+ ACCORDING TO XMLSCHEMA '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="person">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="age" type="xs:integer"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>');
+
+ xmlvalidate
+-------------
+ f
+(1 row)
+]]></screen>
+ </para>
+
+ <para>
+ The schema can also validate attributes:
+<screen><![CDATA[
+SELECT xmlvalidate(DOCUMENT '<product id="123"><name>Widget</name><price>9.99</price></product>'
+ ACCORDING TO XMLSCHEMA '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="product">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="price" type="xs:decimal"/>
+ </xs:sequence>
+ <xs:attribute name="id" type="xs:string" use="required"/>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>');
+
+ xmlvalidate
+-------------
+ t
+(1 row)
+]]></screen>
+ </para>
+
+ <note>
+ <para>
+ For security, <function>xmlvalidate</function> processes the XML Schema
+ as an in-memory text value. External resource references such as
+ <literal>schemaLocation</literal> in <literal>xs:import</literal> or
+ <literal>xs:include</literal> directives are not followed and are
+ treated as empty schema references. This design prevents potential
+ security vulnerabilities where malicious schemas could attempt to
+ access external files or network resources.
+ </para>
+ </note>
+ </sect3>
</sect2>
<sect2 id="functions-xml-mapping">
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index a431fc0926f..c5198ea6ec6 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -64,6 +64,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_xmlschema.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
#include "commands/extension.h"
@@ -290,6 +291,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
case OBJECT_PARAMETER_ACL:
whole_mask = ACL_ALL_RIGHTS_PARAMETER_ACL;
break;
+ case OBJECT_XMLSCHEMA:
+ whole_mask = ACL_ALL_RIGHTS_XMLSCHEMA;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", objtype);
/* not reached, but keep compiler quiet */
@@ -534,6 +538,10 @@ ExecuteGrantStmt(GrantStmt *stmt)
all_privileges = ACL_ALL_RIGHTS_PARAMETER_ACL;
errormsg = gettext_noop("invalid privilege type %s for parameter");
break;
+ case OBJECT_XMLSCHEMA:
+ all_privileges = ACL_ALL_RIGHTS_XMLSCHEMA;
+ errormsg = gettext_noop("invalid privilege type %s for XML schema");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) stmt->objtype);
@@ -639,6 +647,9 @@ ExecGrantStmt_oids(InternalGrant *istmt)
case OBJECT_PARAMETER_ACL:
ExecGrant_Parameter(istmt);
break;
+ case OBJECT_XMLSCHEMA:
+ ExecGrant_common(istmt, XmlSchemaRelationId, ACL_ALL_RIGHTS_XMLSCHEMA, NULL);
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) istmt->objtype);
@@ -2677,6 +2688,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_CONVERSION:
msg = gettext_noop("permission denied for conversion %s");
break;
+ case OBJECT_XMLSCHEMA:
+ msg = gettext_noop("permission denied for XML schema %s");
+ break;
case OBJECT_DATABASE:
msg = gettext_noop("permission denied for database %s");
break;
@@ -2809,6 +2823,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_CONVERSION:
msg = gettext_noop("must be owner of conversion %s");
break;
+ case OBJECT_XMLSCHEMA:
+ msg = gettext_noop("must be owner of XML schema %s");
+ break;
case OBJECT_DATABASE:
msg = gettext_noop("must be owner of database %s");
break;
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index f89267f0342..50ce3871e78 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -66,6 +66,7 @@
#include "catalog/pg_ts_template.h"
#include "catalog/pg_type.h"
#include "catalog/pg_user_mapping.h"
+#include "catalog/pg_xmlschema.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
@@ -79,6 +80,7 @@
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/primnodes.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteRemove.h"
#include "storage/lmgr.h"
@@ -1514,6 +1516,7 @@ doDeletion(const ObjectAddress *object, int flags)
case EventTriggerRelationId:
case TransformRelationId:
case AuthMemRelationId:
+ case XmlSchemaRelationId:
DropObjectById(object);
break;
@@ -2416,6 +2419,34 @@ find_expr_references_walker(Node *node,
context->addrs);
/* fall through to examine arguments */
}
+ else if (IsA(node, XmlExpr))
+ {
+ XmlExpr *xmlexpr = (XmlExpr *) node;
+
+ /*
+ * XMLVALIDATE's second argument is a Const containing the schema OID.
+ * Record a dependency on it.
+ */
+ if (xmlexpr->op == IS_XMLVALIDATE && list_length(xmlexpr->args) == 2)
+ {
+ Node *schema_arg = (Node *) lsecond(xmlexpr->args);
+
+ if (IsA(schema_arg, Const))
+ {
+ Const *schema_const = (Const *) schema_arg;
+ Oid schema_oid;
+
+ if (!schema_const->constisnull &&
+ schema_const->consttype == OIDOID)
+ {
+ schema_oid = DatumGetObjectId(schema_const->constvalue);
+ add_object_address(XmlSchemaRelationId, schema_oid, 0,
+ context->addrs);
+ }
+ }
+ }
+ /* fall through to examine arguments */
+ }
return expression_tree_walker(node, find_expr_references_walker,
context);
diff --git a/src/backend/catalog/meson.build b/src/backend/catalog/meson.build
index 11d21c5ad6b..9ba15e71b72 100644
--- a/src/backend/catalog/meson.build
+++ b/src/backend/catalog/meson.build
@@ -33,6 +33,7 @@ backend_sources += files(
'pg_subscription.c',
'pg_tablespace.c',
'pg_type.c',
+ 'pg_xmlschema.c',
'storage.c',
'toasting.c',
)
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index c3b79a2ba48..89e87a06572 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -30,6 +30,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_conversion.h"
#include "catalog/pg_database.h"
+#include "catalog/pg_xmlschema.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
@@ -4143,6 +4144,61 @@ get_conversion_oid(List *conname, bool missing_ok)
return conoid;
}
+/*
+ * get_xmlschema_oid - find an XML schema by possibly qualified name
+ */
+Oid
+get_xmlschema_oid(List *schemaname, bool missing_ok)
+{
+ char *nspname;
+ char *schema_name;
+ Oid namespaceId;
+ Oid schema_oid = InvalidOid;
+ ListCell *l;
+
+ /* deconstruct the name list */
+ DeconstructQualifiedName(schemaname, &nspname, &schema_name);
+
+ if (nspname)
+ {
+ /* use exact schema given */
+ namespaceId = LookupExplicitNamespace(nspname, missing_ok);
+ if (missing_ok && !OidIsValid(namespaceId))
+ schema_oid = InvalidOid;
+ else
+ schema_oid = GetSysCacheOid2(XMLSCHEMANAMENSP, Anum_pg_xmlschema_oid,
+ PointerGetDatum(schema_name),
+ ObjectIdGetDatum(namespaceId));
+ }
+ else
+ {
+ /* search for it in search path */
+ recomputeNamespacePath();
+
+ foreach(l, activeSearchPath)
+ {
+ namespaceId = lfirst_oid(l);
+
+ if (namespaceId == myTempNamespace)
+ continue; /* do not look in temp namespace */
+
+ schema_oid = GetSysCacheOid2(XMLSCHEMANAMENSP, Anum_pg_xmlschema_oid,
+ PointerGetDatum(schema_name),
+ ObjectIdGetDatum(namespaceId));
+ if (OidIsValid(schema_oid))
+ return schema_oid;
+ }
+ }
+
+ /* Not found in path */
+ if (!OidIsValid(schema_oid) && !missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("XML schema \"%s\" does not exist",
+ NameListToString(schemaname))));
+ return schema_oid;
+}
+
/*
* FindDefaultConversionProc - find default encoding conversion proc
*/
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 02af64b82c6..6306b9c8f6e 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -62,6 +62,7 @@
#include "catalog/pg_ts_template.h"
#include "catalog/pg_type.h"
#include "catalog/pg_user_mapping.h"
+#include "catalog/pg_xmlschema.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
#include "commands/extension.h"
@@ -187,6 +188,20 @@ static const ObjectPropertyType ObjectProperty[] =
OBJECT_COLLATION,
true
},
+ {
+ "xmlschema",
+ XmlSchemaRelationId,
+ XmlSchemaOidIndexId,
+ XMLSCHEMAOID,
+ XMLSCHEMANAMENSP,
+ Anum_pg_xmlschema_oid,
+ Anum_pg_xmlschema_schemaname,
+ Anum_pg_xmlschema_schemanamespace,
+ Anum_pg_xmlschema_schemaowner,
+ Anum_pg_xmlschema_schemaacl,
+ OBJECT_XMLSCHEMA,
+ true
+ },
{
"constraint",
ConstraintRelationId,
@@ -720,6 +735,9 @@ static const struct object_type_map
{
"collation", OBJECT_COLLATION
},
+ {
+ "xmlschema", OBJECT_XMLSCHEMA
+ },
{
"table constraint", OBJECT_TABCONSTRAINT
},
@@ -1029,6 +1047,11 @@ get_object_address(ObjectType objtype, Node *object,
address.objectId = get_collation_oid(castNode(List, object), missing_ok);
address.objectSubId = 0;
break;
+ case OBJECT_XMLSCHEMA:
+ address.classId = XmlSchemaRelationId;
+ address.objectId = get_xmlschema_oid(castNode(List, object), missing_ok);
+ address.objectSubId = 0;
+ break;
case OBJECT_CONVERSION:
address.classId = ConversionRelationId;
address.objectId = get_conversion_oid(castNode(List, object), missing_ok);
@@ -2282,6 +2305,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
case OBJECT_COLUMN:
case OBJECT_ATTRIBUTE:
case OBJECT_COLLATION:
+ case OBJECT_XMLSCHEMA:
case OBJECT_CONVERSION:
case OBJECT_STATISTIC_EXT:
case OBJECT_TSPARSER:
@@ -2460,6 +2484,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
strVal(object));
break;
case OBJECT_COLLATION:
+ case OBJECT_XMLSCHEMA:
case OBJECT_CONVERSION:
case OBJECT_OPCLASS:
case OBJECT_OPFAMILY:
@@ -4067,6 +4092,34 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok)
break;
}
+ case XmlSchemaRelationId:
+ {
+ HeapTuple schemaTup;
+ Form_pg_xmlschema schema;
+ char *nspname;
+
+ schemaTup = SearchSysCache1(XMLSCHEMAOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(schemaTup))
+ {
+ if (!missing_ok)
+ elog(ERROR, "cache lookup failed for XML schema %u",
+ object->objectId);
+ break;
+ }
+
+ schema = (Form_pg_xmlschema) GETSTRUCT(schemaTup);
+
+ /* Qualify the name if not visible in search path */
+ nspname = get_namespace_name(schema->schemanamespace);
+
+ appendStringInfo(&buffer, _("XML schema %s"),
+ quote_qualified_identifier(nspname,
+ NameStr(schema->schemaname)));
+ ReleaseSysCache(schemaTup);
+ break;
+ }
+
default:
elog(ERROR, "unsupported object class: %u", object->classId);
}
@@ -4669,6 +4722,10 @@ getObjectTypeDescription(const ObjectAddress *object, bool missing_ok)
appendStringInfoString(&buffer, "transform");
break;
+ case XmlSchemaRelationId:
+ appendStringInfoString(&buffer, "XML schema");
+ break;
+
default:
elog(ERROR, "unsupported object class: %u", object->classId);
}
@@ -6019,6 +6076,33 @@ getObjectIdentityParts(const ObjectAddress *object,
}
break;
+ case XmlSchemaRelationId:
+ {
+ HeapTuple schemaTup;
+ Form_pg_xmlschema schema;
+ char *nspname;
+
+ schemaTup = SearchSysCache1(XMLSCHEMAOID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(schemaTup))
+ {
+ if (!missing_ok)
+ elog(ERROR, "cache lookup failed for XML schema %u",
+ object->objectId);
+ break;
+ }
+ schema = (Form_pg_xmlschema) GETSTRUCT(schemaTup);
+ nspname = get_namespace_name_or_temp(schema->schemanamespace);
+ appendStringInfoString(&buffer,
+ quote_qualified_identifier(nspname,
+ NameStr(schema->schemaname)));
+ if (objname)
+ *objname = list_make2(nspname,
+ pstrdup(NameStr(schema->schemaname)));
+ ReleaseSysCache(schemaTup);
+ break;
+ }
+
default:
elog(ERROR, "unsupported object class: %u", object->classId);
}
diff --git a/src/backend/catalog/pg_xmlschema.c b/src/backend/catalog/pg_xmlschema.c
new file mode 100644
index 00000000000..4d44a4ef10b
--- /dev/null
+++ b/src/backend/catalog/pg_xmlschema.c
@@ -0,0 +1,191 @@
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "access/table.h"
+#include "catalog/catalog.h"
+#include "catalog/dependency.h"
+#include "catalog/indexing.h"
+#include "catalog/objectaccess.h"
+#include "catalog/pg_xmlschema.h"
+#include "catalog/pg_namespace.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "utils/xml.h"
+
+#ifdef USE_LIBXML
+#include <libxml/xmlschemas.h>
+#endif
+
+/*
+ * XmlSchemaCreate
+ *
+ * Add a new tuple to pg_xmlschema.
+ *
+ * if_not_exists: if true, don't fail on duplicate name, just print a notice
+ * and return InvalidOid.
+ * quiet: if true, don't fail on duplicate name, just silently return
+ * InvalidOid (which overides if_not_exists).
+ */
+Oid
+XmlSchemaCreate(const char *schemaname,
+ Oid schemanamespace,
+ Oid schemaowner,
+ const char *schemadata,
+ bool if_not_exists,
+ bool quiet)
+{
+ Relation rel;
+ TupleDesc tupDesc;
+ HeapTuple tup;
+ Datum values[Natts_pg_xmlschema];
+ bool nulls[Natts_pg_xmlschema];
+ NameData name_name;
+ Oid oid;
+ ObjectAddress myself,
+ referenced;
+
+ Assert(schemaname);
+ Assert(schemanamespace);
+ Assert(schemaowner);
+ Assert(schemadata);
+
+#ifdef USE_LIBXML
+ /* Validate the XML Schema before storing it */
+ {
+ xmlSchemaParserCtxtPtr parser_ctxt;
+ xmlSchemaPtr schema_ptr;
+ PgXmlErrorContext *xmlerrcxt;
+
+ xmlerrcxt = pg_xml_init(PG_XML_STRICTNESS_WELLFORMED);
+
+ PG_TRY();
+ {
+ parser_ctxt = xmlSchemaNewMemParserCtxt(schemadata, strlen(schemadata));
+ if (parser_ctxt == NULL)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT,
+ "failed to create schema parser context");
+
+ schema_ptr = xmlSchemaParse(parser_ctxt);
+ if (schema_ptr == NULL)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT,
+ "invalid XML schema definition");
+
+ /* Clean up */
+ xmlSchemaFree(schema_ptr);
+ xmlSchemaFreeParserCtxt(parser_ctxt);
+ }
+ PG_CATCH();
+ {
+ pg_xml_done(xmlerrcxt, true);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ pg_xml_done(xmlerrcxt, false);
+ }
+#else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("xmlschema support requires libxml")));
+#endif
+
+ /*
+ * Make sure there is no existing XML schema of same name in the namespace.
+ *
+ * This would be caught by the unique index anyway; we're just giving a
+ * friendlier error message. The unique index provides a backstop against
+ * race conditions.
+ */
+ oid = GetSysCacheOid2(XMLSCHEMANAMENSP,
+ Anum_pg_xmlschema_oid,
+ PointerGetDatum(schemaname),
+ ObjectIdGetDatum(schemanamespace));
+ if (OidIsValid(oid))
+ {
+ if (quiet)
+ return InvalidOid;
+ else if (if_not_exists)
+ {
+ /*
+ * If we are in an extension script, insist that the pre-existing
+ * object be a member of the extension, to avoid security risks.
+ */
+ ObjectAddressSet(myself, XmlSchemaRelationId, oid);
+ checkMembershipInCurrentExtension(&myself);
+
+ /* OK to skip */
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("XML schema \"%s\" already exists, skipping",
+ schemaname)));
+ return InvalidOid;
+ }
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("XML schema \"%s\" already exists",
+ schemaname)));
+ }
+
+ /* open pg_xmlschema; lock to protect against concurrent changes */
+ rel = table_open(XmlSchemaRelationId, ShareRowExclusiveLock);
+
+ tupDesc = RelationGetDescr(rel);
+
+ /* form a tuple */
+ memset(nulls, 0, sizeof(nulls));
+
+ namestrcpy(&name_name, schemaname);
+ oid = GetNewOidWithIndex(rel, XmlSchemaOidIndexId,
+ Anum_pg_xmlschema_oid);
+ values[Anum_pg_xmlschema_oid - 1] = ObjectIdGetDatum(oid);
+ values[Anum_pg_xmlschema_schemaname - 1] = NameGetDatum(&name_name);
+ values[Anum_pg_xmlschema_schemanamespace - 1] = ObjectIdGetDatum(schemanamespace);
+ values[Anum_pg_xmlschema_schemaowner - 1] = ObjectIdGetDatum(schemaowner);
+ values[Anum_pg_xmlschema_schemadata - 1] = CStringGetTextDatum(schemadata);
+
+ /* Set up default ACL */
+ {
+ Acl *schemaacl;
+
+ schemaacl = get_user_default_acl(OBJECT_XMLSCHEMA, schemaowner,
+ schemanamespace);
+ if (schemaacl != NULL)
+ values[Anum_pg_xmlschema_schemaacl - 1] = PointerGetDatum(schemaacl);
+ else
+ nulls[Anum_pg_xmlschema_schemaacl - 1] = true;
+ }
+
+ tup = heap_form_tuple(tupDesc, values, nulls);
+
+ /* insert a new tuple */
+ CatalogTupleInsert(rel, tup);
+ Assert(OidIsValid(oid));
+
+ /* set up dependencies for the new XML schema */
+ myself.classId = XmlSchemaRelationId;
+ myself.objectId = oid;
+ myself.objectSubId = 0;
+
+ /* create dependency on namespace */
+ referenced.classId = NamespaceRelationId;
+ referenced.objectId = schemanamespace;
+ referenced.objectSubId = 0;
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ /* create dependency on owner */
+ recordDependencyOnOwner(XmlSchemaRelationId, oid, schemaowner);
+
+ /* dependency on extension */
+ recordDependencyOnCurrentExtension(&myself, false);
+
+ /* Post creation hook for new XML schema */
+ InvokeObjectPostCreateHook(XmlSchemaRelationId, oid, 0);
+
+ heap_freetuple(tup);
+ table_close(rel, NoLock);
+
+ return oid;
+}
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index f7b2389b019..778f54bdae6 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -41,6 +41,7 @@
#include "catalog/pg_ts_dict.h"
#include "catalog/pg_ts_parser.h"
#include "catalog/pg_ts_template.h"
+#include "catalog/pg_xmlschema.h"
#include "commands/alter.h"
#include "commands/collationcmds.h"
#include "commands/dbcommands.h"
@@ -140,6 +141,10 @@ report_namespace_conflict(Oid classId, const char *name, Oid nspOid)
Assert(OidIsValid(nspOid));
msgfmt = gettext_noop("text search configuration \"%s\" already exists in schema \"%s\"");
break;
+ case XmlSchemaRelationId:
+ Assert(OidIsValid(nspOid));
+ msgfmt = gettext_noop("XML schema \"%s\" already exists in schema \"%s\"");
+ break;
default:
elog(ERROR, "unsupported object class: %u", classId);
break;
@@ -423,6 +428,7 @@ ExecRenameStmt(RenameStmt *stmt)
case OBJECT_FDW:
case OBJECT_FOREIGN_SERVER:
case OBJECT_FUNCTION:
+ case OBJECT_XMLSCHEMA:
case OBJECT_OPCLASS:
case OBJECT_OPFAMILY:
case OBJECT_LANGUAGE:
@@ -567,6 +573,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt,
case OBJECT_FUNCTION:
case OBJECT_OPERATOR:
case OBJECT_OPCLASS:
+ case OBJECT_XMLSCHEMA:
case OBJECT_OPFAMILY:
case OBJECT_PROCEDURE:
case OBJECT_ROUTINE:
@@ -877,6 +884,7 @@ ExecAlterOwnerStmt(AlterOwnerStmt *stmt)
case OBJECT_CONVERSION:
case OBJECT_FUNCTION:
case OBJECT_LANGUAGE:
+ case OBJECT_XMLSCHEMA:
case OBJECT_LARGEOBJECT:
case OBJECT_OPERATOR:
case OBJECT_OPCLASS:
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index 92526012d2a..2e893968ab3 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -278,6 +278,13 @@ does_not_exist_skipping(ObjectType objtype, Node *object)
name = NameListToString(castNode(List, object));
}
break;
+ case OBJECT_XMLSCHEMA:
+ if (!schema_does_not_exist_skipping(castNode(List, object), &msg, &name))
+ {
+ msg = gettext_noop("XML schema \"%s\" does not exist, skipping");
+ name = NameListToString(castNode(List, object));
+ }
+ break;
case OBJECT_SCHEMA:
msg = gettext_noop("schema \"%s\" does not exist, skipping");
name = strVal(object);
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 028f9e2de90..4a087935f16 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -2318,6 +2318,7 @@ stringify_grant_objtype(ObjectType objtype)
case OBJECT_TSTEMPLATE:
case OBJECT_USER_MAPPING:
case OBJECT_VIEW:
+ case OBJECT_XMLSCHEMA:
elog(ERROR, "unsupported object type: %d", (int) objtype);
}
@@ -2402,6 +2403,7 @@ stringify_adefprivs_objtype(ObjectType objtype)
case OBJECT_TSTEMPLATE:
case OBJECT_USER_MAPPING:
case OBJECT_VIEW:
+ case OBJECT_XMLSCHEMA:
elog(ERROR, "unsupported object type: %d", (int) objtype);
}
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index ca3f53c6213..3363797ecee 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -55,4 +55,5 @@ backend_sources += files(
'variable.c',
'view.c',
'wait.c',
+ 'xmlschemacmds.c',
)
diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c
index 4160f5b6855..83c6cdd9bb3 100644
--- a/src/backend/commands/seclabel.c
+++ b/src/backend/commands/seclabel.c
@@ -92,6 +92,7 @@ SecLabelSupportsObjectType(ObjectType objtype)
case OBJECT_TSPARSER:
case OBJECT_TSTEMPLATE:
case OBJECT_USER_MAPPING:
+ case OBJECT_XMLSCHEMA:
return false;
/*
diff --git a/src/backend/commands/xmlschemacmds.c b/src/backend/commands/xmlschemacmds.c
new file mode 100644
index 00000000000..13a4d4b7831
--- /dev/null
+++ b/src/backend/commands/xmlschemacmds.c
@@ -0,0 +1,119 @@
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "access/table.h"
+#include "catalog/namespace.h"
+#include "catalog/objectaccess.h"
+#include "catalog/pg_xmlschema.h"
+#include "catalog/pg_namespace.h"
+#include "commands/xmlschemacmds.h"
+#include "commands/defrem.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+
+
+/*
+ * CREATE XMLSCHEMA
+ */
+ObjectAddress
+DefineXmlSchema(ParseState *pstate, List *names, List *parameters, bool if_not_exists)
+{
+ char *schemaName;
+ Oid schemaNamespace;
+ AclResult aclresult;
+ ListCell *pl;
+ DefElem *schemaDataEl = NULL;
+ char *schemaData;
+ Oid newoid;
+ ObjectAddress address;
+
+ schemaNamespace = QualifiedNameGetCreationNamespace(names, &schemaName);
+
+ aclresult = object_aclcheck(NamespaceRelationId, schemaNamespace, GetUserId(), ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_SCHEMA,
+ get_namespace_name(schemaNamespace));
+
+ /* Parse parameters */
+ foreach(pl, parameters)
+ {
+ DefElem *defel = lfirst_node(DefElem, pl);
+ DefElem **defelp;
+
+ if (strcmp(defel->defname, "schema") == 0)
+ defelp = &schemaDataEl;
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("XML schema attribute \"%s\" not recognized",
+ defel->defname),
+ parser_errposition(pstate, defel->location)));
+ break;
+ }
+ if (*defelp != NULL)
+ errorConflictingDefElem(defel, pstate);
+ *defelp = defel;
+ }
+
+ if (!schemaDataEl)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("parameter \"schema\" must be specified")));
+
+ schemaData = defGetString(schemaDataEl);
+
+ newoid = XmlSchemaCreate(schemaName,
+ schemaNamespace,
+ GetUserId(),
+ schemaData,
+ if_not_exists,
+ false);
+
+ if (!OidIsValid(newoid))
+ {
+ /*
+ * When IF NOT EXISTS was specified and the object already existed,
+ * XmlSchemaCreate returned InvalidOid. Report an invalid object
+ * address.
+ */
+ address.classId = XmlSchemaRelationId;
+ address.objectId = InvalidOid;
+ address.objectSubId = 0;
+ return address;
+ }
+
+ /*
+ * Check that there is not already an XML schema with same name in schema.
+ * This is really just a friendlier error message than the unique index
+ * violation.
+ */
+ IsThereXmlSchemaInNamespace(schemaName, schemaNamespace);
+
+ ObjectAddressSet(address, XmlSchemaRelationId, newoid);
+
+ return address;
+}
+
+/*
+ * IsThereXmlSchemaInNamespace
+ *
+ * Check if there is an XML schema with the given name in the given namespace.
+ * If so, raise an appropriate error.
+ */
+void
+IsThereXmlSchemaInNamespace(const char *schemaname, Oid nspOid)
+{
+ /* make sure the name doesn't conflict */
+ if (SearchSysCacheExists2(XMLSCHEMANAMENSP,
+ PointerGetDatum(schemaname),
+ ObjectIdGetDatum(nspOid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("XML schema \"%s\" for schema \"%s\" already exists",
+ schemaname, get_namespace_name(nspOid))));
+}
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 86ab3704b66..aee70f0c742 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4626,6 +4626,33 @@ ExecEvalXmlExpr(ExprState *state, ExprEvalStep *op)
}
break;
+ case IS_XMLVALIDATE:
+ {
+ Datum *argvalue = op->d.xmlexpr.argvalue;
+ bool *argnull = op->d.xmlexpr.argnull;
+ xmltype *data;
+ Oid schema_oid;
+ xmltype *result;
+
+ /* Two arguments: XML data and schema OID */
+ Assert(list_length(xexpr->args) == 2);
+
+ if (argnull[0] || argnull[1])
+ {
+ *op->resnull = true;
+ return;
+ }
+
+ data = DatumGetXmlP(argvalue[0]);
+ schema_oid = DatumGetObjectId(argvalue[1]);
+
+ result = xmlvalidate_schema(data, schema_oid);
+
+ *op->resvalue = PointerGetDatum(result);
+ *op->resnull = false;
+ }
+ break;
+
case IS_DOCUMENT:
{
Datum *argvalue = op->d.xmlexpr.argvalue;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..2404f2a849e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -707,7 +707,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
*/
/* ordinary key words in alphabetical order */
-%token <keyword> ABORT_P ABSENT ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
+%token <keyword> ABORT_P ABSENT ABSOLUTE_P ACCESS ACCORDING ACTION ADD_P ADMIN AFTER
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
ASENSITIVE ASSERTION ASSIGNMENT ASYMMETRIC ATOMIC AT ATTACH ATTRIBUTE AUTHORIZATION
@@ -728,7 +728,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P
DOUBLE_P DROP
- EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENFORCED ENUM_P ERROR_P
+ EACH ELSE ELEMENT EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENFORCED ENUM_P ERROR_P
ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN
EXPRESSION EXTENSION EXTERNAL EXTRACT
@@ -798,7 +798,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
WAIT WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
- XMLPARSE XMLPI XMLROOT XMLSERIALIZE XMLTABLE
+ XMLPARSE XMLPI XMLROOT XMLSCHEMA XMLSERIALIZE XMLTABLE XMLVALIDATE
YEAR_P YES_P
@@ -6654,6 +6654,27 @@ DefineStmt:
n->if_not_exists = true;
$$ = (Node *) n;
}
+ | CREATE XMLSCHEMA any_name AS Sconst
+ {
+ DefineStmt *n = makeNode(DefineStmt);
+
+ n->kind = OBJECT_XMLSCHEMA;
+ n->args = NIL;
+ n->defnames = $3;
+ n->definition = list_make1(makeDefElem("schema", (Node *) makeString($5), @5));
+ $$ = (Node *) n;
+ }
+ | CREATE XMLSCHEMA IF_P NOT EXISTS any_name AS Sconst
+ {
+ DefineStmt *n = makeNode(DefineStmt);
+
+ n->kind = OBJECT_XMLSCHEMA;
+ n->args = NIL;
+ n->defnames = $6;
+ n->definition = list_make1(makeDefElem("schema", (Node *) makeString($8), @8));
+ n->if_not_exists = true;
+ $$ = (Node *) n;
+ }
;
definition: '(' def_list ')' { $$ = $2; }
@@ -7193,6 +7214,7 @@ object_type_any_name:
| INDEX { $$ = OBJECT_INDEX; }
| FOREIGN TABLE { $$ = OBJECT_FOREIGN_TABLE; }
| COLLATION { $$ = OBJECT_COLLATION; }
+ | XMLSCHEMA { $$ = OBJECT_XMLSCHEMA; }
| CONVERSION_P { $$ = OBJECT_CONVERSION; }
| STATISTICS { $$ = OBJECT_STATISTIC_EXT; }
| TEXT_P SEARCH PARSER { $$ = OBJECT_TSPARSER; }
@@ -8107,6 +8129,15 @@ privilege_target:
n->objs = $2;
$$ = n;
}
+ | XMLSCHEMA any_name_list
+ {
+ PrivTarget *n = palloc_object(PrivTarget);
+
+ n->targtype = ACL_TARGET_OBJECT;
+ n->objtype = OBJECT_XMLSCHEMA;
+ n->objs = $2;
+ $$ = n;
+ }
| ALL TABLES IN_P SCHEMA name_list
{
PrivTarget *n = palloc_object(PrivTarget);
@@ -9573,6 +9604,16 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name
n->missing_ok = false;
$$ = (Node *) n;
}
+ | ALTER XMLSCHEMA any_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+
+ n->renameType = OBJECT_XMLSCHEMA;
+ n->object = (Node *) $3;
+ n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
| ALTER CONVERSION_P any_name RENAME TO name
{
RenameStmt *n = makeNode(RenameStmt);
@@ -10250,6 +10291,16 @@ AlterObjectSchemaStmt:
n->missing_ok = false;
$$ = (Node *) n;
}
+ | ALTER XMLSCHEMA any_name SET SCHEMA name
+ {
+ AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
+
+ n->objectType = OBJECT_XMLSCHEMA;
+ n->object = (Node *) $3;
+ n->newschema = $6;
+ n->missing_ok = false;
+ $$ = (Node *) n;
+ }
| ALTER CONVERSION_P any_name SET SCHEMA name
{
AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt);
@@ -10583,6 +10634,15 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
n->newowner = $6;
$$ = (Node *) n;
}
+ | ALTER XMLSCHEMA any_name OWNER TO RoleSpec
+ {
+ AlterOwnerStmt *n = makeNode(AlterOwnerStmt);
+
+ n->objectType = OBJECT_XMLSCHEMA;
+ n->object = (Node *) $3;
+ n->newowner = $6;
+ $$ = (Node *) n;
+ }
| ALTER CONVERSION_P any_name OWNER TO RoleSpec
{
AlterOwnerStmt *n = makeNode(AlterOwnerStmt);
@@ -16288,6 +16348,17 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | XMLVALIDATE '(' DOCUMENT_P a_expr ACCORDING TO XMLSCHEMA any_name ')'
+ {
+ XmlExpr *x = (XmlExpr *)
+ makeXmlExpr(IS_XMLVALIDATE, NULL, $8,
+ list_make1($4),
+ @1);
+
+ x->xmloption = XMLOPTION_DOCUMENT;
+ x->location = @1;
+ $$ = (Node *) x;
+ }
| JSON_OBJECT '(' func_arg_list ')'
{
/* Support for legacy (non-standard) json_object() */
@@ -17898,6 +17969,7 @@ unreserved_keyword:
| ABSENT
| ABSOLUTE_P
| ACCESS
+ | ACCORDING
| ACTION
| ADD_P
| ADMIN
@@ -18227,6 +18299,7 @@ unreserved_keyword:
| WRAPPER
| WRITE
| XML_P
+ | XMLSCHEMA
| YEAR_P
| YES_P
| ZONE
@@ -18306,6 +18379,7 @@ col_name_keyword:
| XMLROOT
| XMLSERIALIZE
| XMLTABLE
+ | XMLVALIDATE
;
/* Type/function identifier --- keywords that can be type or function names.
@@ -18445,6 +18519,7 @@ bare_label_keyword:
| ABSENT
| ABSOLUTE_P
| ACCESS
+ | ACCORDING
| ACTION
| ADD_P
| ADMIN
@@ -18896,8 +18971,10 @@ bare_label_keyword:
| XMLPARSE
| XMLPI
| XMLROOT
+ | XMLSCHEMA
| XMLSERIALIZE
| XMLTABLE
+ | XMLVALIDATE
| YES_P
| ZONE
;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9cba1272253..b0978ff6c1b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -16,6 +16,7 @@
#include "postgres.h"
#include "access/htup_details.h"
+#include "catalog/namespace.h"
#include "catalog/pg_aggregate.h"
#include "catalog/pg_type.h"
#include "miscadmin.h"
@@ -2360,6 +2361,7 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x)
XmlExpr *newx;
ListCell *lc;
int i;
+ Oid xmlvalidate_schema_oid = InvalidOid;
newx = makeNode(XmlExpr);
newx->op = x->op;
@@ -2372,6 +2374,21 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x)
newx->typmod = -1;
newx->location = x->location;
+ /*
+ * XMLVALIDATE stores the schema name list in named_args, not ResTargets.
+ * Extract it before processing named arguments.
+ */
+ if (x->op == IS_XMLVALIDATE && x->named_args != NIL)
+ {
+ List *schema_name_list;
+ schema_name_list = x->named_args;
+ xmlvalidate_schema_oid = get_xmlschema_oid(schema_name_list, false);
+ /* Preserve schema name for deparsing */
+ newx->name = NameListToString(schema_name_list);
+ /* Clear to avoid processing as ResTargets */
+ x->named_args = NIL;
+ }
+
/*
* gram.y built the named args as a list of ResTarget. Transform each,
* and break the names out as a separate list.
@@ -2471,6 +2488,11 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x)
/* not handled here */
Assert(false);
break;
+ case IS_XMLVALIDATE:
+ /* First argument is the XML data */
+ newe = coerce_to_specific_type(pstate, newe, XMLOID,
+ "XMLVALIDATE");
+ break;
case IS_DOCUMENT:
newe = coerce_to_specific_type(pstate, newe, XMLOID,
"IS DOCUMENT");
@@ -2480,6 +2502,26 @@ transformXmlExpr(ParseState *pstate, XmlExpr *x)
i++;
}
+ /* For XMLVALIDATE, add the schema OID as second argument */
+ if (x->op == IS_XMLVALIDATE)
+ {
+ Const *schema_oid_const;
+
+ Assert(OidIsValid(xmlvalidate_schema_oid));
+
+ schema_oid_const = makeConst(OIDOID,
+ -1,
+ InvalidOid,
+ sizeof(Oid),
+ ObjectIdGetDatum(xmlvalidate_schema_oid),
+ false,
+ true);
+ newx->args = lappend(newx->args, schema_oid_const);
+
+ /* Return type is XML */
+ newx->type = XMLOID;
+ }
+
return (Node *) newx;
}
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index b5a2f915b67..b380bb39eb1 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1976,6 +1976,9 @@ FigureColnameInternal(Node *node, char **name)
case IS_XMLSERIALIZE:
*name = "xmlserialize";
return 2;
+ case IS_XMLVALIDATE:
+ *name = "xmlvalidate";
+ return 2;
case IS_DOCUMENT:
/* nothing */
break;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 34dd6e18df5..60e0a0f7e7c 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -57,6 +57,7 @@
#include "commands/vacuum.h"
#include "commands/view.h"
#include "commands/wait.h"
+#include "commands/xmlschemacmds.h"
#include "miscadmin.h"
#include "parser/parse_utilcmd.h"
#include "postmaster/bgwriter.h"
@@ -1443,6 +1444,13 @@ ProcessUtilitySlow(ParseState *pstate,
stmt->definition,
stmt->if_not_exists);
break;
+ case OBJECT_XMLSCHEMA:
+ Assert(stmt->args == NIL);
+ address = DefineXmlSchema(pstate,
+ stmt->defnames,
+ stmt->definition,
+ stmt->if_not_exists);
+ break;
default:
elog(ERROR, "unrecognized define stmt type: %d",
(int) stmt->kind);
@@ -2238,6 +2246,9 @@ AlterObjectTypeCommandTag(ObjectType objtype)
case OBJECT_COLLATION:
tag = CMDTAG_ALTER_COLLATION;
break;
+ case OBJECT_XMLSCHEMA:
+ tag = CMDTAG_ALTER_XMLSCHEMA;
+ break;
case OBJECT_COLUMN:
tag = CMDTAG_ALTER_TABLE;
break;
@@ -2575,6 +2586,9 @@ CreateCommandTag(Node *parsetree)
case OBJECT_COLLATION:
tag = CMDTAG_DROP_COLLATION;
break;
+ case OBJECT_XMLSCHEMA:
+ tag = CMDTAG_DROP_XMLSCHEMA;
+ break;
case OBJECT_CONVERSION:
tag = CMDTAG_DROP_CONVERSION;
break;
@@ -2776,6 +2790,9 @@ CreateCommandTag(Node *parsetree)
case OBJECT_COLLATION:
tag = CMDTAG_CREATE_COLLATION;
break;
+ case OBJECT_XMLSCHEMA:
+ tag = CMDTAG_CREATE_XMLSCHEMA;
+ break;
case OBJECT_ACCESS_METHOD:
tag = CMDTAG_CREATE_ACCESS_METHOD;
break;
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 3a6905f9546..412a0450430 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -867,6 +867,10 @@ acldefault(ObjectType objtype, Oid ownerId)
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_PARAMETER_ACL;
break;
+ case OBJECT_XMLSCHEMA:
+ world_default = ACL_NO_RIGHTS;
+ owner_default = ACL_ALL_RIGHTS_XMLSCHEMA;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", (int) objtype);
world_default = ACL_NO_RIGHTS; /* keep compiler quiet */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 416f1a21ae4..97cc73a223e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10119,17 +10119,20 @@ get_rule_expr(Node *node, deparse_context *context,
case IS_XMLSERIALIZE:
appendStringInfoString(buf, "XMLSERIALIZE(");
break;
+ case IS_XMLVALIDATE:
+ appendStringInfoString(buf, "XMLVALIDATE(");
+ break;
case IS_DOCUMENT:
break;
}
- if (xexpr->op == IS_XMLPARSE || xexpr->op == IS_XMLSERIALIZE)
+ if (xexpr->op == IS_XMLPARSE || xexpr->op == IS_XMLSERIALIZE || xexpr->op == IS_XMLVALIDATE)
{
if (xexpr->xmloption == XMLOPTION_DOCUMENT)
appendStringInfoString(buf, "DOCUMENT ");
else
appendStringInfoString(buf, "CONTENT ");
}
- if (xexpr->name)
+ if (xexpr->name && xexpr->op != IS_XMLVALIDATE)
{
appendStringInfo(buf, "NAME %s",
quote_identifier(map_xml_name_to_sql_identifier(xexpr->name)));
@@ -10226,6 +10229,15 @@ get_rule_expr(Node *node, deparse_context *context,
}
}
break;
+ case IS_XMLVALIDATE:
+ Assert(list_length(xexpr->args) == 2);
+
+ get_rule_expr((Node *) linitial(xexpr->args),
+ context, true);
+
+ appendStringInfoString(buf, " ACCORDING TO XMLSCHEMA ");
+ appendStringInfoString(buf, xexpr->name);
+ break;
case IS_DOCUMENT:
get_rule_expr_paren((Node *) xexpr->args, context, false, node);
break;
diff --git a/src/backend/utils/adt/xml.c b/src/backend/utils/adt/xml.c
index f69dc68286c..e179d61f923 100644
--- a/src/backend/utils/adt/xml.c
+++ b/src/backend/utils/adt/xml.c
@@ -58,6 +58,7 @@
#include <libxml/xmlwriter.h>
#include <libxml/xpath.h>
#include <libxml/xpathInternals.h>
+#include <libxml/xmlschemas.h>
/*
* We used to check for xmlStructuredErrorContext via a configure test; but
@@ -84,6 +85,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_class.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_xmlschema.h"
#include "executor/spi.h"
#include "executor/tablefunc.h"
#include "fmgr.h"
@@ -94,6 +96,7 @@
#include "nodes/execnodes.h"
#include "nodes/miscnodes.h"
#include "nodes/nodeFuncs.h"
+#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/date.h"
@@ -1158,10 +1161,144 @@ xmlvalidate(PG_FUNCTION_ARGS)
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("xmlvalidate is not implemented")));
+ errmsg("xmlvalidate is not implemented against generalized schema definitions")));
return 0;
}
+/*
+ * xmlvalidate_schema - validate XML document against a registered XML Schema
+ *
+ * Validates the given XML document against the schema identified by a schema_oid.
+ * Returns the validated XML value, or raises an error if the validation fails.
+ */
+xmltype *
+xmlvalidate_schema(xmltype *data, Oid schema_oid)
+{
+#ifdef USE_LIBXML
+ HeapTuple tuple;
+ Datum schema_datum;
+ bool isnull;
+ text *schema_text;
+ char *schemastr;
+ volatile xmlDocPtr doc = NULL;
+ volatile xmlSchemaParserCtxtPtr schema_parser_ctxt = NULL;
+ volatile xmlSchemaPtr schema_ptr = NULL;
+ volatile xmlSchemaValidCtxtPtr valid_ctxt = NULL;
+ int result;
+ PgXmlErrorContext *xmlerrcxt;
+ AclResult aclresult;
+
+ /* Check usage permission first */
+ aclresult = object_aclcheck(XmlSchemaRelationId, schema_oid,
+ GetUserId(), ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ {
+ /* Fetch tuple only to get name for the error message */
+ Form_pg_xmlschema schema_form;
+
+ tuple = SearchSysCache1(XMLSCHEMAOID, ObjectIdGetDatum(schema_oid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for XML schema %u", schema_oid);
+
+ schema_form = (Form_pg_xmlschema) GETSTRUCT(tuple);
+ ReleaseSysCache(tuple);
+
+ aclcheck_error(aclresult, OBJECT_XMLSCHEMA,
+ NameStr(schema_form->schemaname));
+ }
+
+ tuple = SearchSysCache1(XMLSCHEMAOID, ObjectIdGetDatum(schema_oid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for XML schema %u", schema_oid);
+
+ schema_datum = SysCacheGetAttr(XMLSCHEMAOID, tuple,
+ Anum_pg_xmlschema_schemadata, &isnull);
+ if (isnull)
+ elog(ERROR, "null schemadata for XML schema %u", schema_oid);
+
+ schema_text = DatumGetTextPP(schema_datum);
+ schemastr = text_to_cstring(schema_text);
+ ReleaseSysCache(tuple);
+
+ xmlerrcxt = pg_xml_init(PG_XML_STRICTNESS_WELLFORMED);
+
+ PG_TRY();
+ {
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+ doc = xml_parse((text *) data, XMLOPTION_DOCUMENT, true,
+ GetDatabaseEncoding(), NULL, NULL, (Node *) &escontext);
+
+ if (escontext.error_occurred || doc == NULL)
+ {
+ if (escontext.error_occurred && escontext.error_data)
+ {
+ ErrorData *edata = escontext.error_data;
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_XML_DOCUMENT),
+ errmsg("invalid XML document"),
+ errdetail_internal("%s", edata->message ? edata->message : "unknown error")));
+ }
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT,
+ "invalid XML document");
+ }
+ schema_parser_ctxt = xmlSchemaNewMemParserCtxt(schemastr, strlen(schemastr));
+ if (schema_parser_ctxt == NULL)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
+ "failed to create schema parser context");
+
+ schema_ptr = xmlSchemaParse(schema_parser_ctxt);
+ if (schema_ptr == NULL)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT,
+ "failed to parse XML schema");
+
+ valid_ctxt = xmlSchemaNewValidCtxt(schema_ptr);
+ if (valid_ctxt == NULL)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_OUT_OF_MEMORY,
+ "failed to create schema validation context");
+
+ xmlSchemaSetValidStructuredErrors(valid_ctxt, xml_errorHandler, xmlerrcxt);
+
+ result = xmlSchemaValidateDoc(valid_ctxt, doc);
+ if (result < 0)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INTERNAL_ERROR,
+ "internal error during schema validation");
+ if (result > 0)
+ xml_ereport(xmlerrcxt, ERROR, ERRCODE_INVALID_XML_DOCUMENT,
+ "XML validation failed");
+ }
+ PG_CATCH();
+ {
+ if (valid_ctxt)
+ xmlSchemaFreeValidCtxt(valid_ctxt);
+ if (schema_ptr)
+ xmlSchemaFree(schema_ptr);
+ if (schema_parser_ctxt)
+ xmlSchemaFreeParserCtxt(schema_parser_ctxt);
+ if (doc)
+ xmlFreeDoc(doc);
+ pg_xml_done(xmlerrcxt, true);
+ pfree(schemastr);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ if (valid_ctxt)
+ xmlSchemaFreeValidCtxt(valid_ctxt);
+ if (schema_ptr)
+ xmlSchemaFree(schema_ptr);
+ if (schema_parser_ctxt)
+ xmlSchemaFreeParserCtxt(schema_parser_ctxt);
+ if (doc)
+ xmlFreeDoc(doc);
+
+ pg_xml_done(xmlerrcxt, false);
+ pfree(schemastr);
+ return data;
+#else
+ NO_XML_SUPPORT();
+ return NULL;
+#endif
+}
bool
xml_is_document(xmltype *arg)
@@ -1181,7 +1318,7 @@ xml_is_document(xmltype *arg)
return !escontext.error_occurred;
#else /* not USE_LIBXML */
NO_XML_SUPPORT();
- return false;
+ return NULL;
#endif /* not USE_LIBXML */
}
diff --git a/src/include/catalog/meson.build b/src/include/catalog/meson.build
index b63cd584068..5846919f626 100644
--- a/src/include/catalog/meson.build
+++ b/src/include/catalog/meson.build
@@ -59,6 +59,7 @@ catalog_headers = [
'pg_seclabel.h',
'pg_shseclabel.h',
'pg_collation.h',
+ 'pg_xmlschema.h',
'pg_parameter_acl.h',
'pg_partitioned_table.h',
'pg_range.h',
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index 1a25973685c..a0f9585c53a 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -191,6 +191,7 @@ extern bool SearchPathMatchesCurrentEnvironment(SearchPathMatcher *path);
extern Oid get_collation_oid(List *collname, bool missing_ok);
extern Oid get_conversion_oid(List *conname, bool missing_ok);
+extern Oid get_xmlschema_oid(List *schemaname, bool missing_ok);
extern Oid FindDefaultConversionProc(int32 for_encoding, int32 to_encoding);
diff --git a/src/include/catalog/pg_xmlschema.h b/src/include/catalog/pg_xmlschema.h
new file mode 100644
index 00000000000..86d228ad15f
--- /dev/null
+++ b/src/include/catalog/pg_xmlschema.h
@@ -0,0 +1,43 @@
+#ifndef PG_XMLSCHEMA_H
+#define PG_XMLSCHEMA_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_xmlschema_d.h"
+
+CATALOG(pg_xmlschema,6434,XmlSchemaRelationId)
+{
+ Oid oid; /* oid */
+ NameData schemaname; /* XML schema name */
+ /* OID of namespace containing this XML schema */
+ Oid schemanamespace BKI_DEFAULT(pg_catalog) BKI_LOOKUP(pg_namespace);
+ /* owner of XML schema */
+ Oid schemaowner BKI_DEFAULT(POSTGRES) BKI_LOOKUP(pg_authid);
+#ifdef CATALOG_VARLEN
+ text schemadata BKI_FORCE_NOT_NULL; /* XSD schema definition text */
+ /* Access privileges */
+ aclitem schemaacl[1] BKI_DEFAULT(_null_);
+#endif
+} FormData_pg_xmlschema;
+
+/* ----------------
+ * Form_pg_xmlschema maps to a pointer to a row with
+ * the format of pg_xmlschema relation.
+ * ----------------
+ */
+typedef FormData_pg_xmlschema *Form_pg_xmlschema;
+
+DECLARE_TOAST(pg_xmlschema, 6435, 6436);
+DECLARE_UNIQUE_INDEX(pg_xmlschema_name_nsp_index, 6437, XmlSchemaNameNspIndexId, pg_xmlschema, btree(schemaname name_ops, schemanamespace oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_xmlschema_oid_index, 6438, XmlSchemaOidIndexId, pg_xmlschema, btree(oid oid_ops));
+
+MAKE_SYSCACHE(XMLSCHEMANAMENSP, pg_xmlschema_name_nsp_index, 8);
+MAKE_SYSCACHE(XMLSCHEMAOID, pg_xmlschema_oid_index, 8);
+
+extern Oid XmlSchemaCreate(const char *schemaname,
+ Oid schemanamespace,
+ Oid schemaowner,
+ const char *schemadata,
+ bool if_not_exists,
+ bool quiet);
+
+#endif /* PG_XMLSCHEMA_H */
diff --git a/src/include/commands/xmlschemacmds.h b/src/include/commands/xmlschemacmds.h
new file mode 100644
index 00000000000..db8c169452e
--- /dev/null
+++ b/src/include/commands/xmlschemacmds.h
@@ -0,0 +1,10 @@
+#ifndef XMLSCHEMACMDS_H
+#define XMLSCHEMACMDS_H
+
+#include "catalog/objectaddress.h"
+#include "parser/parse_node.h"
+
+extern ObjectAddress DefineXmlSchema(ParseState *pstate, List *names, List *parameters, bool if_not_exists);
+extern void IsThereXmlSchemaInNamespace(const char *schemaname, Oid nspOid);
+
+#endif /* XMLSCHEMACMDS_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index aac4bfc70d9..b1227f1a7b7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2401,6 +2401,7 @@ typedef enum ObjectType
OBJECT_TYPE,
OBJECT_USER_MAPPING,
OBJECT_VIEW,
+ OBJECT_XMLSCHEMA,
} ObjectType;
/* ----------------------
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 5211cadc258..c40cbc8981a 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1610,6 +1610,7 @@ typedef enum XmlExprOp
IS_XMLROOT, /* XMLROOT(xml, version, standalone) */
IS_XMLSERIALIZE, /* XMLSERIALIZE(is_document, xmlval, indent) */
IS_DOCUMENT, /* xmlval IS DOCUMENT */
+ IS_XMLVALIDATE, /* XMLVALIDATE(xmlval, schema) */
} XmlExprOp;
typedef enum XmlOptionType
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7753c5c8a8..4e8b5e1d7e7 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -29,6 +29,7 @@ PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("absent", ABSENT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("absolute", ABSOLUTE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("access", ACCESS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("according", ACCORDING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("action", ACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("add", ADD_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("admin", ADMIN, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -520,8 +521,10 @@ PG_KEYWORD("xmlnamespaces", XMLNAMESPACES, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("xmlparse", XMLPARSE, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("xmlpi", XMLPI, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("xmlroot", XMLROOT, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("xmlschema", XMLSCHEMA, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("xmlserialize", XMLSERIALIZE, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("xmltable", XMLTABLE, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("xmlvalidate", XMLVALIDATE, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("year", YEAR_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("yes", YES_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("zone", ZONE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 1290c9bab68..1cd35452acd 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -31,6 +31,7 @@ PG_CMDTAG(CMDTAG_ALTER_CAST, "ALTER CAST", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_COLLATION, "ALTER COLLATION", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_CONSTRAINT, "ALTER CONSTRAINT", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_CONVERSION, "ALTER CONVERSION", true, false, false)
+PG_CMDTAG(CMDTAG_ALTER_XMLSCHEMA, "ALTER XMLSCHEMA", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_DATABASE, "ALTER DATABASE", false, false, false)
PG_CMDTAG(CMDTAG_ALTER_DEFAULT_PRIVILEGES, "ALTER DEFAULT PRIVILEGES", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_DOMAIN, "ALTER DOMAIN", true, false, false)
@@ -88,6 +89,7 @@ PG_CMDTAG(CMDTAG_CREATE_CAST, "CREATE CAST", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_COLLATION, "CREATE COLLATION", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_CONSTRAINT, "CREATE CONSTRAINT", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_CONVERSION, "CREATE CONVERSION", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_XMLSCHEMA, "CREATE XMLSCHEMA", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_DATABASE, "CREATE DATABASE", false, false, false)
PG_CMDTAG(CMDTAG_CREATE_DOMAIN, "CREATE DOMAIN", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_EVENT_TRIGGER, "CREATE EVENT TRIGGER", false, false, false)
@@ -140,6 +142,7 @@ PG_CMDTAG(CMDTAG_DROP_CAST, "DROP CAST", true, false, false)
PG_CMDTAG(CMDTAG_DROP_COLLATION, "DROP COLLATION", true, false, false)
PG_CMDTAG(CMDTAG_DROP_CONSTRAINT, "DROP CONSTRAINT", true, false, false)
PG_CMDTAG(CMDTAG_DROP_CONVERSION, "DROP CONVERSION", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_XMLSCHEMA, "DROP XMLSCHEMA", true, false, false)
PG_CMDTAG(CMDTAG_DROP_DATABASE, "DROP DATABASE", false, false, false)
PG_CMDTAG(CMDTAG_DROP_DOMAIN, "DROP DOMAIN", true, false, false)
PG_CMDTAG(CMDTAG_DROP_EVENT_TRIGGER, "DROP EVENT TRIGGER", false, false, false)
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index ec01fd581cf..e24427c9663 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -169,6 +169,7 @@ typedef struct ArrayType Acl;
#define ACL_ALL_RIGHTS_SCHEMA (ACL_USAGE|ACL_CREATE)
#define ACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)
#define ACL_ALL_RIGHTS_TYPE (ACL_USAGE)
+#define ACL_ALL_RIGHTS_XMLSCHEMA (ACL_USAGE)
/* operation codes for pg_*_aclmask */
typedef enum
diff --git a/src/include/utils/xml.h b/src/include/utils/xml.h
index 03acb255449..dc6a4d37840 100644
--- a/src/include/utils/xml.h
+++ b/src/include/utils/xml.h
@@ -76,6 +76,7 @@ extern xmltype *xmlelement(XmlExpr *xexpr,
extern xmltype *xmlparse(text *data, XmlOptionType xmloption_arg, bool preserve_whitespace);
extern xmltype *xmlpi(const char *target, text *arg, bool arg_is_null, bool *result_is_null);
extern xmltype *xmlroot(xmltype *data, text *version, int standalone);
+extern xmltype *xmlvalidate_schema(xmltype *data, Oid schema_oid);
extern bool xml_is_document(xmltype *arg);
extern text *xmltotext_with_options(xmltype *data, XmlOptionType xmloption_arg,
bool indent);
diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out
index 103a22a3b1d..9794610a754 100644
--- a/src/test/regress/expected/xml.out
+++ b/src/test/regress/expected/xml.out
@@ -1,1883 +1,2180 @@
-CREATE TABLE xmltest (
- id int,
- data xml
-);
-INSERT INTO xmltest VALUES (1, '<value>one</value>');
-INSERT INTO xmltest VALUES (2, '<value>two</value>');
-INSERT INTO xmltest VALUES (3, '<wrong');
-ERROR: invalid XML content
-LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
- ^
-DETAIL: line 1: Couldn't find end of Start Tag wrong line 1
-<wrong
- ^
-SELECT * FROM xmltest;
- id | data
-----+--------------------
- 1 | <value>one</value>
- 2 | <value>two</value>
-(2 rows)
-
--- test non-throwing API, too
-SELECT pg_input_is_valid('<value>one</value>', 'xml');
- pg_input_is_valid
--------------------
- t
-(1 row)
-
-SELECT pg_input_is_valid('<value>one</', 'xml');
- pg_input_is_valid
--------------------
- f
-(1 row)
-
-SELECT message FROM pg_input_error_info('<value>one</', 'xml');
- message
----------------------
- invalid XML content
-(1 row)
-
-SELECT pg_input_is_valid('<?xml version="1.0" standalone="y"?><foo/>', 'xml');
- pg_input_is_valid
--------------------
- f
-(1 row)
-
-SELECT message FROM pg_input_error_info('<?xml version="1.0" standalone="y"?><foo/>', 'xml');
- message
-----------------------------------------------
- invalid XML content: invalid XML declaration
-(1 row)
-
-SELECT xmlcomment('test');
- xmlcomment
--------------
- <!--test-->
-(1 row)
-
-SELECT xmlcomment('-test');
- xmlcomment
---------------
- <!---test-->
-(1 row)
-
-SELECT xmlcomment('test-');
-ERROR: invalid XML comment
-SELECT xmlcomment('--test');
-ERROR: invalid XML comment
-SELECT xmlcomment('te st');
- xmlcomment
---------------
- <!--te st-->
-(1 row)
-
-SELECT xmlconcat(xmlcomment('hello'),
- xmlelement(NAME qux, 'foo'),
- xmlcomment('world'));
- xmlconcat
-----------------------------------------
- <!--hello--><qux>foo</qux><!--world-->
-(1 row)
-
-SELECT xmlconcat('hello', 'you');
- xmlconcat
------------
- helloyou
-(1 row)
-
-SELECT xmlconcat(1, 2);
-ERROR: argument of XMLCONCAT must be type xml, not type integer
-LINE 1: SELECT xmlconcat(1, 2);
- ^
-SELECT xmlconcat('bad', '<syntax');
-ERROR: invalid XML content
-LINE 1: SELECT xmlconcat('bad', '<syntax');
- ^
-DETAIL: line 1: Couldn't find end of Start Tag syntax line 1
-<syntax
- ^
-SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
- xmlconcat
---------------
- <foo/><bar/>
-(1 row)
-
-SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
- xmlconcat
------------------------------------
- <?xml version="1.1"?><foo/><bar/>
-(1 row)
-
-SELECT xmlconcat(NULL);
- xmlconcat
------------
-
-(1 row)
-
-SELECT xmlconcat(NULL, NULL);
- xmlconcat
------------
-
-(1 row)
-
-SELECT xmlelement(name element,
- xmlattributes (1 as one, 'deuce' as two),
- 'content');
- xmlelement
-------------------------------------------------
- <element one="1" two="deuce">content</element>
-(1 row)
-
-SELECT xmlelement(name element,
- xmlattributes ('unnamed and wrong'));
-ERROR: unnamed XML attribute value must be a column reference
-LINE 2: xmlattributes ('unnamed and wrong'));
- ^
-SELECT xmlelement(name element, xmlelement(name nested, 'stuff'));
- xmlelement
--------------------------------------------
- <element><nested>stuff</nested></element>
-(1 row)
-
-SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
- xmlelement
-----------------------------------------------------------------------
- <employee><name>sharon</name><age>25</age><pay>1000</pay></employee>
- <employee><name>sam</name><age>30</age><pay>2000</pay></employee>
- <employee><name>bill</name><age>20</age><pay>1000</pay></employee>
- <employee><name>jeff</name><age>23</age><pay>600</pay></employee>
- <employee><name>cim</name><age>30</age><pay>400</pay></employee>
- <employee><name>linda</name><age>19</age><pay>100</pay></employee>
-(6 rows)
-
-SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
-ERROR: XML attribute name "a" appears more than once
-LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
- ^
-SELECT xmlelement(name num, 37);
- xmlelement
----------------
- <num>37</num>
-(1 row)
-
-SELECT xmlelement(name foo, text 'bar');
- xmlelement
-----------------
- <foo>bar</foo>
-(1 row)
-
-SELECT xmlelement(name foo, xml 'bar');
- xmlelement
-----------------
- <foo>bar</foo>
-(1 row)
-
-SELECT xmlelement(name foo, text 'b<a/>r');
- xmlelement
--------------------------
- <foo>b<a/>r</foo>
-(1 row)
-
-SELECT xmlelement(name foo, xml 'b<a/>r');
- xmlelement
--------------------
- <foo>b<a/>r</foo>
-(1 row)
-
-SELECT xmlelement(name foo, array[1, 2, 3]);
- xmlelement
--------------------------------------------------------------------------
- <foo><element>1</element><element>2</element><element>3</element></foo>
-(1 row)
-
-SET xmlbinary TO base64;
-SELECT xmlelement(name foo, bytea 'bar');
- xmlelement
------------------
- <foo>YmFy</foo>
-(1 row)
-
-SET xmlbinary TO hex;
-SELECT xmlelement(name foo, bytea 'bar');
- xmlelement
--------------------
- <foo>626172</foo>
-(1 row)
-
-SELECT xmlelement(name foo, xmlattributes(true as bar));
- xmlelement
--------------------
- <foo bar="true"/>
-(1 row)
-
-SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar));
- xmlelement
-----------------------------------
- <foo bar="2009-04-09T00:24:37"/>
-(1 row)
-
-SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar));
-ERROR: timestamp out of range
-DETAIL: XML does not support infinite timestamp values.
-SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'b<a/>r' as funnier));
- xmlelement
-------------------------------------------------------------
- <foo funny="<>&"'" funnier="b<a/>r"/>
-(1 row)
-
-SELECT xmlparse(content '');
- xmlparse
-----------
-
-(1 row)
-
-SELECT xmlparse(content ' ');
- xmlparse
-----------
-
-(1 row)
-
-SELECT xmlparse(content 'abc');
- xmlparse
-----------
- abc
-(1 row)
-
-SELECT xmlparse(content '<abc>x</abc>');
- xmlparse
---------------
- <abc>x</abc>
-(1 row)
-
-SELECT xmlparse(content '<invalidentity>&</invalidentity>');
-ERROR: invalid XML content
-DETAIL: line 1: xmlParseEntityRef: no name
-<invalidentity>&</invalidentity>
- ^
-SELECT xmlparse(content '<undefinedentity>&idontexist;</undefinedentity>');
-ERROR: invalid XML content
-DETAIL: line 1: Entity 'idontexist' not defined
-<undefinedentity>&idontexist;</undefinedentity>
- ^
-SELECT xmlparse(content '<invalidns xmlns=''<''/>');
- xmlparse
----------------------------
- <invalidns xmlns='<'/>
-(1 row)
-
-SELECT xmlparse(content '<relativens xmlns=''relative''/>');
- xmlparse
---------------------------------
- <relativens xmlns='relative'/>
-(1 row)
-
-SELECT xmlparse(content '<twoerrors>&idontexist;</unbalanced>');
-ERROR: invalid XML content
-DETAIL: line 1: Entity 'idontexist' not defined
-<twoerrors>&idontexist;</unbalanced>
- ^
-line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
-<twoerrors>&idontexist;</unbalanced>
- ^
-SELECT xmlparse(content '<nosuchprefix:tag/>');
- xmlparse
----------------------
- <nosuchprefix:tag/>
-(1 row)
-
-SELECT xmlparse(document ' ');
-ERROR: invalid XML document
-DETAIL: line 1: Start tag expected, '<' not found
-
- ^
-SELECT xmlparse(document 'abc');
-ERROR: invalid XML document
-DETAIL: line 1: Start tag expected, '<' not found
-abc
-^
-SELECT xmlparse(document '<abc>x</abc>');
- xmlparse
---------------
- <abc>x</abc>
-(1 row)
-
-SELECT xmlparse(document '<invalidentity>&</abc>');
-ERROR: invalid XML document
-DETAIL: line 1: xmlParseEntityRef: no name
-<invalidentity>&</abc>
- ^
-line 1: Opening and ending tag mismatch: invalidentity line 1 and abc
-<invalidentity>&</abc>
- ^
-SELECT xmlparse(document '<undefinedentity>&idontexist;</abc>');
-ERROR: invalid XML document
-DETAIL: line 1: Entity 'idontexist' not defined
-<undefinedentity>&idontexist;</abc>
- ^
-line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc
-<undefinedentity>&idontexist;</abc>
- ^
-SELECT xmlparse(document '<invalidns xmlns=''<''/>');
- xmlparse
----------------------------
- <invalidns xmlns='<'/>
-(1 row)
-
-SELECT xmlparse(document '<relativens xmlns=''relative''/>');
- xmlparse
---------------------------------
- <relativens xmlns='relative'/>
-(1 row)
-
-SELECT xmlparse(document '<twoerrors>&idontexist;</unbalanced>');
-ERROR: invalid XML document
-DETAIL: line 1: Entity 'idontexist' not defined
-<twoerrors>&idontexist;</unbalanced>
- ^
-line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
-<twoerrors>&idontexist;</unbalanced>
- ^
-SELECT xmlparse(document '<nosuchprefix:tag/>');
- xmlparse
----------------------
- <nosuchprefix:tag/>
-(1 row)
-
-SELECT xmlpi(name foo);
- xmlpi
----------
- <?foo?>
-(1 row)
-
-SELECT xmlpi(name xml);
-ERROR: invalid XML processing instruction
-DETAIL: XML processing instruction target name cannot be "xml".
-SELECT xmlpi(name xmlstuff);
- xmlpi
---------------
- <?xmlstuff?>
-(1 row)
-
-SELECT xmlpi(name foo, 'bar');
- xmlpi
--------------
- <?foo bar?>
-(1 row)
-
-SELECT xmlpi(name foo, 'in?>valid');
-ERROR: invalid XML processing instruction
-DETAIL: XML processing instruction cannot contain "?>".
-SELECT xmlpi(name foo, null);
- xmlpi
--------
-
-(1 row)
-
-SELECT xmlpi(name xml, null);
-ERROR: invalid XML processing instruction
-DETAIL: XML processing instruction target name cannot be "xml".
-SELECT xmlpi(name xmlstuff, null);
- xmlpi
--------
-
-(1 row)
-
-SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"');
- xmlpi
--------------------------------------------------------
- <?xml-stylesheet href="mystyle.css" type="text/css"?>
-(1 row)
-
-SELECT xmlpi(name foo, ' bar');
- xmlpi
--------------
- <?foo bar?>
-(1 row)
-
-SELECT xmlroot(xml '<foo/>', version no value, standalone no value);
- xmlroot
----------
- <foo/>
-(1 row)
-
-SELECT xmlroot(xml '<foo/>', version '2.0');
- xmlroot
------------------------------
- <?xml version="2.0"?><foo/>
-(1 row)
-
-SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
- xmlroot
-----------------------------------------------
- <?xml version="1.0" standalone="yes"?><foo/>
-(1 row)
-
-SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes);
- xmlroot
-----------------------------------------------
- <?xml version="1.0" standalone="yes"?><foo/>
-(1 row)
-
-SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no);
- xmlroot
----------------------------------------------
- <?xml version="1.1" standalone="no"?><foo/>
-(1 row)
-
-SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
- xmlroot
----------------------------------------------
- <?xml version="1.0" standalone="no"?><foo/>
-(1 row)
-
-SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
- xmlroot
----------
- <foo/>
-(1 row)
-
-SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value);
- xmlroot
-----------------------------------------------
- <?xml version="1.0" standalone="yes"?><foo/>
-(1 row)
-
-SELECT xmlroot (
- xmlelement (
- name gazonk,
- xmlattributes (
- 'val' AS name,
- 1 + 1 AS num
- ),
- xmlelement (
- NAME qux,
- 'foo'
- )
- ),
- version '1.0',
- standalone yes
-);
- xmlroot
-------------------------------------------------------------------------------------------
- <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk>
-(1 row)
-
-SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
- xmlserialize
---------------------
- <value>one</value>
- <value>two</value>
-(2 rows)
-
-SELECT xmlserialize(content 'good' as char(10));
- xmlserialize
---------------
- good
-(1 row)
-
-SELECT xmlserialize(document 'bad' as text);
-ERROR: not an XML document
--- indent
-SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text INDENT);
- xmlserialize
--------------------------
- <foo> +
- <bar> +
- <val x="y">42</val>+
- </bar> +
- </foo>
-(1 row)
-
-SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text INDENT);
- xmlserialize
--------------------------
- <foo> +
- <bar> +
- <val x="y">42</val>+
- </bar> +
- </foo>
-(1 row)
-
--- no indent
-SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
- xmlserialize
--------------------------------------------
- <foo><bar><val x="y">42</val></bar></foo>
-(1 row)
-
-SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
- xmlserialize
--------------------------------------------
- <foo><bar><val x="y">42</val></bar></foo>
-(1 row)
-
--- indent non singly-rooted xml
-SELECT xmlserialize(DOCUMENT '<foo>73</foo><bar><val x="y">42</val></bar>' AS text INDENT);
-ERROR: not an XML document
-SELECT xmlserialize(CONTENT '<foo>73</foo><bar><val x="y">42</val></bar>' AS text INDENT);
- xmlserialize
------------------------
- <foo>73</foo> +
- <bar> +
- <val x="y">42</val>+
- </bar>
-(1 row)
-
--- indent non singly-rooted xml with mixed contents
-SELECT xmlserialize(DOCUMENT 'text node<foo>73</foo>text node<bar><val x="y">42</val></bar>' AS text INDENT);
-ERROR: not an XML document
-SELECT xmlserialize(CONTENT 'text node<foo>73</foo>text node<bar><val x="y">42</val></bar>' AS text INDENT);
- xmlserialize
-------------------------
- text node +
- <foo>73</foo>text node+
- <bar> +
- <val x="y">42</val> +
- </bar>
-(1 row)
-
--- indent singly-rooted xml with mixed contents
-SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val><val x="y">text node<val>73</val></val></bar></foo>' AS text INDENT);
- xmlserialize
----------------------------------------------
- <foo> +
- <bar> +
- <val x="y">42</val> +
- <val x="y">text node<val>73</val></val>+
- </bar> +
- </foo>
-(1 row)
-
-SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val><val x="y">text node<val>73</val></val></bar></foo>' AS text INDENT);
- xmlserialize
----------------------------------------------
- <foo> +
- <bar> +
- <val x="y">42</val> +
- <val x="y">text node<val>73</val></val>+
- </bar> +
- </foo>
-(1 row)
-
--- indent empty string
-SELECT xmlserialize(DOCUMENT '' AS text INDENT);
-ERROR: not an XML document
-SELECT xmlserialize(CONTENT '' AS text INDENT);
- xmlserialize
---------------
-
-(1 row)
-
--- whitespaces
-SELECT xmlserialize(DOCUMENT ' ' AS text INDENT);
-ERROR: not an XML document
-SELECT xmlserialize(CONTENT ' ' AS text INDENT);
- xmlserialize
---------------
-
-(1 row)
-
--- indent null
-SELECT xmlserialize(DOCUMENT NULL AS text INDENT);
- xmlserialize
---------------
-
-(1 row)
-
-SELECT xmlserialize(CONTENT NULL AS text INDENT);
- xmlserialize
---------------
-
-(1 row)
-
--- indent with XML declaration
-SELECT xmlserialize(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><foo><bar><val>73</val></bar></foo>' AS text INDENT);
- xmlserialize
-----------------------------------------
- <?xml version="1.0" encoding="UTF-8"?>+
- <foo> +
- <bar> +
- <val>73</val> +
- </bar> +
- </foo>
-(1 row)
-
-SELECT xmlserialize(CONTENT '<?xml version="1.0" encoding="UTF-8"?><foo><bar><val>73</val></bar></foo>' AS text INDENT);
- xmlserialize
--------------------
- <foo> +
- <bar> +
- <val>73</val>+
- </bar> +
- </foo>
-(1 row)
-
--- indent containing DOCTYPE declaration
-SELECT xmlserialize(DOCUMENT '<!DOCTYPE a><a/>' AS text INDENT);
- xmlserialize
---------------
- <!DOCTYPE a>+
- <a/>
-(1 row)
-
-SELECT xmlserialize(CONTENT '<!DOCTYPE a><a/>' AS text INDENT);
- xmlserialize
---------------
- <!DOCTYPE a>+
- <a/> +
-
-(1 row)
-
--- indent xml with empty element
-SELECT xmlserialize(DOCUMENT '<foo><bar></bar></foo>' AS text INDENT);
- xmlserialize
---------------
- <foo> +
- <bar/> +
- </foo>
-(1 row)
-
-SELECT xmlserialize(CONTENT '<foo><bar></bar></foo>' AS text INDENT);
- xmlserialize
---------------
- <foo> +
- <bar/> +
- </foo>
-(1 row)
-
--- 'no indent' = not using 'no indent'
-SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text) = xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
- ?column?
-----------
- t
-(1 row)
-
-SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text) = xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
- ?column?
-----------
- t
-(1 row)
-
--- indent xml strings containing blank nodes
-SELECT xmlserialize(DOCUMENT '<foo> <bar></bar> </foo>' AS text INDENT);
- xmlserialize
---------------
- <foo> +
- <bar/> +
- </foo>
-(1 row)
-
-SELECT xmlserialize(CONTENT 'text node<foo> <bar></bar> </foo>' AS text INDENT);
- xmlserialize
---------------
- text node +
- <foo> +
- <bar/> +
- </foo>
-(1 row)
-
-SELECT xml '<foo>bar</foo>' IS DOCUMENT;
- ?column?
-----------
- t
-(1 row)
-
-SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
- ?column?
-----------
- f
-(1 row)
-
-SELECT xml '<abc/>' IS NOT DOCUMENT;
- ?column?
-----------
- f
-(1 row)
-
-SELECT xml 'abc' IS NOT DOCUMENT;
- ?column?
-----------
- t
-(1 row)
-
-SELECT '<>' IS NOT DOCUMENT;
-ERROR: invalid XML content
-LINE 1: SELECT '<>' IS NOT DOCUMENT;
- ^
-DETAIL: line 1: StartTag: invalid element name
-<>
- ^
-SELECT xmlagg(data) FROM xmltest;
- xmlagg
---------------------------------------
- <value>one</value><value>two</value>
-(1 row)
-
-SELECT xmlagg(data) FROM xmltest WHERE id > 10;
- xmlagg
---------
-
-(1 row)
-
-SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp;
- xmlelement
---------------------------------------------------------------------------------------------------------------------------------
- <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees>
-(1 row)
-
--- Check mapping SQL identifier to XML name
-SELECT xmlpi(name ":::_xml_abc135.%-&_");
- xmlpi
--------------------------------------------------
- <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?>
-(1 row)
-
-SELECT xmlpi(name "123");
- xmlpi
----------------
- <?_x0031_23?>
-(1 row)
-
-PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1);
-SET XML OPTION DOCUMENT;
-EXECUTE foo ('<bar/>');
- xmlconcat
---------------
- <foo/><bar/>
-(1 row)
-
-EXECUTE foo ('bad');
-ERROR: invalid XML document
-LINE 1: EXECUTE foo ('bad');
- ^
-DETAIL: line 1: Start tag expected, '<' not found
-bad
-^
-SELECT xml '<!DOCTYPE a><a/><b/>';
-ERROR: invalid XML document
-LINE 1: SELECT xml '<!DOCTYPE a><a/><b/>';
- ^
-DETAIL: line 1: Extra content at the end of the document
-<!DOCTYPE a><a/><b/>
- ^
-SET XML OPTION CONTENT;
-EXECUTE foo ('<bar/>');
- xmlconcat
---------------
- <foo/><bar/>
-(1 row)
-
-EXECUTE foo ('good');
- xmlconcat
-------------
- <foo/>good
-(1 row)
-
-SELECT xml '<!-- in SQL:2006+ a doc is content too--> <?y z?> <!DOCTYPE a><a/>';
- xml
---------------------------------------------------------------------
- <!-- in SQL:2006+ a doc is content too--> <?y z?> <!DOCTYPE a><a/>
-(1 row)
-
-SELECT xml '<?xml version="1.0"?> <!-- hi--> <!DOCTYPE a><a/>';
- xml
-------------------------------
- <!-- hi--> <!DOCTYPE a><a/>
-(1 row)
-
-SELECT xml '<!DOCTYPE a><a/>';
- xml
-------------------
- <!DOCTYPE a><a/>
-(1 row)
-
-SELECT xml '<!-- hi--> oops <!DOCTYPE a><a/>';
-ERROR: invalid XML content
-LINE 1: SELECT xml '<!-- hi--> oops <!DOCTYPE a><a/>';
- ^
-DETAIL: line 1: StartTag: invalid element name
-<!-- hi--> oops <!DOCTYPE a><a/>
- ^
-SELECT xml '<!-- hi--> <oops/> <!DOCTYPE a><a/>';
-ERROR: invalid XML content
-LINE 1: SELECT xml '<!-- hi--> <oops/> <!DOCTYPE a><a/>';
- ^
-DETAIL: line 1: StartTag: invalid element name
-<!-- hi--> <oops/> <!DOCTYPE a><a/>
- ^
-SELECT xml '<!DOCTYPE a><a/><b/>';
-ERROR: invalid XML content
-LINE 1: SELECT xml '<!DOCTYPE a><a/><b/>';
- ^
-DETAIL: line 1: Extra content at the end of the document
-<!DOCTYPE a><a/><b/>
- ^
--- Test backwards parsing
-CREATE VIEW xmlview1 AS SELECT xmlcomment('test');
-CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you');
-CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&');
-CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
-CREATE VIEW xmlview5 AS SELECT xmlparse(content '<abc>x</abc>');
-CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
-CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
-CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10));
-CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text);
-CREATE VIEW xmlview10 AS SELECT xmlserialize(document '<foo><bar>42</bar></foo>' AS text indent);
-CREATE VIEW xmlview11 AS SELECT xmlserialize(document '<foo><bar>42</bar></foo>' AS character varying no indent);
-SELECT table_name, view_definition FROM information_schema.views
- WHERE table_name LIKE 'xmlview%' ORDER BY 1;
- table_name | view_definition
-------------+---------------------------------------------------------------------------------------------------------------------------------------
- xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment;
- xmlview10 | SELECT XMLSERIALIZE(DOCUMENT '<foo><bar>42</bar></foo>'::xml AS text INDENT) AS "xmlserialize";
- xmlview11 | SELECT (XMLSERIALIZE(DOCUMENT '<foo><bar>42</bar></foo>'::xml AS character varying NO INDENT))::character varying AS "xmlserialize";
- xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat";
- xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement";
- xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(name AS name, age AS age, salary AS pay)) AS "xmlelement" +
- | FROM emp;
- xmlview5 | SELECT XMLPARSE(CONTENT '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse";
- xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi";
- xmlview7 | SELECT XMLROOT('<foo/>'::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot";
- xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10) NO INDENT))::character(10) AS "xmlserialize";
- xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text NO INDENT) AS "xmlserialize";
-(11 rows)
-
--- Text XPath expressions evaluation
-SELECT xpath('/value', data) FROM xmltest;
- xpath
-----------------------
- {<value>one</value>}
- {<value>two</value>}
-(2 rows)
-
-SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
- ?column?
-----------
- t
- t
-(2 rows)
-
-SELECT xpath('', '<!-- error -->');
-ERROR: empty XPath expression
-CONTEXT: SQL function "xpath" statement 1
-SELECT xpath('//text()', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
- xpath
-----------------
- {"number one"}
-(1 row)
-
-SELECT xpath('//loc:piece/@id', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
- xpath
--------
- {1,2}
-(1 row)
-
-SELECT xpath('//loc:piece', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
- xpath
-------------------------------------------------------------------------------------------------------------------------------------------------
- {"<local:piece xmlns:local=\"http://127.0.0.1\" id=\"1\">number one</local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"}
-(1 row)
-
-SELECT xpath('//loc:piece', '<local:data xmlns:local="http://127.0.0.1" xmlns="http://127.0.0.2"><local:piece id="1"><internal>number one</internal><internal2/></local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
- xpath
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- {"<local:piece xmlns:local=\"http://127.0.0.1\" xmlns=\"http://127.0.0.2\" id=\"1\"><internal>number one</internal><internal2/></local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"}
-(1 row)
-
-SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
- xpath
--------------------------
- {<b>two</b>,<b>etc</b>}
-(1 row)
-
-SELECT xpath('//text()', '<root><</root>');
- xpath
---------
- {<}
-(1 row)
-
-SELECT xpath('//@value', '<root value="<"/>');
- xpath
---------
- {<}
-(1 row)
-
-SELECT xpath('''<<invalid>>''', '<root/>');
- xpath
----------------------------
- {<<invalid>>}
-(1 row)
-
-SELECT xpath('count(//*)', '<root><sub/><sub/></root>');
- xpath
--------
- {3}
-(1 row)
-
-SELECT xpath('count(//*)=0', '<root><sub/><sub/></root>');
- xpath
----------
- {false}
-(1 row)
-
-SELECT xpath('count(//*)=3', '<root><sub/><sub/></root>');
- xpath
---------
- {true}
-(1 row)
-
-SELECT xpath('name(/*)', '<root><sub/><sub/></root>');
- xpath
---------
- {root}
-(1 row)
-
-SELECT xpath('/nosuchtag', '<root/>');
- xpath
--------
- {}
-(1 row)
-
-SELECT xpath('root', '<root/>');
- xpath
------------
- {<root/>}
-(1 row)
-
--- Round-trip non-ASCII data through xpath().
-DO $$
-DECLARE
- xml_declaration text := '<?xml version="1.0" encoding="ISO-8859-1"?>';
- degree_symbol text;
- res xml[];
-BEGIN
- -- Per the documentation, except when the server encoding is UTF8, xpath()
- -- may not work on non-ASCII data. The untranslatable_character and
- -- undefined_function traps below, currently dead code, will become relevant
- -- if we remove this limitation.
- IF current_setting('server_encoding') <> 'UTF8' THEN
- RAISE LOG 'skip: encoding % unsupported for xpath',
- current_setting('server_encoding');
- RETURN;
- END IF;
-
- degree_symbol := convert_from('\xc2b0', 'UTF8');
- res := xpath('text()', (xml_declaration ||
- '<x>' || degree_symbol || '</x>')::xml);
- IF degree_symbol <> res[1]::text THEN
- RAISE 'expected % (%), got % (%)',
- degree_symbol, convert_to(degree_symbol, 'UTF8'),
- res[1], convert_to(res[1]::text, 'UTF8');
- END IF;
-EXCEPTION
- -- character with byte sequence 0xc2 0xb0 in encoding "UTF8" has no equivalent in encoding "LATIN8"
- WHEN untranslatable_character
- -- default conversion function for encoding "UTF8" to "MULE_INTERNAL" does not exist
- OR undefined_function
- -- unsupported XML feature
- OR feature_not_supported THEN
- RAISE LOG 'skip: %', SQLERRM;
-END
-$$;
--- Test xmlexists and xpath_exists
-SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
- xmlexists
------------
- f
-(1 row)
-
-SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
- xmlexists
------------
- t
-(1 row)
-
-SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>');
- xmlexists
------------
- t
-(1 row)
-
-SELECT xpath_exists('//town[text() = ''Toronto'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
- xpath_exists
---------------
- f
-(1 row)
-
-SELECT xpath_exists('//town[text() = ''Cwmbran'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
- xpath_exists
---------------
- t
-(1 row)
-
-SELECT xpath_exists('count(/nosuchtag)', '<root/>'::xml);
- xpath_exists
---------------
- t
-(1 row)
-
-INSERT INTO xmltest VALUES (4, '<menu><beers><name>Budvar</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
-INSERT INTO xmltest VALUES (5, '<menu><beers><name>Molson</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
-INSERT INTO xmltest VALUES (6, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Budvar</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
-INSERT INTO xmltest VALUES (7, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Molson</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
-SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data);
- count
--------
- 0
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data BY REF);
- count
--------
- 0
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data);
- count
--------
- 2
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data);
- count
--------
- 1
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
- count
--------
- 0
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data);
- count
--------
- 2
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data);
- count
--------
- 1
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]);
- count
--------
- 0
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]);
- count
--------
- 2
-(1 row)
-
-SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]);
- count
--------
- 1
-(1 row)
-
-CREATE TABLE query ( expr TEXT );
-INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']');
-SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data);
- count
--------
- 2
-(1 row)
-
--- Test xml_is_well_formed and variants
-SELECT xml_is_well_formed_document('<foo>bar</foo>');
- xml_is_well_formed_document
------------------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed_document('abc');
- xml_is_well_formed_document
------------------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed_content('<foo>bar</foo>');
- xml_is_well_formed_content
-----------------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed_content('abc');
- xml_is_well_formed_content
-----------------------------
- t
-(1 row)
-
-SET xmloption TO DOCUMENT;
-SELECT xml_is_well_formed('abc');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<>');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<abc/>');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed('<foo>bar</foo>');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed('<foo>bar</foo');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<foo><bar>baz</foo>');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed('<invalidentity>&</abc>');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<undefinedentity>&idontexist;</abc>');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SELECT xml_is_well_formed('<invalidns xmlns=''<''/>');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed('<relativens xmlns=''relative''/>');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
-SELECT xml_is_well_formed('<twoerrors>&idontexist;</unbalanced>');
- xml_is_well_formed
---------------------
- f
-(1 row)
-
-SET xmloption TO CONTENT;
-SELECT xml_is_well_formed('abc');
- xml_is_well_formed
---------------------
- t
-(1 row)
-
--- Since xpath() deals with namespaces, it's a bit stricter about
--- what's well-formed and what's not. If we don't obey these rules
--- (i.e. ignore namespace-related errors from libxml), xpath()
--- fails in subtle ways. The following would for example produce
--- the xml value
--- <invalidns xmlns='<'/>
--- which is invalid because '<' may not appear un-escaped in
--- attribute values.
--- Since different libxml versions emit slightly different
--- error messages, we suppress the DETAIL in this test.
-\set VERBOSITY terse
-SELECT xpath('/*', '<invalidns xmlns=''<''/>');
-ERROR: could not parse XML document
-\set VERBOSITY default
--- Again, the XML isn't well-formed for namespace purposes
-SELECT xpath('/*', '<nosuchprefix:tag/>');
-ERROR: could not parse XML document
-DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined
-<nosuchprefix:tag/>
- ^
-CONTEXT: SQL function "xpath" statement 1
--- XPath deprecates relative namespaces, but they're not supposed to
--- throw an error, only a warning.
-SELECT xpath('/*', '<relativens xmlns=''relative''/>');
-WARNING: line 1: xmlns: URI relative is not absolute
-<relativens xmlns='relative'/>
- ^
- xpath
---------------------------------------
- {"<relativens xmlns=\"relative\"/>"}
-(1 row)
-
--- External entity references should not leak filesystem information.
-SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>');
- xmlparse
------------------------------------------------------------------
- <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>
-(1 row)
-
-SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>');
- xmlparse
------------------------------------------------------------------------
- <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>
-(1 row)
-
--- This might or might not load the requested DTD, but it mustn't throw error.
-SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>');
- xmlparse
-------------------------------------------------------------------------------------------------------------------------------------------------------
- <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>
-(1 row)
-
--- XMLPATH tests
-CREATE TABLE xmldata(data xml);
-INSERT INTO xmldata VALUES('<ROWS>
-<ROW id="1">
- <COUNTRY_ID>AU</COUNTRY_ID>
- <COUNTRY_NAME>Australia</COUNTRY_NAME>
- <REGION_ID>3</REGION_ID>
-</ROW>
-<ROW id="2">
- <COUNTRY_ID>CN</COUNTRY_ID>
- <COUNTRY_NAME>China</COUNTRY_NAME>
- <REGION_ID>3</REGION_ID>
-</ROW>
-<ROW id="3">
- <COUNTRY_ID>HK</COUNTRY_ID>
- <COUNTRY_NAME>HongKong</COUNTRY_NAME>
- <REGION_ID>3</REGION_ID>
-</ROW>
-<ROW id="4">
- <COUNTRY_ID>IN</COUNTRY_ID>
- <COUNTRY_NAME>India</COUNTRY_NAME>
- <REGION_ID>3</REGION_ID>
-</ROW>
-<ROW id="5">
- <COUNTRY_ID>JP</COUNTRY_ID>
- <COUNTRY_NAME>Japan</COUNTRY_NAME>
- <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
-</ROW>
-<ROW id="6">
- <COUNTRY_ID>SG</COUNTRY_ID>
- <COUNTRY_NAME>Singapore</COUNTRY_NAME>
- <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
-</ROW>
-</ROWS>');
--- XMLTABLE with columns
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
- id | _id | country_name | country_id | region_id | size | unit | premier_name
-----+-----+--------------+------------+-----------+------+------+---------------
- 1 | 1 | Australia | AU | 3 | | | not specified
- 2 | 2 | China | CN | 3 | | | not specified
- 3 | 3 | HongKong | HK | 3 | | | not specified
- 4 | 4 | India | IN | 3 | | | not specified
- 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
- 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
-(6 rows)
-
-CREATE VIEW xmltableview1 AS SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
-SELECT * FROM xmltableview1;
- id | _id | country_name | country_id | region_id | size | unit | premier_name
-----+-----+--------------+------------+-----------+------+------+---------------
- 1 | 1 | Australia | AU | 3 | | | not specified
- 2 | 2 | China | CN | 3 | | | not specified
- 3 | 3 | HongKong | HK | 3 | | | not specified
- 4 | 4 | India | IN | 3 | | | not specified
- 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
- 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
-(6 rows)
-
-\sv xmltableview1
-CREATE OR REPLACE VIEW public.xmltableview1 AS
- SELECT "xmltable".id,
- "xmltable"._id,
- "xmltable".country_name,
- "xmltable".country_id,
- "xmltable".region_id,
- "xmltable".size,
- "xmltable".unit,
- "xmltable".premier_name
- FROM ( SELECT xmldata.data
- FROM xmldata) x,
- LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
-EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
- QUERY PLAN
------------------------------------------
- Nested Loop
- -> Seq Scan on xmldata
- -> Table Function Scan on "xmltable"
-(3 rows)
-
-EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Nested Loop
- Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
- -> Seq Scan on public.xmldata
- Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
- Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
-(7 rows)
-
--- errors
-SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp) AS f (v1, v2);
-ERROR: XMLTABLE function has 1 columns available but 2 columns specified
-SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp __pg__is_not_null 1) AS f (v1);
-ERROR: option name "__pg__is_not_null" cannot be used in XMLTABLE
-LINE 1: ...MLTABLE (ROW () PASSING null COLUMNS v1 timestamp __pg__is_n...
- ^
--- XMLNAMESPACES tests
-SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
- '/zz:rows/zz:row'
- PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
- COLUMNS a int PATH 'zz:a');
- a
-----
- 10
-(1 row)
-
-CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS "Zz"),
- '/Zz:rows/Zz:row'
- PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
- COLUMNS a int PATH 'Zz:a');
-SELECT * FROM xmltableview2;
- a
-----
- 10
-(1 row)
-
-\sv xmltableview2
-CREATE OR REPLACE VIEW public.xmltableview2 AS
- SELECT a
- FROM XMLTABLE(XMLNAMESPACES ('http://x.y'::text AS "Zz"), ('/Zz:rows/Zz:row'::text) PASSING ('<rows xmlns="http://x.y"><row><a>10</a></row></rows>'::xml) COLUMNS a integer PATH ('Zz:a'::text))
-SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
- '/rows/row'
- PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
- COLUMNS a int PATH 'a');
-ERROR: DEFAULT namespace is not supported
-SELECT * FROM XMLTABLE('.'
- PASSING '<foo/>'
- COLUMNS a text PATH 'foo/namespace::node()');
- a
---------------------------------------
- http://www.w3.org/XML/1998/namespace
-(1 row)
-
--- used in prepare statements
-PREPARE pp AS
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
-EXECUTE pp;
- id | _id | country_name | country_id | region_id | size | unit | premier_name
-----+-----+--------------+------------+-----------+------+------+---------------
- 1 | 1 | Australia | AU | 3 | | | not specified
- 2 | 2 | China | CN | 3 | | | not specified
- 3 | 3 | HongKong | HK | 3 | | | not specified
- 4 | 4 | India | IN | 3 | | | not specified
- 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
- 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
-(6 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
- COUNTRY_NAME | REGION_ID
---------------+-----------
- India | 3
- Japan | 3
-(2 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
- id | COUNTRY_NAME | REGION_ID
-----+--------------+-----------
- 1 | India | 3
- 2 | Japan | 3
-(2 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int);
- id | COUNTRY_NAME | REGION_ID
-----+--------------+-----------
- 4 | India | 3
- 5 | Japan | 3
-(2 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
- id
-----
- 4
- 5
-(2 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
- id
-----
- 1
- 2
-(2 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.');
- id | COUNTRY_NAME | REGION_ID | rawdata
-----+--------------+-----------+------------------------------------------------------------------
- 4 | India | 3 | <ROW id="4"> +
- | | | <COUNTRY_ID>IN</COUNTRY_ID> +
- | | | <COUNTRY_NAME>India</COUNTRY_NAME> +
- | | | <REGION_ID>3</REGION_ID> +
- | | | </ROW>
- 5 | Japan | 3 | <ROW id="5"> +
- | | | <COUNTRY_ID>JP</COUNTRY_ID> +
- | | | <COUNTRY_NAME>Japan</COUNTRY_NAME> +
- | | | <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>+
- | | | </ROW>
-(2 rows)
-
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*');
- id | COUNTRY_NAME | REGION_ID | rawdata
-----+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------
- 4 | India | 3 | <COUNTRY_ID>IN</COUNTRY_ID><COUNTRY_NAME>India</COUNTRY_NAME><REGION_ID>3</REGION_ID>
- 5 | Japan | 3 | <COUNTRY_ID>JP</COUNTRY_ID><COUNTRY_NAME>Japan</COUNTRY_NAME><REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
-(2 rows)
-
-SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
- element
-----------------------
- a1aa2a bbbbxxxcccc
-(1 row)
-
-SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text PATH 'element/text()'); -- should fail
-ERROR: more than one value returned by column XPath expression
--- CDATA test
-select * from xmltable('d/r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
- c
--------------------------
- <hello> &"<>!<a>foo</a>
- 2
-(2 rows)
-
--- XML builtin entities
-SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent text);
- ent
------
- '
- "
- &
- <
- >
-(5 rows)
-
-SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent xml);
- ent
-------------------
- <ent>'</ent>
- <ent>"</ent>
- <ent>&</ent>
- <ent><</ent>
- <ent>></ent>
-(5 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Nested Loop
- Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
- -> Seq Scan on public.xmldata
- Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
- Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
-(7 rows)
-
--- test qual
-SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
- COUNTRY_NAME | REGION_ID
---------------+-----------
- Japan | 3
-(1 row)
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Nested Loop
- Output: f."COUNTRY_NAME", f."REGION_ID"
- -> Seq Scan on public.xmldata
- Output: xmldata.data
- -> Table Function Scan on "xmltable" f
- Output: f."COUNTRY_NAME", f."REGION_ID"
- Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
- Filter: (f."COUNTRY_NAME" = 'Japan'::text)
-(8 rows)
-
-EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
-SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- [ +
- { +
- "Plan": { +
- "Node Type": "Nested Loop", +
- "Parallel Aware": false, +
- "Async Capable": false, +
- "Join Type": "Inner", +
- "Disabled": false, +
- "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
- "Inner Unique": false, +
- "Plans": [ +
- { +
- "Node Type": "Seq Scan", +
- "Parent Relationship": "Outer", +
- "Parallel Aware": false, +
- "Async Capable": false, +
- "Relation Name": "xmldata", +
- "Schema": "public", +
- "Alias": "xmldata", +
- "Disabled": false, +
- "Output": ["xmldata.data"] +
- }, +
- { +
- "Node Type": "Table Function Scan", +
- "Parent Relationship": "Inner", +
- "Parallel Aware": false, +
- "Async Capable": false, +
- "Table Function Name": "xmltable", +
- "Alias": "f", +
- "Disabled": false, +
- "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
- "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
- "Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
- } +
- ] +
- } +
- } +
- ]
-(1 row)
-
--- should to work with more data
-INSERT INTO xmldata VALUES('<ROWS>
-<ROW id="10">
- <COUNTRY_ID>CZ</COUNTRY_ID>
- <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
- <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
-</ROW>
-<ROW id="11">
- <COUNTRY_ID>DE</COUNTRY_ID>
- <COUNTRY_NAME>Germany</COUNTRY_NAME>
- <REGION_ID>2</REGION_ID>
-</ROW>
-<ROW id="12">
- <COUNTRY_ID>FR</COUNTRY_ID>
- <COUNTRY_NAME>France</COUNTRY_NAME>
- <REGION_ID>2</REGION_ID>
-</ROW>
-</ROWS>');
-INSERT INTO xmldata VALUES('<ROWS>
-<ROW id="20">
- <COUNTRY_ID>EG</COUNTRY_ID>
- <COUNTRY_NAME>Egypt</COUNTRY_NAME>
- <REGION_ID>1</REGION_ID>
-</ROW>
-<ROW id="21">
- <COUNTRY_ID>SD</COUNTRY_ID>
- <COUNTRY_NAME>Sudan</COUNTRY_NAME>
- <REGION_ID>1</REGION_ID>
-</ROW>
-</ROWS>');
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
- id | _id | country_name | country_id | region_id | size | unit | premier_name
-----+-----+----------------+------------+-----------+------+------+---------------
- 1 | 1 | Australia | AU | 3 | | | not specified
- 2 | 2 | China | CN | 3 | | | not specified
- 3 | 3 | HongKong | HK | 3 | | | not specified
- 4 | 4 | India | IN | 3 | | | not specified
- 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
- 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
- 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
- 11 | 2 | Germany | DE | 2 | | | not specified
- 12 | 3 | France | FR | 2 | | | not specified
- 20 | 1 | Egypt | EG | 1 | | | not specified
- 21 | 2 | Sudan | SD | 1 | | | not specified
-(11 rows)
-
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
- WHERE region_id = 2;
- id | _id | country_name | country_id | region_id | size | unit | premier_name
-----+-----+----------------+------------+-----------+------+------+---------------
- 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
- 11 | 2 | Germany | DE | 2 | | | not specified
- 12 | 3 | France | FR | 2 | | | not specified
-(3 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE',
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
- WHERE region_id = 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Nested Loop
- Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
- -> Seq Scan on public.xmldata
- Output: xmldata.data
- -> Table Function Scan on "xmltable"
- Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
- Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
- Filter: ("xmltable".region_id = 2)
-(8 rows)
-
--- should fail, NULL value
-SELECT xmltable.*
- FROM (SELECT data FROM xmldata) x,
- LATERAL XMLTABLE('/ROWS/ROW'
- PASSING data
- COLUMNS id int PATH '@id',
- _id FOR ORDINALITY,
- country_name text PATH 'COUNTRY_NAME' NOT NULL,
- country_id text PATH 'COUNTRY_ID',
- region_id int PATH 'REGION_ID',
- size float PATH 'SIZE' NOT NULL,
- unit text PATH 'SIZE/@unit',
- premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
-ERROR: null is not allowed in column "size"
--- if all is ok, then result is empty
--- one line xml test
-WITH
- x AS (SELECT proname, proowner, procost::numeric, pronargs,
- array_to_string(proargnames,',') as proargnames,
- case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
- FROM pg_proc WHERE proname = 'f_leak'),
- y AS (SELECT xmlelement(name proc,
- xmlforest(proname, proowner,
- procost, pronargs,
- proargnames, proargtypes)) as proc
- FROM x),
- z AS (SELECT xmltable.*
- FROM y,
- LATERAL xmltable('/proc' PASSING proc
- COLUMNS proname name,
- proowner oid,
- procost float,
- pronargs int,
- proargnames text,
- proargtypes text))
- SELECT * FROM z
- EXCEPT SELECT * FROM x;
- proname | proowner | procost | pronargs | proargnames | proargtypes
----------+----------+---------+----------+-------------+-------------
-(0 rows)
-
--- multi line xml test, result should be empty too
-WITH
- x AS (SELECT proname, proowner, procost::numeric, pronargs,
- array_to_string(proargnames,',') as proargnames,
- case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
- FROM pg_proc),
- y AS (SELECT xmlelement(name data,
- xmlagg(xmlelement(name proc,
- xmlforest(proname, proowner, procost,
- pronargs, proargnames, proargtypes)))) as doc
- FROM x),
- z AS (SELECT xmltable.*
- FROM y,
- LATERAL xmltable('/data/proc' PASSING doc
- COLUMNS proname name,
- proowner oid,
- procost float,
- pronargs int,
- proargnames text,
- proargtypes text))
- SELECT * FROM z
- EXCEPT SELECT * FROM x;
- proname | proowner | procost | pronargs | proargnames | proargtypes
----------+----------+---------+----------+-------------+-------------
-(0 rows)
-
-CREATE TABLE xmltest2(x xml, _path text);
-INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
-INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
-INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
-INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D');
-SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c');
- a
----
- 1
- 2
- 3
- 2
-(4 rows)
-
-SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
- a
----
- 1
- 2
- 3
- 2
-(4 rows)
-
-SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
- a
-----
- 11
- 12
- 13
- 14
-(4 rows)
-
--- XPath result can be boolean or number too
-SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"', e integer PATH 'string-length(.)');
- a | b | c | d | e
-----------+---+----+---+---
- <a>a</a> | a | hi | t | 1
-(1 row)
-
-\x
-SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH '/e/n2', y xml PATH '/');
--[ RECORD 1 ]-----------------------------------------------------------
-x | <n2>&deep</n2>
-y | <e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>+
- |
-
-\x
-SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '"<foo/>"', b xml PATH '"<foo/>"');
- a | b
---------+--------------
- <foo/> | <foo/>
-(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 & <"bar">
-(1 row)
-
-SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
- xmltext
----------------------------------
- x<P>73</P>0.42truej
-(1 row)
-
+CREATE TABLE xmltest (
+ id int,
+ data xml
+);
+INSERT INTO xmltest VALUES (1, '<value>one</value>');
+INSERT INTO xmltest VALUES (2, '<value>two</value>');
+INSERT INTO xmltest VALUES (3, '<wrong');
+ERROR: invalid XML content
+LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
+ ^
+DETAIL: line 1: Couldn't find end of Start Tag wrong line 1
+<wrong
+ ^
+SELECT * FROM xmltest;
+ id | data
+----+--------------------
+ 1 | <value>one</value>
+ 2 | <value>two</value>
+(2 rows)
+
+-- test non-throwing API, too
+SELECT pg_input_is_valid('<value>one</value>', 'xml');
+ pg_input_is_valid
+-------------------
+ t
+(1 row)
+
+SELECT pg_input_is_valid('<value>one</', 'xml');
+ pg_input_is_valid
+-------------------
+ f
+(1 row)
+
+SELECT message FROM pg_input_error_info('<value>one</', 'xml');
+ message
+---------------------
+ invalid XML content
+(1 row)
+
+SELECT pg_input_is_valid('<?xml version="1.0" standalone="y"?><foo/>', 'xml');
+ pg_input_is_valid
+-------------------
+ f
+(1 row)
+
+SELECT message FROM pg_input_error_info('<?xml version="1.0" standalone="y"?><foo/>', 'xml');
+ message
+----------------------------------------------
+ invalid XML content: invalid XML declaration
+(1 row)
+
+SELECT xmlcomment('test');
+ xmlcomment
+-------------
+ <!--test-->
+(1 row)
+
+SELECT xmlcomment('-test');
+ xmlcomment
+--------------
+ <!---test-->
+(1 row)
+
+SELECT xmlcomment('test-');
+ERROR: invalid XML comment
+SELECT xmlcomment('--test');
+ERROR: invalid XML comment
+SELECT xmlcomment('te st');
+ xmlcomment
+--------------
+ <!--te st-->
+(1 row)
+
+SELECT xmlconcat(xmlcomment('hello'),
+ xmlelement(NAME qux, 'foo'),
+ xmlcomment('world'));
+ xmlconcat
+----------------------------------------
+ <!--hello--><qux>foo</qux><!--world-->
+(1 row)
+
+SELECT xmlconcat('hello', 'you');
+ xmlconcat
+-----------
+ helloyou
+(1 row)
+
+SELECT xmlconcat(1, 2);
+ERROR: argument of XMLCONCAT must be type xml, not type integer
+LINE 1: SELECT xmlconcat(1, 2);
+ ^
+SELECT xmlconcat('bad', '<syntax');
+ERROR: invalid XML content
+LINE 1: SELECT xmlconcat('bad', '<syntax');
+ ^
+DETAIL: line 1: Couldn't find end of Start Tag syntax line 1
+<syntax
+ ^
+SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
+ xmlconcat
+--------------
+ <foo/><bar/>
+(1 row)
+
+SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
+ xmlconcat
+-----------------------------------
+ <?xml version="1.1"?><foo/><bar/>
+(1 row)
+
+SELECT xmlconcat(NULL);
+ xmlconcat
+-----------
+
+(1 row)
+
+SELECT xmlconcat(NULL, NULL);
+ xmlconcat
+-----------
+
+(1 row)
+
+SELECT xmlelement(name element,
+ xmlattributes (1 as one, 'deuce' as two),
+ 'content');
+ xmlelement
+------------------------------------------------
+ <element one="1" two="deuce">content</element>
+(1 row)
+
+SELECT xmlelement(name element,
+ xmlattributes ('unnamed and wrong'));
+ERROR: unnamed XML attribute value must be a column reference
+LINE 2: xmlattributes ('unnamed and wrong'));
+ ^
+SELECT xmlelement(name element, xmlelement(name nested, 'stuff'));
+ xmlelement
+-------------------------------------------
+ <element><nested>stuff</nested></element>
+(1 row)
+
+SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
+ xmlelement
+----------------------------------------------------------------------
+ <employee><name>sharon</name><age>25</age><pay>1000</pay></employee>
+ <employee><name>sam</name><age>30</age><pay>2000</pay></employee>
+ <employee><name>bill</name><age>20</age><pay>1000</pay></employee>
+ <employee><name>jeff</name><age>23</age><pay>600</pay></employee>
+ <employee><name>cim</name><age>30</age><pay>400</pay></employee>
+ <employee><name>linda</name><age>19</age><pay>100</pay></employee>
+(6 rows)
+
+SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
+ERROR: XML attribute name "a" appears more than once
+LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
+ ^
+SELECT xmlelement(name num, 37);
+ xmlelement
+---------------
+ <num>37</num>
+(1 row)
+
+SELECT xmlelement(name foo, text 'bar');
+ xmlelement
+----------------
+ <foo>bar</foo>
+(1 row)
+
+SELECT xmlelement(name foo, xml 'bar');
+ xmlelement
+----------------
+ <foo>bar</foo>
+(1 row)
+
+SELECT xmlelement(name foo, text 'b<a/>r');
+ xmlelement
+-------------------------
+ <foo>b<a/>r</foo>
+(1 row)
+
+SELECT xmlelement(name foo, xml 'b<a/>r');
+ xmlelement
+-------------------
+ <foo>b<a/>r</foo>
+(1 row)
+
+SELECT xmlelement(name foo, array[1, 2, 3]);
+ xmlelement
+-------------------------------------------------------------------------
+ <foo><element>1</element><element>2</element><element>3</element></foo>
+(1 row)
+
+SET xmlbinary TO base64;
+SELECT xmlelement(name foo, bytea 'bar');
+ xmlelement
+-----------------
+ <foo>YmFy</foo>
+(1 row)
+
+SET xmlbinary TO hex;
+SELECT xmlelement(name foo, bytea 'bar');
+ xmlelement
+-------------------
+ <foo>626172</foo>
+(1 row)
+
+SELECT xmlelement(name foo, xmlattributes(true as bar));
+ xmlelement
+-------------------
+ <foo bar="true"/>
+(1 row)
+
+SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar));
+ xmlelement
+----------------------------------
+ <foo bar="2009-04-09T00:24:37"/>
+(1 row)
+
+SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar));
+ERROR: timestamp out of range
+DETAIL: XML does not support infinite timestamp values.
+SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'b<a/>r' as funnier));
+ xmlelement
+------------------------------------------------------------
+ <foo funny="<>&"'" funnier="b<a/>r"/>
+(1 row)
+
+SELECT xmlparse(content '');
+ xmlparse
+----------
+
+(1 row)
+
+SELECT xmlparse(content ' ');
+ xmlparse
+----------
+
+(1 row)
+
+SELECT xmlparse(content 'abc');
+ xmlparse
+----------
+ abc
+(1 row)
+
+SELECT xmlparse(content '<abc>x</abc>');
+ xmlparse
+--------------
+ <abc>x</abc>
+(1 row)
+
+SELECT xmlparse(content '<invalidentity>&</invalidentity>');
+ERROR: invalid XML content
+DETAIL: line 1: xmlParseEntityRef: no name
+<invalidentity>&</invalidentity>
+ ^
+SELECT xmlparse(content '<undefinedentity>&idontexist;</undefinedentity>');
+ERROR: invalid XML content
+DETAIL: line 1: Entity 'idontexist' not defined
+<undefinedentity>&idontexist;</undefinedentity>
+ ^
+SELECT xmlparse(content '<invalidns xmlns=''<''/>');
+ xmlparse
+---------------------------
+ <invalidns xmlns='<'/>
+(1 row)
+
+SELECT xmlparse(content '<relativens xmlns=''relative''/>');
+ xmlparse
+--------------------------------
+ <relativens xmlns='relative'/>
+(1 row)
+
+SELECT xmlparse(content '<twoerrors>&idontexist;</unbalanced>');
+ERROR: invalid XML content
+DETAIL: line 1: Entity 'idontexist' not defined
+<twoerrors>&idontexist;</unbalanced>
+ ^
+line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
+<twoerrors>&idontexist;</unbalanced>
+ ^
+SELECT xmlparse(content '<nosuchprefix:tag/>');
+ xmlparse
+---------------------
+ <nosuchprefix:tag/>
+(1 row)
+
+SELECT xmlparse(document ' ');
+ERROR: invalid XML document
+DETAIL: line 1: Start tag expected, '<' not found
+
+ ^
+SELECT xmlparse(document 'abc');
+ERROR: invalid XML document
+DETAIL: line 1: Start tag expected, '<' not found
+abc
+^
+SELECT xmlparse(document '<abc>x</abc>');
+ xmlparse
+--------------
+ <abc>x</abc>
+(1 row)
+
+SELECT xmlparse(document '<invalidentity>&</abc>');
+ERROR: invalid XML document
+DETAIL: line 1: xmlParseEntityRef: no name
+<invalidentity>&</abc>
+ ^
+line 1: Opening and ending tag mismatch: invalidentity line 1 and abc
+<invalidentity>&</abc>
+ ^
+SELECT xmlparse(document '<undefinedentity>&idontexist;</abc>');
+ERROR: invalid XML document
+DETAIL: line 1: Entity 'idontexist' not defined
+<undefinedentity>&idontexist;</abc>
+ ^
+line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc
+<undefinedentity>&idontexist;</abc>
+ ^
+SELECT xmlparse(document '<invalidns xmlns=''<''/>');
+ xmlparse
+---------------------------
+ <invalidns xmlns='<'/>
+(1 row)
+
+SELECT xmlparse(document '<relativens xmlns=''relative''/>');
+ xmlparse
+--------------------------------
+ <relativens xmlns='relative'/>
+(1 row)
+
+SELECT xmlparse(document '<twoerrors>&idontexist;</unbalanced>');
+ERROR: invalid XML document
+DETAIL: line 1: Entity 'idontexist' not defined
+<twoerrors>&idontexist;</unbalanced>
+ ^
+line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
+<twoerrors>&idontexist;</unbalanced>
+ ^
+SELECT xmlparse(document '<nosuchprefix:tag/>');
+ xmlparse
+---------------------
+ <nosuchprefix:tag/>
+(1 row)
+
+SELECT xmlpi(name foo);
+ xmlpi
+---------
+ <?foo?>
+(1 row)
+
+SELECT xmlpi(name xml);
+ERROR: invalid XML processing instruction
+DETAIL: XML processing instruction target name cannot be "xml".
+SELECT xmlpi(name xmlstuff);
+ xmlpi
+--------------
+ <?xmlstuff?>
+(1 row)
+
+SELECT xmlpi(name foo, 'bar');
+ xmlpi
+-------------
+ <?foo bar?>
+(1 row)
+
+SELECT xmlpi(name foo, 'in?>valid');
+ERROR: invalid XML processing instruction
+DETAIL: XML processing instruction cannot contain "?>".
+SELECT xmlpi(name foo, null);
+ xmlpi
+-------
+
+(1 row)
+
+SELECT xmlpi(name xml, null);
+ERROR: invalid XML processing instruction
+DETAIL: XML processing instruction target name cannot be "xml".
+SELECT xmlpi(name xmlstuff, null);
+ xmlpi
+-------
+
+(1 row)
+
+SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"');
+ xmlpi
+-------------------------------------------------------
+ <?xml-stylesheet href="mystyle.css" type="text/css"?>
+(1 row)
+
+SELECT xmlpi(name foo, ' bar');
+ xmlpi
+-------------
+ <?foo bar?>
+(1 row)
+
+SELECT xmlroot(xml '<foo/>', version no value, standalone no value);
+ xmlroot
+---------
+ <foo/>
+(1 row)
+
+SELECT xmlroot(xml '<foo/>', version '2.0');
+ xmlroot
+-----------------------------
+ <?xml version="2.0"?><foo/>
+(1 row)
+
+SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
+ xmlroot
+----------------------------------------------
+ <?xml version="1.0" standalone="yes"?><foo/>
+(1 row)
+
+SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes);
+ xmlroot
+----------------------------------------------
+ <?xml version="1.0" standalone="yes"?><foo/>
+(1 row)
+
+SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no);
+ xmlroot
+---------------------------------------------
+ <?xml version="1.1" standalone="no"?><foo/>
+(1 row)
+
+SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
+ xmlroot
+---------------------------------------------
+ <?xml version="1.0" standalone="no"?><foo/>
+(1 row)
+
+SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
+ xmlroot
+---------
+ <foo/>
+(1 row)
+
+SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value);
+ xmlroot
+----------------------------------------------
+ <?xml version="1.0" standalone="yes"?><foo/>
+(1 row)
+
+SELECT xmlroot (
+ xmlelement (
+ name gazonk,
+ xmlattributes (
+ 'val' AS name,
+ 1 + 1 AS num
+ ),
+ xmlelement (
+ NAME qux,
+ 'foo'
+ )
+ ),
+ version '1.0',
+ standalone yes
+);
+ xmlroot
+------------------------------------------------------------------------------------------
+ <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk>
+(1 row)
+
+SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
+ xmlserialize
+--------------------
+ <value>one</value>
+ <value>two</value>
+(2 rows)
+
+SELECT xmlserialize(content 'good' as char(10));
+ xmlserialize
+--------------
+ good
+(1 row)
+
+SELECT xmlserialize(document 'bad' as text);
+ERROR: not an XML document
+-- indent
+SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text INDENT);
+ xmlserialize
+-------------------------
+ <foo> +
+ <bar> +
+ <val x="y">42</val>+
+ </bar> +
+ </foo>
+(1 row)
+
+SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text INDENT);
+ xmlserialize
+-------------------------
+ <foo> +
+ <bar> +
+ <val x="y">42</val>+
+ </bar> +
+ </foo>
+(1 row)
+
+-- no indent
+SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
+ xmlserialize
+-------------------------------------------
+ <foo><bar><val x="y">42</val></bar></foo>
+(1 row)
+
+SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
+ xmlserialize
+-------------------------------------------
+ <foo><bar><val x="y">42</val></bar></foo>
+(1 row)
+
+-- indent non singly-rooted xml
+SELECT xmlserialize(DOCUMENT '<foo>73</foo><bar><val x="y">42</val></bar>' AS text INDENT);
+ERROR: not an XML document
+SELECT xmlserialize(CONTENT '<foo>73</foo><bar><val x="y">42</val></bar>' AS text INDENT);
+ xmlserialize
+-----------------------
+ <foo>73</foo> +
+ <bar> +
+ <val x="y">42</val>+
+ </bar>
+(1 row)
+
+-- indent non singly-rooted xml with mixed contents
+SELECT xmlserialize(DOCUMENT 'text node<foo>73</foo>text node<bar><val x="y">42</val></bar>' AS text INDENT);
+ERROR: not an XML document
+SELECT xmlserialize(CONTENT 'text node<foo>73</foo>text node<bar><val x="y">42</val></bar>' AS text INDENT);
+ xmlserialize
+------------------------
+ text node +
+ <foo>73</foo>text node+
+ <bar> +
+ <val x="y">42</val> +
+ </bar>
+(1 row)
+
+-- indent singly-rooted xml with mixed contents
+SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val><val x="y">text node<val>73</val></val></bar></foo>' AS text INDENT);
+ xmlserialize
+---------------------------------------------
+ <foo> +
+ <bar> +
+ <val x="y">42</val> +
+ <val x="y">text node<val>73</val></val>+
+ </bar> +
+ </foo>
+(1 row)
+
+SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val><val x="y">text node<val>73</val></val></bar></foo>' AS text INDENT);
+ xmlserialize
+---------------------------------------------
+ <foo> +
+ <bar> +
+ <val x="y">42</val> +
+ <val x="y">text node<val>73</val></val>+
+ </bar> +
+ </foo>
+(1 row)
+
+-- indent empty string
+SELECT xmlserialize(DOCUMENT '' AS text INDENT);
+ERROR: not an XML document
+SELECT xmlserialize(CONTENT '' AS text INDENT);
+ xmlserialize
+--------------
+
+(1 row)
+
+-- whitespaces
+SELECT xmlserialize(DOCUMENT ' ' AS text INDENT);
+ERROR: not an XML document
+SELECT xmlserialize(CONTENT ' ' AS text INDENT);
+ xmlserialize
+--------------
+
+(1 row)
+
+-- indent null
+SELECT xmlserialize(DOCUMENT NULL AS text INDENT);
+ xmlserialize
+--------------
+
+(1 row)
+
+SELECT xmlserialize(CONTENT NULL AS text INDENT);
+ xmlserialize
+--------------
+
+(1 row)
+
+-- indent with XML declaration
+SELECT xmlserialize(DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><foo><bar><val>73</val></bar></foo>' AS text INDENT);
+ xmlserialize
+----------------------------------------
+ <?xml version="1.0" encoding="UTF-8"?>+
+ <foo> +
+ <bar> +
+ <val>73</val> +
+ </bar> +
+ </foo>
+(1 row)
+
+SELECT xmlserialize(CONTENT '<?xml version="1.0" encoding="UTF-8"?><foo><bar><val>73</val></bar></foo>' AS text INDENT);
+ xmlserialize
+-------------------
+ <foo> +
+ <bar> +
+ <val>73</val>+
+ </bar> +
+ </foo>
+(1 row)
+
+-- indent containing DOCTYPE declaration
+SELECT xmlserialize(DOCUMENT '<!DOCTYPE a><a/>' AS text INDENT);
+ xmlserialize
+--------------
+ <!DOCTYPE a>+
+ <a/>
+(1 row)
+
+SELECT xmlserialize(CONTENT '<!DOCTYPE a><a/>' AS text INDENT);
+ xmlserialize
+--------------
+ <!DOCTYPE a>+
+ <a/> +
+
+(1 row)
+
+-- indent xml with empty element
+SELECT xmlserialize(DOCUMENT '<foo><bar></bar></foo>' AS text INDENT);
+ xmlserialize
+--------------
+ <foo> +
+ <bar/> +
+ </foo>
+(1 row)
+
+SELECT xmlserialize(CONTENT '<foo><bar></bar></foo>' AS text INDENT);
+ xmlserialize
+--------------
+ <foo> +
+ <bar/> +
+ </foo>
+(1 row)
+
+-- 'no indent' = not using 'no indent'
+SELECT xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text) = xmlserialize(DOCUMENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text) = xmlserialize(CONTENT '<foo><bar><val x="y">42</val></bar></foo>' AS text NO INDENT);
+ ?column?
+----------
+ t
+(1 row)
+
+-- indent xml strings containing blank nodes
+SELECT xmlserialize(DOCUMENT '<foo> <bar></bar> </foo>' AS text INDENT);
+ xmlserialize
+--------------
+ <foo> +
+ <bar/> +
+ </foo>
+(1 row)
+
+SELECT xmlserialize(CONTENT 'text node<foo> <bar></bar> </foo>' AS text INDENT);
+ xmlserialize
+--------------
+ text node +
+ <foo> +
+ <bar/> +
+ </foo>
+(1 row)
+
+SELECT xml '<foo>bar</foo>' IS DOCUMENT;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT xml '<abc/>' IS NOT DOCUMENT;
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT xml 'abc' IS NOT DOCUMENT;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '<>' IS NOT DOCUMENT;
+ERROR: invalid XML content
+LINE 1: SELECT '<>' IS NOT DOCUMENT;
+ ^
+DETAIL: line 1: StartTag: invalid element name
+<>
+ ^
+SELECT xmlagg(data) FROM xmltest;
+ xmlagg
+--------------------------------------
+ <value>one</value><value>two</value>
+(1 row)
+
+SELECT xmlagg(data) FROM xmltest WHERE id > 10;
+ xmlagg
+--------
+
+(1 row)
+
+SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp;
+ xmlelement
+--------------------------------------------------------------------------------------------------------------------------------
+ <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees>
+(1 row)
+
+-- Check mapping SQL identifier to XML name
+SELECT xmlpi(name ":::_xml_abc135.%-&_");
+ xmlpi
+-------------------------------------------------
+ <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?>
+(1 row)
+
+SELECT xmlpi(name "123");
+ xmlpi
+---------------
+ <?_x0031_23?>
+(1 row)
+
+PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1);
+SET XML OPTION DOCUMENT;
+EXECUTE foo ('<bar/>');
+ xmlconcat
+--------------
+ <foo/><bar/>
+(1 row)
+
+EXECUTE foo ('bad');
+ERROR: invalid XML document
+LINE 1: EXECUTE foo ('bad');
+ ^
+DETAIL: line 1: Start tag expected, '<' not found
+bad
+^
+SELECT xml '<!DOCTYPE a><a/><b/>';
+ERROR: invalid XML document
+LINE 1: SELECT xml '<!DOCTYPE a><a/><b/>';
+ ^
+DETAIL: line 1: Extra content at the end of the document
+<!DOCTYPE a><a/><b/>
+ ^
+SET XML OPTION CONTENT;
+EXECUTE foo ('<bar/>');
+ xmlconcat
+--------------
+ <foo/><bar/>
+(1 row)
+
+EXECUTE foo ('good');
+ xmlconcat
+------------
+ <foo/>good
+(1 row)
+
+SELECT xml '<!-- in SQL:2006+ a doc is content too--> <?y z?> <!DOCTYPE a><a/>';
+ xml
+--------------------------------------------------------------------
+ <!-- in SQL:2006+ a doc is content too--> <?y z?> <!DOCTYPE a><a/>
+(1 row)
+
+SELECT xml '<?xml version="1.0"?> <!-- hi--> <!DOCTYPE a><a/>';
+ xml
+------------------------------
+ <!-- hi--> <!DOCTYPE a><a/>
+(1 row)
+
+SELECT xml '<!DOCTYPE a><a/>';
+ xml
+------------------
+ <!DOCTYPE a><a/>
+(1 row)
+
+SELECT xml '<!-- hi--> oops <!DOCTYPE a><a/>';
+ERROR: invalid XML content
+LINE 1: SELECT xml '<!-- hi--> oops <!DOCTYPE a><a/>';
+ ^
+DETAIL: line 1: StartTag: invalid element name
+<!-- hi--> oops <!DOCTYPE a><a/>
+ ^
+SELECT xml '<!-- hi--> <oops/> <!DOCTYPE a><a/>';
+ERROR: invalid XML content
+LINE 1: SELECT xml '<!-- hi--> <oops/> <!DOCTYPE a><a/>';
+ ^
+DETAIL: line 1: StartTag: invalid element name
+<!-- hi--> <oops/> <!DOCTYPE a><a/>
+ ^
+SELECT xml '<!DOCTYPE a><a/><b/>';
+ERROR: invalid XML content
+LINE 1: SELECT xml '<!DOCTYPE a><a/><b/>';
+ ^
+DETAIL: line 1: Extra content at the end of the document
+<!DOCTYPE a><a/><b/>
+ ^
+-- Test backwards parsing
+CREATE VIEW xmlview1 AS SELECT xmlcomment('test');
+CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you');
+CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&');
+CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
+CREATE VIEW xmlview5 AS SELECT xmlparse(content '<abc>x</abc>');
+CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
+CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
+CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10));
+CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text);
+CREATE VIEW xmlview10 AS SELECT xmlserialize(document '<foo><bar>42</bar></foo>' AS text indent);
+CREATE VIEW xmlview11 AS SELECT xmlserialize(document '<foo><bar>42</bar></foo>' AS character varying no indent);
+SELECT table_name, view_definition FROM information_schema.views
+ WHERE table_name LIKE 'xmlview%' ORDER BY 1;
+ table_name | view_definition
+------------+---------------------------------------------------------------------------------------------------------------------------------------
+ xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment;
+ xmlview10 | SELECT XMLSERIALIZE(DOCUMENT '<foo><bar>42</bar></foo>'::xml AS text INDENT) AS "xmlserialize";
+ xmlview11 | SELECT (XMLSERIALIZE(DOCUMENT '<foo><bar>42</bar></foo>'::xml AS character varying NO INDENT))::character varying AS "xmlserialize";
+ xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat";
+ xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement";
+ xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(name AS name, age AS age, salary AS pay)) AS "xmlelement" +
+ | FROM emp;
+ xmlview5 | SELECT XMLPARSE(CONTENT '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse";
+ xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi";
+ xmlview7 | SELECT XMLROOT('<foo/>'::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot";
+ xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10) NO INDENT))::character(10) AS "xmlserialize";
+ xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text NO INDENT) AS "xmlserialize";
+(11 rows)
+
+-- Text XPath expressions evaluation
+SELECT xpath('/value', data) FROM xmltest;
+ xpath
+----------------------
+ {<value>one</value>}
+ {<value>two</value>}
+(2 rows)
+
+SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
+ ?column?
+----------
+ t
+ t
+(2 rows)
+
+SELECT xpath('', '<!-- error -->');
+ERROR: empty XPath expression
+CONTEXT: SQL function "xpath" statement 1
+SELECT xpath('//text()', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
+ xpath
+----------------
+ {"number one"}
+(1 row)
+
+SELECT xpath('//loc:piece/@id', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
+ xpath
+-------
+ {1,2}
+(1 row)
+
+SELECT xpath('//loc:piece', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
+ xpath
+------------------------------------------------------------------------------------------------------------------------------------------------
+ {"<local:piece xmlns:local=\"http://127.0.0.1\" id=\"1\">number one</local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"}
+(1 row)
+
+SELECT xpath('//loc:piece', '<local:data xmlns:local="http://127.0.0.1" xmlns="http://127.0.0.2"><local:piece id="1"><internal>number one</internal><internal2/></local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
+ xpath
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ {"<local:piece xmlns:local=\"http://127.0.0.1\" xmlns=\"http://127.0.0.2\" id=\"1\"><internal>number one</internal><internal2/></local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"}
+(1 row)
+
+SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
+ xpath
+-------------------------
+ {<b>two</b>,<b>etc</b>}
+(1 row)
+
+SELECT xpath('//text()', '<root><</root>');
+ xpath
+--------
+ {<}
+(1 row)
+
+SELECT xpath('//@value', '<root value="<"/>');
+ xpath
+--------
+ {<}
+(1 row)
+
+SELECT xpath('''<<invalid>>''', '<root/>');
+ xpath
+---------------------------
+ {<<invalid>>}
+(1 row)
+
+SELECT xpath('count(//*)', '<root><sub/><sub/></root>');
+ xpath
+-------
+ {3}
+(1 row)
+
+SELECT xpath('count(//*)=0', '<root><sub/><sub/></root>');
+ xpath
+---------
+ {false}
+(1 row)
+
+SELECT xpath('count(//*)=3', '<root><sub/><sub/></root>');
+ xpath
+--------
+ {true}
+(1 row)
+
+SELECT xpath('name(/*)', '<root><sub/><sub/></root>');
+ xpath
+--------
+ {root}
+(1 row)
+
+SELECT xpath('/nosuchtag', '<root/>');
+ xpath
+-------
+ {}
+(1 row)
+
+SELECT xpath('root', '<root/>');
+ xpath
+-----------
+ {<root/>}
+(1 row)
+
+-- Round-trip non-ASCII data through xpath().
+DO $$
+DECLARE
+ xml_declaration text := '<?xml version="1.0" encoding="ISO-8859-1"?>';
+ degree_symbol text;
+ res xml[];
+BEGIN
+ -- Per the documentation, except when the server encoding is UTF8, xpath()
+ -- may not work on non-ASCII data. The untranslatable_character and
+ -- undefined_function traps below, currently dead code, will become relevant
+ -- if we remove this limitation.
+ IF current_setting('server_encoding') <> 'UTF8' THEN
+ RAISE LOG 'skip: encoding % unsupported for xpath',
+ current_setting('server_encoding');
+ RETURN;
+ END IF;
+
+ degree_symbol := convert_from('\xc2b0', 'UTF8');
+ res := xpath('text()', (xml_declaration ||
+ '<x>' || degree_symbol || '</x>')::xml);
+ IF degree_symbol <> res[1]::text THEN
+ RAISE 'expected % (%), got % (%)',
+ degree_symbol, convert_to(degree_symbol, 'UTF8'),
+ res[1], convert_to(res[1]::text, 'UTF8');
+ END IF;
+EXCEPTION
+ -- character with byte sequence 0xc2 0xb0 in encoding "UTF8" has no equivalent in encoding "LATIN8"
+ WHEN untranslatable_character
+ -- default conversion function for encoding "UTF8" to "MULE_INTERNAL" does not exist
+ OR undefined_function
+ -- unsupported XML feature
+ OR feature_not_supported THEN
+ RAISE LOG 'skip: %', SQLERRM;
+END
+$$;
+-- Test xmlexists and xpath_exists
+SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
+ xmlexists
+-----------
+ f
+(1 row)
+
+SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
+ xmlexists
+-----------
+ t
+(1 row)
+
+SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>');
+ xmlexists
+-----------
+ t
+(1 row)
+
+SELECT xpath_exists('//town[text() = ''Toronto'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
+ xpath_exists
+--------------
+ f
+(1 row)
+
+SELECT xpath_exists('//town[text() = ''Cwmbran'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
+ xpath_exists
+--------------
+ t
+(1 row)
+
+SELECT xpath_exists('count(/nosuchtag)', '<root/>'::xml);
+ xpath_exists
+--------------
+ t
+(1 row)
+
+INSERT INTO xmltest VALUES (4, '<menu><beers><name>Budvar</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
+INSERT INTO xmltest VALUES (5, '<menu><beers><name>Molson</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
+INSERT INTO xmltest VALUES (6, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Budvar</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
+INSERT INTO xmltest VALUES (7, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Molson</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
+SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data);
+ count
+-------
+ 0
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data BY REF);
+ count
+-------
+ 0
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data);
+ count
+-------
+ 2
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data);
+ count
+-------
+ 1
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
+ count
+-------
+ 0
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data);
+ count
+-------
+ 2
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data);
+ count
+-------
+ 1
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]);
+ count
+-------
+ 0
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]);
+ count
+-------
+ 2
+(1 row)
+
+SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]);
+ count
+-------
+ 1
+(1 row)
+
+CREATE TABLE query ( expr TEXT );
+INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']');
+SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data);
+ count
+-------
+ 2
+(1 row)
+
+-- Test xml_is_well_formed and variants
+SELECT xml_is_well_formed_document('<foo>bar</foo>');
+ xml_is_well_formed_document
+-----------------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed_document('abc');
+ xml_is_well_formed_document
+-----------------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed_content('<foo>bar</foo>');
+ xml_is_well_formed_content
+----------------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed_content('abc');
+ xml_is_well_formed_content
+----------------------------
+ t
+(1 row)
+
+SET xmloption TO DOCUMENT;
+SELECT xml_is_well_formed('abc');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<>');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<abc/>');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed('<foo>bar</foo>');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed('<foo>bar</foo');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<foo><bar>baz</foo>');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed('<invalidentity>&</abc>');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<undefinedentity>&idontexist;</abc>');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SELECT xml_is_well_formed('<invalidns xmlns=''<''/>');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed('<relativens xmlns=''relative''/>');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+SELECT xml_is_well_formed('<twoerrors>&idontexist;</unbalanced>');
+ xml_is_well_formed
+--------------------
+ f
+(1 row)
+
+SET xmloption TO CONTENT;
+SELECT xml_is_well_formed('abc');
+ xml_is_well_formed
+--------------------
+ t
+(1 row)
+
+-- Since xpath() deals with namespaces, it's a bit stricter about
+-- what's well-formed and what's not. If we don't obey these rules
+-- (i.e. ignore namespace-related errors from libxml), xpath()
+-- fails in subtle ways. The following would for example produce
+-- the xml value
+-- <invalidns xmlns='<'/>
+-- which is invalid because '<' may not appear un-escaped in
+-- attribute values.
+-- Since different libxml versions emit slightly different
+-- error messages, we suppress the DETAIL in this test.
+\set VERBOSITY terse
+SELECT xpath('/*', '<invalidns xmlns=''<''/>');
+ERROR: could not parse XML document
+\set VERBOSITY default
+-- Again, the XML isn't well-formed for namespace purposes
+SELECT xpath('/*', '<nosuchprefix:tag/>');
+ERROR: could not parse XML document
+DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined
+<nosuchprefix:tag/>
+ ^
+CONTEXT: SQL function "xpath" statement 1
+-- XPath deprecates relative namespaces, but they're not supposed to
+-- throw an error, only a warning.
+SELECT xpath('/*', '<relativens xmlns=''relative''/>');
+WARNING: line 1: xmlns: URI relative is not absolute
+<relativens xmlns='relative'/>
+ ^
+ xpath
+--------------------------------------
+ {"<relativens xmlns=\"relative\"/>"}
+(1 row)
+
+-- External entity references should not leak filesystem information.
+SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>');
+ xmlparse
+-----------------------------------------------------------------
+ <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>
+(1 row)
+
+SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>');
+ xmlparse
+-----------------------------------------------------------------------
+ <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>
+(1 row)
+
+-- This might or might not load the requested DTD, but it mustn't throw error.
+SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>');
+ xmlparse
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>
+(1 row)
+
+-- XMLPATH tests
+CREATE TABLE xmldata(data xml);
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="1">
+ <COUNTRY_ID>AU</COUNTRY_ID>
+ <COUNTRY_NAME>Australia</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="2">
+ <COUNTRY_ID>CN</COUNTRY_ID>
+ <COUNTRY_NAME>China</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="3">
+ <COUNTRY_ID>HK</COUNTRY_ID>
+ <COUNTRY_NAME>HongKong</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="4">
+ <COUNTRY_ID>IN</COUNTRY_ID>
+ <COUNTRY_NAME>India</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID>
+</ROW>
+<ROW id="5">
+ <COUNTRY_ID>JP</COUNTRY_ID>
+ <COUNTRY_NAME>Japan</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+</ROW>
+<ROW id="6">
+ <COUNTRY_ID>SG</COUNTRY_ID>
+ <COUNTRY_NAME>Singapore</COUNTRY_NAME>
+ <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
+</ROW>
+</ROWS>');
+-- XMLTABLE with columns
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+CREATE VIEW xmltableview1 AS SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME/text()' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+SELECT * FROM xmltableview1;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+\sv xmltableview1
+CREATE OR REPLACE VIEW public.xmltableview1 AS
+ SELECT "xmltable".id,
+ "xmltable"._id,
+ "xmltable".country_name,
+ "xmltable".country_id,
+ "xmltable".region_id,
+ "xmltable".size,
+ "xmltable".unit,
+ "xmltable".premier_name
+ FROM ( SELECT xmldata.data
+ FROM xmldata) x,
+ LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
+ QUERY PLAN
+-----------------------------------------
+ Nested Loop
+ -> Seq Scan on xmldata
+ -> Table Function Scan on "xmltable"
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> Table Function Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- errors
+SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp) AS f (v1, v2);
+ERROR: XMLTABLE function has 1 columns available but 2 columns specified
+SELECT * FROM XMLTABLE (ROW () PASSING null COLUMNS v1 timestamp __pg__is_not_null 1) AS f (v1);
+ERROR: option name "__pg__is_not_null" cannot be used in XMLTABLE
+LINE 1: ...MLTABLE (ROW () PASSING null COLUMNS v1 timestamp __pg__is_n...
+ ^
+-- XMLNAMESPACES tests
+SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
+ '/zz:rows/zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'zz:a');
+ a
+----
+ 10
+(1 row)
+
+CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS "Zz"),
+ '/Zz:rows/Zz:row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'Zz:a');
+SELECT * FROM xmltableview2;
+ a
+----
+ 10
+(1 row)
+
+\sv xmltableview2
+CREATE OR REPLACE VIEW public.xmltableview2 AS
+ SELECT a
+ FROM XMLTABLE(XMLNAMESPACES ('http://x.y'::text AS "Zz"), ('/Zz:rows/Zz:row'::text) PASSING ('<rows xmlns="http://x.y"><row><a>10</a></row></rows>'::xml) COLUMNS a integer PATH ('Zz:a'::text))
+SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
+ '/rows/row'
+ PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
+ COLUMNS a int PATH 'a');
+ERROR: DEFAULT namespace is not supported
+SELECT * FROM XMLTABLE('.'
+ PASSING '<foo/>'
+ COLUMNS a text PATH 'foo/namespace::node()');
+ a
+--------------------------------------
+ http://www.w3.org/XML/1998/namespace
+(1 row)
+
+-- used in prepare statements
+PREPARE pp AS
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+EXECUTE pp;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+--------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+(6 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+ India | 3
+ Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+ 1 | India | 3
+ 2 | Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int);
+ id | COUNTRY_NAME | REGION_ID
+----+--------------+-----------
+ 4 | India | 3
+ 5 | Japan | 3
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
+ id
+----
+ 4
+ 5
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
+ id
+----
+ 1
+ 2
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+------------------------------------------------------------------
+ 4 | India | 3 | <ROW id="4"> +
+ | | | <COUNTRY_ID>IN</COUNTRY_ID> +
+ | | | <COUNTRY_NAME>India</COUNTRY_NAME> +
+ | | | <REGION_ID>3</REGION_ID> +
+ | | | </ROW>
+ 5 | Japan | 3 | <ROW id="5"> +
+ | | | <COUNTRY_ID>JP</COUNTRY_ID> +
+ | | | <COUNTRY_NAME>Japan</COUNTRY_NAME> +
+ | | | <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>+
+ | | | </ROW>
+(2 rows)
+
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*');
+ id | COUNTRY_NAME | REGION_ID | rawdata
+----+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------
+ 4 | India | 3 | <COUNTRY_ID>IN</COUNTRY_ID><COUNTRY_NAME>India</COUNTRY_NAME><REGION_ID>3</REGION_ID>
+ 5 | Japan | 3 | <COUNTRY_ID>JP</COUNTRY_ID><COUNTRY_NAME>Japan</COUNTRY_NAME><REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
+(2 rows)
+
+SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
+ element
+----------------------
+ a1aa2a bbbbxxxcccc
+(1 row)
+
+SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text PATH 'element/text()'); -- should fail
+ERROR: more than one value returned by column XPath expression
+-- CDATA test
+select * from xmltable('d/r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
+ c
+-------------------------
+ <hello> &"<>!<a>foo</a>
+ 2
+(2 rows)
+
+-- XML builtin entities
+SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent text);
+ ent
+-----
+ '
+ "
+ &
+ <
+ >
+(5 rows)
+
+SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent xml);
+ ent
+------------------
+ <ent>'</ent>
+ <ent>"</ent>
+ <ent>&</ent>
+ <ent><</ent>
+ <ent>></ent>
+(5 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> Table Function Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+(7 rows)
+
+-- test qual
+SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
+ COUNTRY_NAME | REGION_ID
+--------------+-----------
+ Japan | 3
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: f."COUNTRY_NAME", f."REGION_ID"
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> Table Function Scan on "xmltable" f
+ Output: f."COUNTRY_NAME", f."REGION_ID"
+ Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
+ Filter: (f."COUNTRY_NAME" = 'Japan'::text)
+(8 rows)
+
+EXPLAIN (VERBOSE, FORMAT JSON, COSTS OFF)
+SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) AS f WHERE "COUNTRY_NAME" = 'Japan';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Nested Loop", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Inner Unique": false, +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "xmldata", +
+ "Schema": "public", +
+ "Alias": "xmldata", +
+ "Disabled": false, +
+ "Output": ["xmldata.data"] +
+ }, +
+ { +
+ "Node Type": "Table Function Scan", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Table Function Name": "xmltable", +
+ "Alias": "f", +
+ "Disabled": false, +
+ "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
+ "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
+ "Filter": "(f.\"COUNTRY_NAME\" = 'Japan'::text)" +
+ } +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+-- should to work with more data
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="10">
+ <COUNTRY_ID>CZ</COUNTRY_ID>
+ <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
+</ROW>
+<ROW id="11">
+ <COUNTRY_ID>DE</COUNTRY_ID>
+ <COUNTRY_NAME>Germany</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+<ROW id="12">
+ <COUNTRY_ID>FR</COUNTRY_ID>
+ <COUNTRY_NAME>France</COUNTRY_NAME>
+ <REGION_ID>2</REGION_ID>
+</ROW>
+</ROWS>');
+INSERT INTO xmldata VALUES('<ROWS>
+<ROW id="20">
+ <COUNTRY_ID>EG</COUNTRY_ID>
+ <COUNTRY_NAME>Egypt</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+<ROW id="21">
+ <COUNTRY_ID>SD</COUNTRY_ID>
+ <COUNTRY_NAME>Sudan</COUNTRY_NAME>
+ <REGION_ID>1</REGION_ID>
+</ROW>
+</ROWS>');
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+----------------+------------+-----------+------+------+---------------
+ 1 | 1 | Australia | AU | 3 | | | not specified
+ 2 | 2 | China | CN | 3 | | | not specified
+ 3 | 3 | HongKong | HK | 3 | | | not specified
+ 4 | 4 | India | IN | 3 | | | not specified
+ 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
+ 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
+ 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
+ 11 | 2 | Germany | DE | 2 | | | not specified
+ 12 | 3 | France | FR | 2 | | | not specified
+ 20 | 1 | Egypt | EG | 1 | | | not specified
+ 21 | 2 | Sudan | SD | 1 | | | not specified
+(11 rows)
+
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ id | _id | country_name | country_id | region_id | size | unit | premier_name
+----+-----+----------------+------------+-----------+------+------+---------------
+ 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
+ 11 | 2 | Germany | DE | 2 | | | not specified
+ 12 | 3 | France | FR | 2 | | | not specified
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE',
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
+ WHERE region_id = 2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ -> Seq Scan on public.xmldata
+ Output: xmldata.data
+ -> Table Function Scan on "xmltable"
+ Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
+ Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
+ Filter: ("xmltable".region_id = 2)
+(8 rows)
+
+-- should fail, NULL value
+SELECT xmltable.*
+ FROM (SELECT data FROM xmldata) x,
+ LATERAL XMLTABLE('/ROWS/ROW'
+ PASSING data
+ COLUMNS id int PATH '@id',
+ _id FOR ORDINALITY,
+ country_name text PATH 'COUNTRY_NAME' NOT NULL,
+ country_id text PATH 'COUNTRY_ID',
+ region_id int PATH 'REGION_ID',
+ size float PATH 'SIZE' NOT NULL,
+ unit text PATH 'SIZE/@unit',
+ premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
+ERROR: null is not allowed in column "size"
+-- if all is ok, then result is empty
+-- one line xml test
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
+ FROM pg_proc WHERE proname = 'f_leak'),
+ y AS (SELECT xmlelement(name proc,
+ xmlforest(proname, proowner,
+ procost, pronargs,
+ proargnames, proargtypes)) as proc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/proc' PASSING proc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ proname | proowner | procost | pronargs | proargnames | proargtypes
+---------+----------+---------+----------+-------------+-------------
+(0 rows)
+
+-- multi line xml test, result should be empty too
+WITH
+ x AS (SELECT proname, proowner, procost::numeric, pronargs,
+ array_to_string(proargnames,',') as proargnames,
+ case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
+ FROM pg_proc),
+ y AS (SELECT xmlelement(name data,
+ xmlagg(xmlelement(name proc,
+ xmlforest(proname, proowner, procost,
+ pronargs, proargnames, proargtypes)))) as doc
+ FROM x),
+ z AS (SELECT xmltable.*
+ FROM y,
+ LATERAL xmltable('/data/proc' PASSING doc
+ COLUMNS proname name,
+ proowner oid,
+ procost float,
+ pronargs int,
+ proargnames text,
+ proargtypes text))
+ SELECT * FROM z
+ EXCEPT SELECT * FROM x;
+ proname | proowner | procost | pronargs | proargnames | proargtypes
+---------+----------+---------+----------+-------------+-------------
+(0 rows)
+
+CREATE TABLE xmltest2(x xml, _path text);
+INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
+INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
+INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
+INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D');
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c');
+ a
+---
+ 1
+ 2
+ 3
+ 2
+(4 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
+ a
+---
+ 1
+ 2
+ 3
+ 2
+(4 rows)
+
+SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);
+ a
+----
+ 11
+ 12
+ 13
+ 14
+(4 rows)
+
+-- XPath result can be boolean or number too
+SELECT * FROM XMLTABLE('*' PASSING '<a>a</a>' COLUMNS a xml PATH '.', b text PATH '.', c text PATH '"hi"', d boolean PATH '. = "a"', e integer PATH 'string-length(.)');
+ a | b | c | d | e
+----------+---+----+---+---
+ <a>a</a> | a | hi | t | 1
+(1 row)
+
+\x
+SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>' COLUMNS x xml PATH '/e/n2', y xml PATH '/');
+-[ RECORD 1 ]-----------------------------------------------------------
+x | <n2>&deep</n2>
+y | <e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n2>&deep</n2>post</e>+
+ |
+
+\x
+SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '"<foo/>"', b xml PATH '"<foo/>"');
+ a | b
+--------+--------------
+ <foo/> | <foo/>
+(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 & <"bar">
+(1 row)
+
+SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+ xmltext
+---------------------------------
+ x<P>73</P>0.42truej
+(1 row)
+
+CREATE XMLSCHEMA person_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="person">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="age" type="xs:integer"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+CREATE XMLSCHEMA IF NOT EXISTS person_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="other" type="xs:string"/>
+</xs:schema>';
+NOTICE: XML schema "person_schema" already exists, skipping
+CREATE XMLSCHEMA person_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="duplicate" type="xs:string"/>
+</xs:schema>';
+ERROR: XML schema "person_schema" already exists
+CREATE SCHEMA test_xmlschema_ns;
+CREATE XMLSCHEMA test_xmlschema_ns.product_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="product">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="price" type="xs:decimal"/>
+ </xs:sequence>
+ <xs:attribute name="id" type="xs:string" use="required"/>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+CREATE XMLSCHEMA bad_schema AS '<this-is-not-valid-xsd>';
+ERROR: invalid XML schema definition
+DETAIL: line 1: Premature end of data in tag this-is-not-valid-xsd line 1
+<this-is-not-valid-xsd>
+ ^
+CREATE XMLSCHEMA bad_xml_schema AS 'not even xml';
+ERROR: invalid XML schema definition
+DETAIL: line 1: Start tag expected, '<' not found
+not even xml
+^
+CREATE XMLSCHEMA book_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="book">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="title" type="xs:string"/>
+ <xs:element name="author" maxOccurs="unbounded">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="firstname" type="xs:string"/>
+ <xs:element name="lastname" type="xs:string"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+ <xs:element name="year" type="xs:integer"/>
+ </xs:sequence>
+ <xs:attribute name="isbn" type="xs:string" use="required"/>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name><age>30</age></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+ xmlvalidate
+-------------------------------------------------
+ <person><name>John</name><age>30</age></person>
+(1 row)
+
+SELECT XMLVALIDATE(DOCUMENT '<product id="P123"><name>Widget</name><price>9.99</price></product>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema);
+ xmlvalidate
+---------------------------------------------------------------------
+ <product id="P123"><name>Widget</name><price>9.99</price></product>
+(1 row)
+
+SELECT XMLVALIDATE(DOCUMENT
+ '<book isbn="978-0-123456-78-9">
+ <title>PostgreSQL Internals</title>
+ <author><firstname>John</firstname><lastname>Titor</lastname></author>
+ <author><firstname>Jane</firstname></author>
+ <year>2024</year>
+ </book>'
+ ACCORDING TO XMLSCHEMA book_schema);
+ERROR: XML validation failed
+SELECT XMLSERIALIZE(DOCUMENT
+ XMLVALIDATE(DOCUMENT '<person><name>Alice</name><age>25</age></person>'
+ ACCORDING TO XMLSCHEMA person_schema) AS text);
+ xmlserialize
+--------------------------------------------------
+ <person><name>Alice</name><age>25</age></person>
+(1 row)
+
+SELECT XMLVALIDATE(DOCUMENT NULL ACCORDING TO XMLSCHEMA person_schema);
+ xmlvalidate
+-------------
+
+(1 row)
+
+SELECT XMLVALIDATE(DOCUMENT NULL ACCORDING TO XMLSCHEMA person_schema) IS NULL AS is_null;
+ is_null
+---------
+ t
+(1 row)
+
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+ERROR: XML validation failed
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name><age>not-a-number</age></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+ERROR: XML validation failed
+SELECT XMLVALIDATE(DOCUMENT '<product><name>Widget</name><price>9.99</price></product>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema);
+ERROR: XML validation failed
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name><age>30</age><extra>data</extra></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+ERROR: XML validation failed
+SELECT XMLVALIDATE(DOCUMENT '<notperson><name>John</name><age>30</age></notperson>'
+ ACCORDING TO XMLSCHEMA person_schema);
+ERROR: XML validation failed
+SELECT XMLVALIDATE(DOCUMENT '<test>value</test>'
+ ACCORDING TO XMLSCHEMA nonexistent_schema);
+ERROR: XML schema "nonexistent_schema" does not exist
+CREATE VIEW validated_people AS
+ SELECT XMLVALIDATE(DOCUMENT data ACCORDING TO XMLSCHEMA person_schema) AS validated_xml
+ FROM xmltest WHERE id = 1;
+SELECT pg_get_viewdef('validated_people'::regclass, true);
+ pg_get_viewdef
+------------------------------------------------------------------------------------------
+ SELECT XMLVALIDATE(DOCUMENT data ACCORDING TO XMLSCHEMA person_schema) AS validated_xml+
+ FROM xmltest +
+ WHERE id = 1;
+(1 row)
+
+DROP VIEW validated_people;
+CREATE VIEW validated_products AS
+ SELECT XMLVALIDATE(DOCUMENT '<product id="123"><name>Test</name><price>1.99</price></product>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema) AS validated_xml;
+SELECT pg_get_viewdef('validated_products'::regclass, true);
+ pg_get_viewdef
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ SELECT XMLVALIDATE(DOCUMENT '<product id="123"><name>Test</name><price>1.99</price></product>'::xml ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema) AS validated_xml;
+(1 row)
+
+DROP VIEW validated_products;
+ALTER XMLSCHEMA book_schema RENAME TO library_book_schema;
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA book_schema);
+ERROR: XML schema "book_schema" does not exist
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA library_book_schema);
+ xmlvalidate
+-----------------------------------------------------------------------------------------------------------------------------
+ <book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>
+(1 row)
+
+ALTER XMLSCHEMA library_book_schema SET SCHEMA test_xmlschema_ns;
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA library_book_schema);
+ERROR: XML schema "library_book_schema" does not exist
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.library_book_schema);
+ xmlvalidate
+-----------------------------------------------------------------------------------------------------------------------------
+ <book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>
+(1 row)
+
+CREATE ROLE xmlschema_test_role;
+ALTER XMLSCHEMA test_xmlschema_ns.library_book_schema OWNER TO xmlschema_test_role;
+SELECT schemaname, schemanamespace::regnamespace, schemaowner::regrole
+FROM pg_xmlschema
+WHERE schemaname = 'library_book_schema';
+ schemaname | schemanamespace | schemaowner
+---------------------+-------------------+---------------------
+ library_book_schema | test_xmlschema_ns | xmlschema_test_role
+(1 row)
+
+CREATE VIEW book_view AS
+ SELECT XMLVALIDATE(DOCUMENT '<book isbn="456"><title>Dep Test</title><author><firstname>X</firstname><lastname>Y</lastname></author><year>2025</year></book>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.library_book_schema) AS validated_book;
+DROP XMLSCHEMA test_xmlschema_ns.library_book_schema;
+ERROR: cannot drop XML schema test_xmlschema_ns.library_book_schema because other objects depend on it
+DETAIL: view book_view depends on XML schema test_xmlschema_ns.library_book_schema
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP XMLSCHEMA test_xmlschema_ns.library_book_schema CASCADE;
+NOTICE: drop cascades to view book_view
+SELECT * FROM book_view;
+ERROR: relation "book_view" does not exist
+LINE 1: SELECT * FROM book_view;
+ ^
+DROP XMLSCHEMA person_schema;
+DROP XMLSCHEMA IF EXISTS person_schema;
+NOTICE: XML schema "person_schema" does not exist, skipping
+DROP XMLSCHEMA person_schema;
+ERROR: XML schema "person_schema" does not exist
+DROP XMLSCHEMA test_xmlschema_ns.product_schema;
+SET ROLE xmlschema_test_role;
+CREATE XMLSCHEMA public.should_fail_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="test" type="xs:string"/>
+</xs:schema>';
+RESET ROLE;
+GRANT CREATE ON SCHEMA test_xmlschema_ns TO xmlschema_test_role;
+SET ROLE xmlschema_test_role;
+CREATE XMLSCHEMA test_xmlschema_ns.role_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="test" type="xs:string"/>
+</xs:schema>';
+RESET ROLE;
+DROP XMLSCHEMA test_xmlschema_ns.role_schema;
+DROP ROLE xmlschema_test_role;
+ERROR: role "xmlschema_test_role" cannot be dropped because some objects depend on it
+DETAIL: privileges for schema test_xmlschema_ns
+owner of XML schema public.should_fail_schema
+DROP SCHEMA test_xmlschema_ns CASCADE;
+CREATE ROLE xmlschema_user1;
+CREATE ROLE xmlschema_user2;
+CREATE XMLSCHEMA permission_test_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="test" type="xs:string"/>
+</xs:schema>';
+SET ROLE xmlschema_user1;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+ERROR: permission denied for XML schema permission_test_schema
+RESET ROLE;
+GRANT USAGE ON XMLSCHEMA permission_test_schema TO xmlschema_user1;
+SET ROLE xmlschema_user1;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+ xmlvalidate
+-------------------
+ <test>data</test>
+(1 row)
+
+RESET ROLE;
+SET ROLE xmlschema_user2;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+ERROR: permission denied for XML schema permission_test_schema
+RESET ROLE;
+GRANT USAGE ON XMLSCHEMA permission_test_schema TO xmlschema_user2;
+SET ROLE xmlschema_user2;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+ xmlvalidate
+-------------------
+ <test>data</test>
+(1 row)
+
+RESET ROLE;
+REVOKE USAGE ON XMLSCHEMA permission_test_schema FROM xmlschema_user1;
+SET ROLE xmlschema_user1;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+ERROR: permission denied for XML schema permission_test_schema
+RESET ROLE;
+DROP XMLSCHEMA permission_test_schema;
+DROP ROLE xmlschema_user1;
+DROP ROLE xmlschema_user2;
+CREATE TABLE validated_xml_data (
+ id serial PRIMARY KEY,
+ data xml
+);
+CREATE XMLSCHEMA data_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="data">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="value" type="xs:integer"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+INSERT INTO validated_xml_data (data)
+VALUES (XMLVALIDATE(DOCUMENT '<data><value>42</value></data>'
+ ACCORDING TO XMLSCHEMA data_schema));
+INSERT INTO validated_xml_data (data)
+VALUES (XMLVALIDATE(DOCUMENT '<data><value>100</value></data>'
+ ACCORDING TO XMLSCHEMA data_schema));
+SELECT id, data FROM validated_xml_data ORDER BY id;
+ id | data
+----+---------------------------------
+ 1 | <data><value>42</value></data>
+ 2 | <data><value>100</value></data>
+(2 rows)
+
+INSERT INTO validated_xml_data (data)
+VALUES (XMLVALIDATE(DOCUMENT '<data><value>not-an-int</value></data>'
+ ACCORDING TO XMLSCHEMA data_schema));
+ERROR: XML validation failed
+DROP TABLE validated_xml_data;
+DROP XMLSCHEMA data_schema;
+DROP XMLSCHEMA should_fail_schema;
+DROP ROLE xmlschema_test_role;
diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql
index 0ea4f508837..5051ff47b17 100644
--- a/src/test/regress/sql/xml.sql
+++ b/src/test/regress/sql/xml.sql
@@ -679,3 +679,277 @@ SELECT xmltext(' ');
SELECT xmltext('foo `$_-+?=*^%!|/\()[]{}');
SELECT xmltext('foo & <"bar">');
SELECT xmltext('x'|| '<P>73</P>'::xml || .42 || true || 'j'::char);
+
+CREATE XMLSCHEMA person_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="person">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="age" type="xs:integer"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+
+
+CREATE XMLSCHEMA IF NOT EXISTS person_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="other" type="xs:string"/>
+</xs:schema>';
+
+CREATE XMLSCHEMA person_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="duplicate" type="xs:string"/>
+</xs:schema>';
+
+CREATE SCHEMA test_xmlschema_ns;
+
+CREATE XMLSCHEMA test_xmlschema_ns.product_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="product">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="name" type="xs:string"/>
+ <xs:element name="price" type="xs:decimal"/>
+ </xs:sequence>
+ <xs:attribute name="id" type="xs:string" use="required"/>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+
+CREATE XMLSCHEMA bad_schema AS '<this-is-not-valid-xsd>';
+
+CREATE XMLSCHEMA bad_xml_schema AS 'not even xml';
+
+CREATE XMLSCHEMA book_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="book">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="title" type="xs:string"/>
+ <xs:element name="author" maxOccurs="unbounded">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="firstname" type="xs:string"/>
+ <xs:element name="lastname" type="xs:string"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+ <xs:element name="year" type="xs:integer"/>
+ </xs:sequence>
+ <xs:attribute name="isbn" type="xs:string" use="required"/>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name><age>30</age></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<product id="P123"><name>Widget</name><price>9.99</price></product>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema);
+
+SELECT XMLVALIDATE(DOCUMENT
+ '<book isbn="978-0-123456-78-9">
+ <title>PostgreSQL Internals</title>
+ <author><firstname>John</firstname><lastname>Titor</lastname></author>
+ <author><firstname>Jane</firstname></author>
+ <year>2024</year>
+ </book>'
+ ACCORDING TO XMLSCHEMA book_schema);
+
+SELECT XMLSERIALIZE(DOCUMENT
+ XMLVALIDATE(DOCUMENT '<person><name>Alice</name><age>25</age></person>'
+ ACCORDING TO XMLSCHEMA person_schema) AS text);
+
+SELECT XMLVALIDATE(DOCUMENT NULL ACCORDING TO XMLSCHEMA person_schema);
+
+SELECT XMLVALIDATE(DOCUMENT NULL ACCORDING TO XMLSCHEMA person_schema) IS NULL AS is_null;
+
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name><age>not-a-number</age></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<product><name>Widget</name><price>9.99</price></product>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<person><name>John</name><age>30</age><extra>data</extra></person>'
+ ACCORDING TO XMLSCHEMA person_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<notperson><name>John</name><age>30</age></notperson>'
+ ACCORDING TO XMLSCHEMA person_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<test>value</test>'
+ ACCORDING TO XMLSCHEMA nonexistent_schema);
+
+CREATE VIEW validated_people AS
+ SELECT XMLVALIDATE(DOCUMENT data ACCORDING TO XMLSCHEMA person_schema) AS validated_xml
+ FROM xmltest WHERE id = 1;
+
+SELECT pg_get_viewdef('validated_people'::regclass, true);
+
+DROP VIEW validated_people;
+
+CREATE VIEW validated_products AS
+ SELECT XMLVALIDATE(DOCUMENT '<product id="123"><name>Test</name><price>1.99</price></product>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.product_schema) AS validated_xml;
+
+SELECT pg_get_viewdef('validated_products'::regclass, true);
+
+DROP VIEW validated_products;
+
+ALTER XMLSCHEMA book_schema RENAME TO library_book_schema;
+
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA book_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA library_book_schema);
+
+ALTER XMLSCHEMA library_book_schema SET SCHEMA test_xmlschema_ns;
+
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA library_book_schema);
+
+SELECT XMLVALIDATE(DOCUMENT '<book isbn="123"><title>Test</title><author><firstname>A</firstname><lastname>B</lastname></author><year>2024</year></book>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.library_book_schema);
+
+CREATE ROLE xmlschema_test_role;
+
+ALTER XMLSCHEMA test_xmlschema_ns.library_book_schema OWNER TO xmlschema_test_role;
+
+SELECT schemaname, schemanamespace::regnamespace, schemaowner::regrole
+FROM pg_xmlschema
+WHERE schemaname = 'library_book_schema';
+
+CREATE VIEW book_view AS
+ SELECT XMLVALIDATE(DOCUMENT '<book isbn="456"><title>Dep Test</title><author><firstname>X</firstname><lastname>Y</lastname></author><year>2025</year></book>'
+ ACCORDING TO XMLSCHEMA test_xmlschema_ns.library_book_schema) AS validated_book;
+
+DROP XMLSCHEMA test_xmlschema_ns.library_book_schema;
+
+DROP XMLSCHEMA test_xmlschema_ns.library_book_schema CASCADE;
+
+SELECT * FROM book_view;
+
+DROP XMLSCHEMA person_schema;
+
+DROP XMLSCHEMA IF EXISTS person_schema;
+
+DROP XMLSCHEMA person_schema;
+
+DROP XMLSCHEMA test_xmlschema_ns.product_schema;
+
+SET ROLE xmlschema_test_role;
+
+CREATE XMLSCHEMA public.should_fail_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="test" type="xs:string"/>
+</xs:schema>';
+
+RESET ROLE;
+
+GRANT CREATE ON SCHEMA test_xmlschema_ns TO xmlschema_test_role;
+
+SET ROLE xmlschema_test_role;
+
+CREATE XMLSCHEMA test_xmlschema_ns.role_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="test" type="xs:string"/>
+</xs:schema>';
+
+RESET ROLE;
+
+DROP XMLSCHEMA test_xmlschema_ns.role_schema;
+
+DROP ROLE xmlschema_test_role;
+
+DROP SCHEMA test_xmlschema_ns CASCADE;
+
+CREATE ROLE xmlschema_user1;
+CREATE ROLE xmlschema_user2;
+
+CREATE XMLSCHEMA permission_test_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="test" type="xs:string"/>
+</xs:schema>';
+
+SET ROLE xmlschema_user1;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+
+RESET ROLE;
+
+GRANT USAGE ON XMLSCHEMA permission_test_schema TO xmlschema_user1;
+
+SET ROLE xmlschema_user1;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+
+RESET ROLE;
+
+SET ROLE xmlschema_user2;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+
+RESET ROLE;
+
+GRANT USAGE ON XMLSCHEMA permission_test_schema TO xmlschema_user2;
+
+SET ROLE xmlschema_user2;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+
+RESET ROLE;
+
+REVOKE USAGE ON XMLSCHEMA permission_test_schema FROM xmlschema_user1;
+
+SET ROLE xmlschema_user1;
+SELECT XMLVALIDATE(DOCUMENT '<test>data</test>'
+ ACCORDING TO XMLSCHEMA permission_test_schema);
+
+RESET ROLE;
+
+DROP XMLSCHEMA permission_test_schema;
+DROP ROLE xmlschema_user1;
+DROP ROLE xmlschema_user2;
+
+CREATE TABLE validated_xml_data (
+ id serial PRIMARY KEY,
+ data xml
+);
+
+CREATE XMLSCHEMA data_schema AS '<?xml version="1.0"?>
+<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
+ <xs:element name="data">
+ <xs:complexType>
+ <xs:sequence>
+ <xs:element name="value" type="xs:integer"/>
+ </xs:sequence>
+ </xs:complexType>
+ </xs:element>
+</xs:schema>';
+
+INSERT INTO validated_xml_data (data)
+VALUES (XMLVALIDATE(DOCUMENT '<data><value>42</value></data>'
+ ACCORDING TO XMLSCHEMA data_schema));
+
+INSERT INTO validated_xml_data (data)
+VALUES (XMLVALIDATE(DOCUMENT '<data><value>100</value></data>'
+ ACCORDING TO XMLSCHEMA data_schema));
+
+SELECT id, data FROM validated_xml_data ORDER BY id;
+
+INSERT INTO validated_xml_data (data)
+VALUES (XMLVALIDATE(DOCUMENT '<data><value>not-an-int</value></data>'
+ ACCORDING TO XMLSCHEMA data_schema));
+
+DROP TABLE validated_xml_data;
+
+DROP XMLSCHEMA data_schema;
+
+DROP XMLSCHEMA should_fail_schema;
+
+DROP ROLE xmlschema_test_role;
--
2.52.0.windows.1
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: WIP - xmlvalidate implementation from TODO list
In-Reply-To: <CAN3aFCe_cBshj0rb7J8yoT+fRHOBOZmk-m8V7DMLDe0ZjSgjcA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox