An XMLQUERY-like function

In the syntax of the SQL/XML standard, here is a query that would return an XML element representing the declaration of the function with the name numeric_avg (if PostgreSQL really had the standard XMLQUERY function built in):

SELECT XMLQUERY('/pg_catalog/pg_proc[proname eq $FUNCNAME]'
                PASSING BY VALUE x, 'numeric_avg' AS FUNCNAME
                RETURNING CONTENT EMPTY ON EMPTY)
FROM catalog_as_xml;

It binds the ‘context item’ of the query to x, and the FUNCNAME parameter to the given value, then evaluates the query and returns XML “CONTENT” (a tree structure with a document node at the root, but not necessarily meeting all the requirements of an XML “DOCUMENT”). It can be rewritten as this call to the xq_ret_content method provided here:

SELECT javatest.xq_ret_content('/pg_catalog/pg_proc[proname eq $FUNCNAME]',
                               PASSING => p, nullOnEmpty => false)
FROM catalog_as_xml,
LATERAL (SELECT x AS ".", 'numeric_avg' AS "FUNCNAME") AS p;

In the rewritten form, the type of value returned is determined by which function is called, and the parameters to pass to the query are moved out to a separate SELECT that supplies their values, types, and names (with the context item now given the name “.”) and is passed by its alias into the query function.

An alert reader may notice that the example above includes a named parameter, FUNCNAME, and it is spelled in uppercase in the XQuery expression that uses it, and is spelled in uppercase and quoted in the sub-SELECT that supplies it. The reason is an unconditional toUppercase() in PL/Java's internal JDBC driver, which is not anything the JDBC standard requires, but has been there in PL/Java since 2005. For now, therefore, no matter how a parameter name is spelled in the sub-SELECT, it must appear in uppercase in the XQuery expression using it, or it will not be recognized. A future PL/Java release is highly likely to stop forcibly uppercasing the names. At that time, any code relying on the uppercasing will break. Therefore, it is wisest, until then, to call this function with all parameter names spelled in uppercase both in the SQL and in the XQuery text, and on the SQL side that requires quoting the name to avoid the conventional lowercasing done by PostgreSQL.

In the standard, parameters and results (of XML types) can be passed BY VALUE or BY REF, where the latter means that the same nodes will retain their XQuery node identities over calls (note that this is a meaning unrelated to what “by value” and “by reference” usually mean in PostgreSQL's documentation). PostgreSQL's implementation of the XML type provides no way for BY REF semantics to be implemented, so everything happening here happens BY VALUE implicitly, and does not need to be specified.

An XMLEXISTS-like predicate

In the syntax of the SQL/XML standard, here is a query that would return a boolean result indicating whether an SQL function named numeric_avg is declared (if PostgreSQL really had the standard XMLEXISTS function built in):

SELECT XMLEXISTS('/pg_catalog/pg_proc[proname eq $FUNCNAME]'
                 PASSING BY VALUE x, 'numeric_avg' AS FUNCNAME)
FROM catalog_as_xml;

It can be rewritten as this call to the xmlexists method provided here:

SELECT "xmlexists"('/pg_catalog/pg_proc[proname eq $FUNCNAME]',
                   PASSING => p)
FROM catalog_as_xml,
LATERAL (SELECT x AS ".", 'numeric_avg' AS "FUNCNAME") AS p;

As for the XMLQUERY-like function above, , the context item and a parameter are supplied by a separate query producing the row p that is given as the PASSING argument to "xmlexists". The parameter name is capitalized for the reasons explained above for the XMLQUERY-like function.

An XMLTABLE-like function

The function xmltable here implements (much of) the standard function of the same name. Because it is the same name, it has to be either schema-qualified or double-quoted in a call to avoid confusion with the reserved word. A rewritten form of the first example in the PostgreSQL manual could be:

SELECT xmltable.*
FROM
  xmldata,

  LATERAL (SELECT data AS ".", 'not specified'::text AS "DPREMIER") AS p,

  "xmltable"('//ROWS/ROW', PASSING => p, COLUMNS => ARRAY[
   'data(@id)', null, 'COUNTRY_NAME',
   'COUNTRY_ID', 'SIZE[@unit eq "sq_km"]',
   'concat(SIZE[@unit ne "sq_km"], " ", SIZE[@unit ne "sq_km"]/@unit)',
   'let $e := PREMIER_NAME
    return if ( empty($e) ) then $DPREMIER else $e'
  ]) AS (
   id int, ordinality int, "COUNTRY_NAME" text, country_id text,
   size_sq_km float, size_other text, premier_name text
  );

Again, the context item and a parameter (here the desired default value for PREMIER, passed in as the parameter DPREMIER) are supplied by a separate query producing the row p that is given as "xmltable"'s PASSING argument. The result column names and types are now specified in the AS list following the function call, and the column XML Query expressions are supplied as the COLUMNS array. The array must have length equal to the result column AS list (there is no defaulting an omitted column expression to an element test using the column's name, as there is in the standard function). The array is allowed to have one null element, marking that column FOR ORDINALITY.

The parameter being passed into the XQuery expressions here, DPREMIER, is spelled in uppercase (and, on the SQL side, quoted), for the reasons explained above for the XMLQUERY-like function.

In the first column expression, @id is wrapped in data() to return the value of the attribute, as @id by itself would be a bare XML attribute node, outside of any XML element. Many implementations (including the XPath-based pseudo-XMLTABLE built in to PostgreSQL) will allow a bare attribute node in a column expression result, and assume the attribute's value is wanted, but a strict interpretation of the spec appears to require raising err:XPTY0004 in that case. So, just use data() to wrap any attribute node being returned in a column expression.

More on that issue and the spec can be found at “About bare attribute nodes” in the code comments.

An XMLCAST-like function

The ISO SQL XMLCAST is used to convert XML content into a value of an SQL data type, or an SQL value to an XML value, following the same precisely-specified conversion rules that are used for the parameters and results of the XMLQUERY and XMLTABLE functions. It can also convert from one XML type to another, though in PostgreSQL, which has just one XML type, the conversion is trivial. In a DBMS with support for the full set of XML types such as XML(CONTENT), XML(DOCUMENT), and XML(SEQUENCE), the rules for casting one to another are more interesting.

This ordinary-function implementation of XMLCAST is used by rewriting an SQL standard form like

SELECT XMLCAST(value AS wantedtype)

into a form like

SELECT result FROM (select value) as v, "xmlcast"(v) AS (result wantedtype)

where either: value is of xml type, wantedtype is xml, or both; in other words, the only case XMLCAST does not handle is where neither the input nor result is of xml type. Because casting XML to XML is not exciting in PostgreSQL, the most useful cases are XML to another SQL type, or the reverse.

The ISO SQL XQuery regular expression features

The SQL standard specifies a string predicate, LIKE_REGEX, for testing a string against an XQuery regular expression (an extension of XML Schema regular expression syntax), and four string functions also based on XQuery regular expressions: OCCURRENCES_REGEX, POSITION_REGEX, SUBSTRING_REGEX, and TRANSLATE_REGEX.

The “flags” parameter to any of these can include any of the XQuery regular expression flags s, m, i, x, and q.

As with the XMLQUERY and XMLTABLE functions, some straightforward rewriting is needed from the SQL-standard syntax into calls of these ordinary functions.

In the current implementation, all of these functions recognize newlines in the way specified by XQuery, not the modified way specified for ISO SQL, as further explained below after the function descriptions. To leave a clear path to a full implementation, these versions all accept an additional parameter w3cNewlines, which must always be present, for now, as w3cNewlines => true. Specifying false, or omitting this parameter, will mean the ISO SQL newline treatment is wanted, and will be rejected as an unsupported feature in this implementation.

To avoid clutter, the w3cNewlines => true is not shown in the examples below.

value LIKE_REGEX pattern
value LIKE_REGEX pattern FLAG flags

like_regex(value, pattern)
like_regex(value, pattern, flags)
like_regex(value, pattern, flag => flags)

OCCURRENCES_REGEX(pattern IN str)
OCCURRENCES_REGEX(pattern FLAG flags IN str)
OCCURRENCES_REGEX(pattern IN str FROM position USING CHARACTERS)

occurrences_regex(pattern, str)
occurrences_regex(pattern, flag => flags, "in" => str)
occurrences_regex(pattern, str, "from" => position)

POSITION_REGEX(START pattern IN str)
POSITION_REGEX(AFTER pattern IN str)
POSITION_REGEX(START pattern IN str OCCURRENCE n)
POSITION_REGEX(START pattern IN str OCCURRENCE n GROUP m)
POSITION_REGEX(START pattern IN str FROM pos OCCURRENCE n GROUP m)

position_regex(pattern, str)
position_regex(pattern, str, after => true)
position_regex(pattern, str, occurrence => n)
position_regex(pattern, str, occurrence => n, "group" => m)
position_regex(pattern, str, "from" => pos, occurrence => n, "group" => m)

SUBSTRING_REGEX(pattern IN str)
SUBSTRING_REGEX(pattern FLAG flags IN str)
SUBSTRING_REGEX(pattern IN str FROM position)
SUBSTRING_REGEX(pattern IN str OCCURRENCE n GROUP m)

substring_regex(pattern, str)
substring_regex(pattern, flag => flags, "in" => str)
substring_regex(pattern, str, "from" => position)
substring_regex(pattern, str, occurrence => n, "group" => m)

TRANSLATE_REGEX(pattern IN str WITH repl)
TRANSLATE_REGEX(pattern IN str WITH repl OCCURRENCE n)
TRANSLATE_REGEX(pattern FLAG flags IN str WITH repl)
TRANSLATE_REGEX(pattern IN str WITH repl FROM position)

translate_regex(pattern, str, "with" => repl)
translate_regex(pattern, str, "with" => repl, occurrence => n)
translate_regex(pattern, flag => flags, "in" => str, "with" => repl)
translate_regex(pattern, str, "with" => repl, "from" => position)

SELECT translate_regex('(?<=o)(?=b)', 'foobar', "with" => '!',
                       flag => ';j', w3cNewlines => true);
 translate_regex 
-----------------
 foobar

SELECT translate_regex('(?<=o)(?=b)|\A\z', 'foobar', "with" => '!',
                       flag => ';j', w3cNewlines => true);
 translate_regex 
-----------------
 foo!bar