| <!-- doc/src/sgml/features.sgml --> |
| |
| <appendix id="features"> |
| <title>SQL Conformance</title> |
| |
| <para> |
| This section attempts to outline to what extent |
| <productname>PostgreSQL</productname> conforms to the current SQL |
| standard. The following information is not a full statement of |
| conformance, but it presents the main topics in as much detail as is |
| both reasonable and useful for users. |
| </para> |
| |
| <para> |
| The formal name of the SQL standard is ISO/IEC 9075 <quote>Database |
| Language SQL</quote>. A revised version of the standard is released |
| from time to time; the most recent update appearing in 2016. |
| The 2016 version is referred to as ISO/IEC 9075:2016, or simply as SQL:2016. |
| The versions prior to that were SQL:2011, SQL:2008, SQL:2006, SQL:2003, |
| SQL:1999, and SQL-92. Each version |
| replaces the previous one, so claims of conformance to earlier |
| versions have no official merit. |
| <productname>PostgreSQL</productname> development aims for |
| conformance with the latest official version of the standard where |
| such conformance does not contradict traditional features or common |
| sense. Many of the features required by the SQL |
| standard are supported, though sometimes with slightly differing |
| syntax or function. Further moves towards conformance can be |
| expected over time. |
| </para> |
| |
| <para> |
| <acronym>SQL-92</acronym> defined three feature sets for |
| conformance: Entry, Intermediate, and Full. Most database |
| management systems claiming <acronym>SQL</acronym> standard |
| conformance were conforming at only the Entry level, since the |
| entire set of features in the Intermediate and Full levels was |
| either too voluminous or in conflict with legacy behaviors. |
| </para> |
| |
| <para> |
| Starting with <acronym>SQL:1999</acronym>, the SQL standard defines |
| a large set of individual features rather than the ineffectively |
| broad three levels found in <acronym>SQL-92</acronym>. A large |
| subset of these features represents the <quote>Core</quote> |
| features, which every conforming SQL implementation must supply. |
| The rest of the features are purely optional. |
| </para> |
| |
| <para> |
| The standard versions beginning with <acronym>SQL:2003</acronym> |
| are also split into a number |
| of parts. Each is known by a shorthand name. Note that these parts |
| are not consecutively numbered. |
| |
| <itemizedlist> |
| <listitem><para>ISO/IEC 9075-1 Framework (SQL/Framework)</para><indexterm><primary>SQL/Framework</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-2 Foundation (SQL/Foundation)</para><indexterm><primary>SQL/Foundation</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-3 Call Level Interface (SQL/CLI)</para><indexterm><primary>SQL/CLI</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)</para><indexterm><primary>SQL/PSM</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-9 Management of External Data (SQL/MED)</para><indexterm><primary>SQL/MED</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)</para><indexterm><primary>SQL/OLB</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)</para><indexterm><primary>SQL/Schemata</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)</para><indexterm><primary>SQL/JRT</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-14 XML-related specifications (SQL/XML)</para><indexterm><primary>SQL/XML</primary></indexterm></listitem> |
| <listitem><para>ISO/IEC 9075-15 Multi-dimensional arrays (SQL/MDA)</para><indexterm><primary>SQL/MDA</primary></indexterm></listitem> |
| </itemizedlist> |
| </para> |
| |
| <para> |
| The <productname>PostgreSQL</productname> core covers parts 1, 2, 9, |
| 11, and 14. Part 3 is covered by the ODBC driver, and part 13 is |
| covered by the PL/Java plug-in, but exact conformance is currently |
| not being verified for these components. There are currently no |
| implementations of parts 4, 10, and 15 |
| for <productname>PostgreSQL</productname>. |
| </para> |
| |
| <para> |
| PostgreSQL supports most of the major features of SQL:2016. Out of |
| 177 mandatory features required for full Core conformance, |
| PostgreSQL conforms to at least 170. In addition, there is a long |
| list of supported optional features. It might be worth noting that at |
| the time of writing, no current version of any database management |
| system claims full conformance to Core SQL:2016. |
| </para> |
| |
| <para> |
| In the following two sections, we provide a list of those features |
| that <productname>PostgreSQL</productname> supports, followed by a |
| list of the features defined in <acronym>SQL:2016</acronym> which |
| are not yet supported in <productname>PostgreSQL</productname>. |
| Both of these lists are approximate: There might be minor details that |
| are nonconforming for a feature that is listed as supported, and |
| large parts of an unsupported feature might in fact be implemented. |
| The main body of the documentation always contains the most accurate |
| information about what does and does not work. |
| </para> |
| |
| <note> |
| <para> |
| Feature codes containing a hyphen are subfeatures. Therefore, if a |
| particular subfeature is not supported, the main feature is listed |
| as unsupported even if some other subfeatures are supported. |
| </para> |
| </note> |
| |
| <sect1 id="features-sql-standard"> |
| <title>Supported Features</title> |
| |
| <para> |
| <informaltable> |
| <tgroup cols="4"> |
| <colspec colname="col1" colwidth="1.5*"/> |
| <colspec colname="col2" colwidth="1*"/> |
| <colspec colname="col3" colwidth="7*"/> |
| <colspec colname="col4" colwidth="3*"/> |
| <thead> |
| <row> |
| <entry>Identifier</entry> |
| <entry>Core?</entry> |
| <entry>Description</entry> |
| <entry>Comment</entry> |
| </row> |
| </thead> |
| |
| &features-supported; |
| |
| </tgroup> |
| </informaltable> |
| </para> |
| </sect1> |
| |
| <sect1 id="unsupported-features-sql-standard"> |
| <title>Unsupported Features</title> |
| |
| <para> |
| The following features defined in <acronym>SQL:2016</acronym> are not |
| implemented in this release of |
| <productname>PostgreSQL</productname>. In a few cases, equivalent |
| functionality is available. |
| |
| <informaltable> |
| <tgroup cols="4"> |
| <colspec colname="col1" colwidth="1.5*"/> |
| <colspec colname="col2" colwidth="1*"/> |
| <colspec colname="col3" colwidth="7*"/> |
| <colspec colname="col4" colwidth="3*"/> |
| <thead> |
| <row> |
| <entry>Identifier</entry> |
| <entry>Core?</entry> |
| <entry>Description</entry> |
| <entry>Comment</entry> |
| </row> |
| </thead> |
| |
| &features-unsupported; |
| |
| </tgroup> |
| </informaltable> |
| </para> |
| </sect1> |
| |
| <sect1 id="xml-limits-conformance"> |
| <title>XML Limits and Conformance to SQL/XML</title> |
| |
| <indexterm> |
| <primary>SQL/XML</primary> |
| <secondary>limits and conformance</secondary> |
| </indexterm> |
| |
| <para> |
| Significant revisions to the XML-related specifications in ISO/IEC 9075-14 |
| (SQL/XML) were introduced with SQL:2006. |
| <productname>PostgreSQL</productname>'s implementation of the XML data |
| type and related functions largely follows the earlier 2003 edition, |
| with some borrowing from later editions. In particular: |
| <itemizedlist> |
| <listitem> |
| <para> |
| Where the current standard provides a family of XML data types |
| to hold <quote>document</quote> or <quote>content</quote> in |
| untyped or XML Schema-typed variants, and a type |
| <type>XML(SEQUENCE)</type> to hold arbitrary pieces of XML content, |
| <productname>PostgreSQL</productname> provides the single |
| <type>xml</type> type, which can hold <quote>document</quote> or |
| <quote>content</quote>. There is no equivalent of the |
| standard's <quote>sequence</quote> type. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| <productname>PostgreSQL</productname> provides two functions |
| introduced in SQL:2006, but in variants that use the XPath 1.0 |
| language, rather than XML Query as specified for them in the |
| standard. |
| </para> |
| </listitem> |
| </itemizedlist> |
| </para> |
| |
| <para> |
| This section presents some of the resulting differences you may encounter. |
| </para> |
| |
| <sect2 id="functions-xml-limits-xpath1"> |
| <title>Queries Are Restricted to XPath 1.0</title> |
| |
| <para> |
| The <productname>PostgreSQL</productname>-specific functions |
| <function>xpath()</function> and <function>xpath_exists()</function> |
| query XML documents using the XPath language. |
| <productname>PostgreSQL</productname> also provides XPath-only variants |
| of the standard functions <function>XMLEXISTS</function> and |
| <function>XMLTABLE</function>, which officially use |
| the XQuery language. For all of these functions, |
| <productname>PostgreSQL</productname> relies on the |
| <application>libxml2</application> library, which provides only XPath 1.0. |
| </para> |
| |
| <para> |
| There is a strong connection between the XQuery language and XPath |
| versions 2.0 and later: any expression that is syntactically valid and |
| executes successfully in both produces the same result (with a minor |
| exception for expressions containing numeric character references or |
| predefined entity references, which XQuery replaces with the |
| corresponding character while XPath leaves them alone). But there is |
| no such connection between these languages and XPath 1.0; it was an |
| earlier language and differs in many respects. |
| </para> |
| |
| <para> |
| There are two categories of limitation to keep in mind: the restriction |
| from XQuery to XPath for the functions specified in the SQL standard, and |
| the restriction of XPath to version 1.0 for both the standard and the |
| <productname>PostgreSQL</productname>-specific functions. |
| </para> |
| |
| <sect3> |
| <title>Restriction of XQuery to XPath</title> |
| |
| <para> |
| Features of XQuery beyond those of XPath include: |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| XQuery expressions can construct and return new XML nodes, in |
| addition to all possible XPath values. XPath can create and return |
| values of the atomic types (numbers, strings, and so on) but can |
| only return XML nodes that were already present in documents |
| supplied as input to the expression. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| XQuery has control constructs for iteration, sorting, and grouping. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| XQuery allows declaration and use of local functions. |
| </para> |
| </listitem> |
| </itemizedlist> |
| </para> |
| |
| <para> |
| Recent XPath versions begin to offer capabilities overlapping with |
| these (such as functional-style <function>for-each</function> and |
| <function>sort</function>, anonymous functions, and |
| <function>parse-xml</function> to create a node from a string), |
| but such features were not available before XPath 3.0. |
| </para> |
| </sect3> |
| |
| <sect3 id="xml-xpath-1-specifics"> |
| <title>Restriction of XPath to 1.0</title> |
| |
| <para> |
| For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 |
| presents a number of differences to contend with: |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| The fundamental type of an XQuery/XPath expression, the |
| <type>sequence</type>, which can contain XML nodes, atomic values, |
| or both, does not exist in XPath 1.0. A 1.0 expression can only |
| produce a node-set (containing zero or more XML nodes), or a single |
| atomic value. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| Unlike an XQuery/XPath sequence, which can contain any desired |
| items in any desired order, an XPath 1.0 node-set has no |
| guaranteed order and, like any set, does not allow multiple |
| appearances of the same item. |
| <note> |
| <para> |
| The <application>libxml2</application> library does seem to |
| always return node-sets to <productname>PostgreSQL</productname> |
| with their members in the same relative order they had in the |
| input document. Its documentation does not commit to this |
| behavior, and an XPath 1.0 expression cannot control it. |
| </para> |
| </note> |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| While XQuery/XPath provides all of the types defined in XML Schema |
| and many operators and functions over those types, XPath 1.0 has only |
| node-sets and the three atomic types <type>boolean</type>, |
| <type>double</type>, and <type>string</type>. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| XPath 1.0 has no conditional operator. An XQuery/XPath expression |
| such as <literal>if ( hat ) then hat/@size else "no hat"</literal> |
| has no XPath 1.0 equivalent. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| XPath 1.0 has no ordering comparison operator for strings. Both |
| <literal>"cat" < "dog"</literal> and |
| <literal>"cat" > "dog"</literal> are false, because each is a |
| numeric comparison of two <literal>NaN</literal>s. In contrast, |
| <literal>=</literal> and <literal>!=</literal> do compare the strings |
| as strings. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| XPath 1.0 blurs the distinction between |
| <firstterm>value comparisons</firstterm> and |
| <firstterm>general comparisons</firstterm> as XQuery/XPath define |
| them. Both <literal>sale/@hatsize = 7</literal> and |
| <literal>sale/@customer = "alice"</literal> are existentially |
| quantified comparisons, true if there is |
| any <literal>sale</literal> with the given value for the |
| attribute, but <literal>sale/@taxable = false()</literal> is a |
| value comparison to the |
| <firstterm>effective boolean value</firstterm> of a whole node-set. |
| It is true only if no <literal>sale</literal> has |
| a <literal>taxable</literal> attribute at all. |
| </para> |
| </listitem> |
| |
| <listitem> |
| <para> |
| In the XQuery/XPath data model, a <firstterm>document |
| node</firstterm> can have either document form (i.e., exactly one |
| top-level element, with only comments and processing instructions |
| outside of it) or content form (with those constraints |
| relaxed). Its equivalent in XPath 1.0, the |
| <firstterm>root node</firstterm>, can only be in document form. |
| This is part of the reason an <type>xml</type> value passed as the |
| context item to any <productname>PostgreSQL</productname> |
| XPath-based function must be in document form. |
| </para> |
| </listitem> |
| </itemizedlist> |
| </para> |
| |
| <para> |
| The differences highlighted here are not all of them. In XQuery and |
| the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility |
| mode, and the W3C lists of |
| <ulink url='https://www.w3.org/TR/2010/REC-xpath-functions-20101214/#xpath1-compatibility'>function library changes</ulink> |
| and |
| <ulink url='https://www.w3.org/TR/xpath20/#id-backwards-compatibility'>language changes</ulink> |
| applied in that mode offer a more complete (but still not exhaustive) |
| account of the differences. The compatibility mode cannot make the |
| later languages exactly equivalent to XPath 1.0. |
| </para> |
| </sect3> |
| |
| <sect3 id="functions-xml-limits-casts"> |
| <title>Mappings between SQL and XML Data Types and Values</title> |
| |
| <para> |
| In SQL:2006 and later, both directions of conversion between standard SQL |
| data types and the XML Schema types are specified precisely. However, the |
| rules are expressed using the types and semantics of XQuery/XPath, and |
| have no direct application to the different data model of XPath 1.0. |
| </para> |
| |
| <para> |
| When <productname>PostgreSQL</productname> maps SQL data values to XML |
| (as in <function>xmlelement</function>), or XML to SQL (as in the output |
| columns of <function>xmltable</function>), except for a few cases |
| treated specially, <productname>PostgreSQL</productname> simply assumes |
| that the XML data type's XPath 1.0 string form will be valid as the |
| text-input form of the SQL datatype, and conversely. This rule has the |
| virtue of simplicity while producing, for many data types, results similar |
| to the mappings specified in the standard. |
| </para> |
| |
| <para> |
| Where interoperability with other systems is a concern, for some data |
| types, it may be necessary to use data type formatting functions (such |
| as those in <xref linkend="functions-formatting"/>) explicitly to |
| produce the standard mappings. |
| </para> |
| </sect3> |
| </sect2> |
| |
| <sect2 id="functions-xml-limits-postgresql"> |
| <title>Incidental Limits of the Implementation</title> |
| |
| <para> |
| This section concerns limits that are not inherent in the |
| <application>libxml2</application> library, but apply to the current |
| implementation in <productname>PostgreSQL</productname>. |
| </para> |
| |
| <sect3> |
| <title>Only <literal>BY VALUE</literal> Passing Mechanism Is Supported</title> |
| |
| <para> |
| The SQL standard defines two <firstterm>passing mechanisms</firstterm> |
| that apply when passing an XML argument from SQL to an XML function or |
| receiving a result: <literal>BY REF</literal>, in which a particular XML |
| value retains its node identity, and <literal>BY VALUE</literal>, in which |
| the content of the XML is passed but node identity is not preserved. A |
| mechanism can be specified before a list of parameters, as the default |
| mechanism for all of them, or after any parameter, to override the |
| default. |
| </para> |
| |
| <para> |
| To illustrate the difference, if |
| <replaceable>x</replaceable> is an XML value, these two queries in |
| an SQL:2006 environment would produce true and false, respectively: |
| |
| <programlisting> |
| SELECT XMLQUERY('$a is $b' PASSING BY REF <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY); |
| SELECT XMLQUERY('$a is $b' PASSING BY VALUE <replaceable>x</replaceable> AS a, <replaceable>x</replaceable> AS b NULL ON EMPTY); |
| </programlisting> |
| </para> |
| |
| <para> |
| <productname>PostgreSQL</productname> will accept |
| <literal>BY VALUE</literal> or <literal>BY REF</literal> in an |
| <function>XMLEXISTS</function> or <function>XMLTABLE</function> |
| construct, but it ignores them. The <type>xml</type> data type holds |
| a character-string serialized representation, so there is no node |
| identity to preserve, and passing is always effectively <literal>BY |
| VALUE</literal>. |
| </para> |
| </sect3> |
| |
| <sect3> |
| <title>Cannot Pass Named Parameters to Queries</title> |
| |
| <para> |
| The XPath-based functions support passing one parameter to serve as the |
| XPath expression's context item, but do not support passing additional |
| values to be available to the expression as named parameters. |
| </para> |
| </sect3> |
| |
| <sect3> |
| <title>No <type>XML(SEQUENCE)</type> Type</title> |
| |
| <para> |
| The <productname>PostgreSQL</productname> <type>xml</type> data type |
| can only hold a value in <literal>DOCUMENT</literal> |
| or <literal>CONTENT</literal> form. An XQuery/XPath expression |
| context item must be a single XML node or atomic value, but XPath 1.0 |
| further restricts it to be only an XML node, and has no node type |
| allowing <literal>CONTENT</literal>. The upshot is that a |
| well-formed <literal>DOCUMENT</literal> is the only form of XML value |
| that <productname>PostgreSQL</productname> can supply as an XPath |
| context item. |
| </para> |
| </sect3> |
| </sect2> |
| </sect1> |
| |
| </appendix> |
