diff options
Diffstat (limited to 'doc/src/sgml/features.sgml')
| -rw-r--r-- | doc/src/sgml/features.sgml | 494 |
1 files changed, 494 insertions, 0 deletions
diff --git a/doc/src/sgml/features.sgml b/doc/src/sgml/features.sgml new file mode 100644 index 0000000..05365d8 --- /dev/null +++ b/doc/src/sgml/features.sgml @@ -0,0 +1,494 @@ +<!-- 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> |