2.4. Scalar Functions

XMLCAST

Cast to or from XML:

XMLCAST(expression AS type)

Copy to Clipboard Toggle word wrap

The expression or type must be XML. The returned value will be a type. This is the same functionality as XMLTABLE uses to convert values to the desired runtime type, with the exception that array type targets are not supported with XMLCAST.

XMLCOMMENT

XMLCOMMENT(comment)

Copy to Clipboard Toggle word wrap

This returns an XML comment.

The comment is a string. The returned value is XML.

XMLCONCAT

XMLCONCAT(content [, content]*)

Copy to Clipboard Toggle word wrap

This returns XML with the concatenation of the given XML types. If a value is null, it will be ignored. If all values are null, null is returned. This is how you concatenate two or more XML fragments:

SELECT XMLCONCAT( XMLELEMENT("name", CustomerName), XMLPARSE(CONTENT '
  <a>
	b
  </a>' WELLFORMED) ) FROM Customer c WHERE c.CustomerID = 87; ========================================================== 
  <name>
     Wartian Herkku
  </name>
  <a>
     b
  </a>
  

Copy to Clipboard Toggle word wrap

The content is XML. The returned value is XML.

XMLELEMENT

XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*)
ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*)
NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+

Copy to Clipboard Toggle word wrap

Returns an XML element with the given name and content. If the content value is of a type other than XML, it will be escaped when added to the parent element. Null content values are ignored. Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values.

XMLNAMESPACES is used to provide namespace information. NO DEFAULT is equivalent to defining the default namespace to the null URI - xmlns="". Only one DEFAULT or NO DEFAULT namespace item may be specified. The namespace prefixes xmlns and xml are reserved.

If an attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored.

For example, with an xml_value of <doc/>,

XMLELEMENT(NAME "elem", 1, '<2/>', xml_value)

Copy to Clipboard Toggle word wrap

returns

<elem>1&lt;2/&gt;<doc/><elem/>

Copy to Clipboard Toggle word wrap

name and prefix are identifiers. uri is a string literal. content can be any type. Return value is XML. The return value is valid for use in places where a document is expected.

SELECT XMLELEMENT("name", CustomerName)
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<name>Wartian Herkku</name>
"Multiple Columns"
SELECT XMLELEMENT("customer", 
          XMLELEMENT("name", c.CustomerName),
          XMLELEMENT("contact", c.ContactName))
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
"Columns as Attributes"
SELECT XMLELEMENT("customer", 
          XMLELEMENT("name", c.CustomerName,
            XMLATTRIBUTES(
                  "contact" as c.ContactName,
                  "id" as c.CustomerID
            )
          )
       )
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<customer>
  <name contact="Pirkko Koskitalo" id="87">Wartian Herkku</name>
</customer>

Copy to Clipboard Toggle word wrap

XMLFOREST

XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*)

Copy to Clipboard Toggle word wrap

Returns an concatenation of XML elements for each content item. See XMLELEMENT for the definition of NSP. If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.

name is an identifier. content can be any type. Return value is XML.

You can use XMLFORREST to simplify the declaration of multiple XMLELEMENTS, XMLFOREST function allows you to process multiple columns at once:

SELECT XMLELEMENT("customer", 
          XMLFOREST(
             c.CustomerName AS "name",
             c.ContactName AS "contact"
          ))
FROM   Customer c
WHERE  c.CustomerID = 87;
 
==========================================================
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
XMLAGG

XMLAGG is an aggregate function, that takes a collection of XML elements and returns an aggregated XML document.
XMLAGG(xml)

From above example in XMLElement, each row in the Customer table table will generate row of XML if there are multiple rows matching the criteria. That will generate a valid XML, but it will not be well formed, because it lacks the root element. XMLAGG can used to correct that
"Example"
SELECT XMLELEMENT("customers",
         XMLAGG(
           XMLELEMENT("customer", 
             XMLFOREST(
               c.CustomerName AS "name",
               c.ContactName AS "contact"
             )))
FROM   Customer c
 
 
==========================================================
<customers>
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
<customer><name>Wellington Importadora</name><contact>Paula Parente</contact></customer>
<customer><name>White Clover Markets</name><contact>Karl Jablonski</contact></customer>
</customers>

Copy to Clipboard Toggle word wrap

XMLAGG

XMLAGG is an aggregate function, that takes a collection of XML elements and returns an aggregated XML document.

XMLAGG(xml)

Copy to Clipboard Toggle word wrap

In the XMLElement example, each row in the Customer table generates a row of XML if there are multiple rows matching the criteria. This will be valid XML, but it will not be well formed, because it lacks the root element. Use XMLAGG to correct that:

SELECT XMLELEMENT("customers",
         XMLAGG(
           XMLELEMENT("customer", 
             XMLFOREST(
               c.CustomerName AS "name",
               c.ContactName AS "contact"
             )))
FROM   Customer c
 
 
==========================================================
<customers>
<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
<customer><name>Wellington Importadora</name><contact>Paula Parente</contact></customer>
<customer><name>White Clover Markets</name><contact>Karl Jablonski</contact></customer>
</customers>

Copy to Clipboard Toggle word wrap

XMLPARSE

XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])

Copy to Clipboard Toggle word wrap

Returns an XML type representation of the string value expression. If DOCUMENT is specified, then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.

expr in {string, clob, blob and varbinary}. Return value is XML.

If DOCUMENT is specified then the expression must have a single root element and may or may not contain an XML declaration. If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.

SELECT XMLPARSE(CONTENT '<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>' WELLFORMED);
 

Copy to Clipboard Toggle word wrap

Will return a SQLXML with contents:

<customer><name>Wartian Herkku</name><contact>Pirkko Koskitalo</contact></customer>
 

Copy to Clipboard Toggle word wrap

XMLPI

XMLPI([NAME] name [, content])

Copy to Clipboard Toggle word wrap

Returns an XML processing instruction.

name is an identifier. content is a string. Return value is XML.

XMLQUERY

XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]]
PASSING:=PASSING exp [AS name] [, exp [AS name]]*

Copy to Clipboard Toggle word wrap

Returns the XML result from evaluating the given xquery. See XMLELEMENT for the definition of NSP. Namespaces may also be directly declared in the XQuery prolog.

The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the XQuery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type.

The ON EMPTY clause is used to specify the result when the evaluated sequence is empty. EMPTY ON EMPTY, the default, returns an empty XML result. NULL ON EMPTY returns a null result.

xquery in string. Return value is XML.

Note

XMLQUERY is part of the SQL/XML 2006 specification.

See also XMLTABLE.

XMLEXISTS

Returns true if a non-empty sequence would be returned by evaluating the given xquery.

XMLEXISTS([<NSP>] xquery [<PASSING>]]
 
PASSING:=PASSING exp [AS name] [, exp [AS name]]*

Copy to Clipboard Toggle word wrap

Namespaces may also be directly declared in the xquery prolog.

The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the xquery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type. Null/Unknown will be returned if the context item evaluates to null.

xquery in string. Return value is boolean.

XMLEXISTS is part of the SQL/XML 2006 specification.

XMLSERIALIZE

XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype] [ENCODING enc] [VERSION ver] [(INCLUDING|EXCLUDING) XMLDECLARATION])

Copy to Clipboard Toggle word wrap

Returns a character type representation of the XML expression.

datatype may be character (string, varchar, clob) or binary (blob, varbinary). CONTENT is the default. If DOCUMENT is specified and the XML is not a valid document or fragment, then an exception is raised.

Return value matches data type. If no data type is specified, then CLOB will be assumed.

The encoding enc is specified as an identifier. A character serialization may not specify an encoding. The version ver is specified as a string literal. If a particular XMLDECLARATION is not specified, then the result will have a declaration only if performing a non UTF-8/UTF-16 or non version 1.0 document serialization or the underlying XML has an declaration. If CONTENT is being serialized, then the declaration will be omitted if the value is not a document or element.

The following example produces a BLOB of XML in UTF-16 including the appropriate byte order mark of FE FF and XML declaration:

XMLSERIALIZE(DOCUMENT value AS BLOB ENCODING "UTF-16" INCLUDING XMLDECLARATION)

Copy to Clipboard Toggle word wrap

XMLTEXT

This returns XML text.

XMLTEXT(text)

Copy to Clipboard Toggle word wrap

The text is a string and the returned value is XML.

XSLTRANSFORM

XSLTRANSFORM(doc, xsl)

Copy to Clipboard Toggle word wrap

Applies an XSL stylesheet to the given document.

doc and xsl in {string, clob, xml}. Return value is a CLOB. If either argument is null, the result is null.

XPATHVALUE

XPATHVALUE(doc, xpath)

Copy to Clipboard Toggle word wrap

Applies the XPATH expression to the document and returns a string value for the first matching result. For more control over the results and XQuery, use the XMLQUERY function.

Matching a non-text node will still produce a string result, which includes all descendant text nodes.

doc and xpath in {string, clob, xml}. Return value is a string.

When the input document utilizes namespaces, it is sometimes necessary to specify XPATH that ignores namespaces. For example, given the following XML,

<?xml version="1.0" ?>
  <ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>

Copy to Clipboard Toggle word wrap

the following function results in 'Hello World'.

xpathValue(value, '/*[local-name()="return"])

Copy to Clipboard Toggle word wrap

JSONTOXML

JSONTOXML(rootElementName, json)

Copy to Clipboard Toggle word wrap

Returns an XML document from JSON. The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON BLOBS. If another encoding is used, see the TO_CHARS function (see Section 2.4.5, “String Functions”).

rootElementName is a string, json is in {clob, blob}. Return value is XML. The result is always a well-formed XML document.

The mapping to XML uses the following rules:

• The current element name is initially the rootElementName, and becomes the object value name as the JSON structure is traversed.
• All element names must be valid XML 1.1 names. Invalid names are fully escaped according to the SQLXML specification.
• Each object or primitive value will be enclosed in an element with the current name.
• Unless an array value is the root, it will not be enclosed in an additional element.
• Null values will be represented by an empty element with the attribute xsi:nil="true"
• Boolean and numerical value elements will have the attribute xsi:type set to boolean and decimal respectively.

Example 2.1. Sample JSON to XML for jsonToXml('person', x)

JSON:

{ "firstName" : "John" , "children" : [ "Randy", "Judy" ] }

Copy to Clipboard Toggle word wrap

XML:

<?xml version="1.0" ?><person><firstName>John</firstName><children>Randy</children><children>Judy</children></person>

Copy to Clipboard Toggle word wrap

Example 2.2. Sample JSON to XML for jsonToXml('person', x) with a root array.

JSON:

[{ "firstName" : "George" }, { "firstName" : "Jerry" }]

Copy to Clipboard Toggle word wrap

XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):

<?xml version="1.0" ?><person><person><firstName>George</firstName></person><person><firstName>Jerry</firstName></person></person>

Copy to Clipboard Toggle word wrap

JSON:

Example 2.3. Sample JSON to XML for jsonToXml('root', x) with an invalid name.

{"/invalid" : "abc" }

Copy to Clipboard Toggle word wrap

XML:

Example 2.4. Sample JSON to XML for jsonToXml('root', x) with an invalid name.

<?xml version="1.0" ?>
<root>
  <_u002F_invalid>abc</_u002F_invalid>
</root>

Copy to Clipboard Toggle word wrap

JSONARRAY

JSONARRAY(value...)

Copy to Clipboard Toggle word wrap

Returns a JSON array.

value is any object convertable to a JSON value (see Section 2.4.16, “Conversion to JSON”). Return value is a CLOB marked as being valid JSON. Null values will be included in the result as null literals.

For example:

jsonArray('a"b', 1, null, false, {d'2010-11-21'})

Copy to Clipboard Toggle word wrap

returns

["a\"b",1,null,false,"2010-11-21"]

Copy to Clipboard Toggle word wrap

JSONOBJECT

JSONARRAY(value [as name] ...)

Copy to Clipboard Toggle word wrap

Returns a JSON object.

value is any object convertable to a JSON value (see Section 2.4.16, “Conversion to JSON”). Return value is a clob marked as being valid JSON.

Null values will be included in the result as null literals.

If a name is not supplied and the expression is a column reference, the column name will be used otherwise exprN will be used where N is the 1-based index of the value in the JSONARRAY expression.

For example:

jsonObject('a"b' as val, 1, null as "null")

Copy to Clipboard Toggle word wrap

returns

{"val":"a\"b","expr2":1,"null":null}

Copy to Clipboard Toggle word wrap

JSONPARSE

JSONPARSE(value, wellformed)

Copy to Clipboard Toggle word wrap

Validates and returns a JSON result.

value is blob with an appropriate JSON binary encoding (UTF-8, UTF-16, or UTF-32) or clob. wellformed is a boolean indicating that validation should be skipped. Return value is a CLOB marked as being valid JSON.

A null for either input will return null.

jsonParse('"a"')

Copy to Clipboard Toggle word wrap

JSONARRAY_AGG

This creates a JSON array result as a Clob, including a null value. This is similar to JSONARRAY but aggregates its contents into single object.

SELECT JSONARRAY_AGG(JSONOBJECT(CustomerId, CustomerName))
FROM   Customer c
WHERE  c.CustomerID >= 88;
==========================================================
[{"CustomerId":88, "CustomerName":"Wellington Importadora"}, {"CustomerId":89, "CustomerName":"White Clover Markets"}]

Copy to Clipboard Toggle word wrap

You can also wrap the array:

SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(CustomerId as id, CustomerName as name)) as Customer)
FROM   Customer c
WHERE  c.CustomerID >= 88;
==========================================================
{"Customer":[{"id":89,"name":"Wellington Importadora"},{"id":100,"name":"White Clover Markets"}]}

Copy to Clipboard Toggle word wrap

Conversion Functions

ST_GeomFromText

Returns a geometry from a Clob in WKT format.

 ST_GeomFromText(text [, srid]) 

Copy to Clipboard Toggle word wrap

text is a clob, srid is an optional integer. Return value is a geometry.

ST_GeomFromWKB/ST_GeomFromBinary

Returns a geometry from a blob in WKB format.

 
ST_GeomFromWKB(bin [, srid])

Copy to Clipboard Toggle word wrap

bin is a blob, srid is an optional integer. Return value is a geometry.

ST_GeomFromGeoJSON

Returns a geometry from a Clob in GeoJSON format.

 
ST_GeomFromGeoJson(text [, srid]) 

Copy to Clipboard Toggle word wrap

text is a clob, srid is an optional integer. Return value is a geometry.

ST_GeomFromGML

Returns a geometry from a Clob in GML2 format.

 
ST_GeomFromGML(text [, srid]) 

Copy to Clipboard Toggle word wrap

text is a clob, srid is an optional integer. Return value is a geometry.

ST_AsText

 
ST_GeomAsText(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is clob in WKT format.

ST_AsBinary

 
ST_GeomAsBinary(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is a blob in WKB format.

ST_GeomFromEWKB

Returns a geometry from a blob in EWKB format.

 
ST_GeomFromEWKB(bin) 

Copy to Clipboard Toggle word wrap

The bin is a blob. Return value is a geometry. Only two dimensions are supported.

ST_AsGeoJSON

 
ST_GeomAsGeoJSON(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is a clob with the GeoJSON value.

ST_AsGML

 
ST_GeomAsGML(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is a clob with the GML2 value.

ST_AsEWKT

 
ST_AsEWKT(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is a clob with the EWKT value. The EWKT value is the WKT value with the SRID prefix.

ST_AsKML

 
ST_AsKML(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is a clob with the KML value. The KML value is effectively a simplified GML value and projected into SRID 4326.

Operators

&&

Returns true if the bounding boxes of geom1 and geom2 intersect.

 
geom1 && geom2 

Copy to Clipboard Toggle word wrap

geom1 and geom2 are geometries. The returned value is a boolean.

Relationship Functions

ST_CONTAINS

Returns true if geom1 contains geom2 contains another.

 
ST_CONTAINS(geom1, geom2) 

Copy to Clipboard Toggle word wrap

geom1, geom2 are geometries. Return value is a boolean.

ST_CROSSES

Returns true if the geometries cross.

 
ST_CROSSES(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a boolean.

ST_DISJOINT

Returns true if the geometries are disjoint.

 
ST_DISJOINT(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a boolean.

ST_DISTANCE

Returns the distance between two geometries.

 
ST_DISTANCE(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a double.

ST_EQUALS

Returns true if the two geometries are spatially equal - the points and order may differ, but neither geometry lies outside of the other.

 
ST_EQUALS(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a boolean.

ST_INTERSECTS

Returns true if the geometries intersect.

 
ST_INTERSECT(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a boolean.

ST_OVERLAPS

Returns true if the geometries overlap.

 
ST_OVERLAPS(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a boolean.

ST_TOUCHES

Returns true if the geometries touch.

 
ST_TOUCHES(geom1, geom2) 

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Return value is a boolean.

ST_DWithin

Returns true if the geometries are within a given distance of one another.

 
ST_DWithin(geom1, geom2, dist)

Copy to Clipboard Toggle word wrap

geom1 and geom2 are geometries. dist is a double. The returned value is a boolean.

ST_OrderingEquals

Returns true if geom1 and geom2 have the same structure and the same ordering of points.

 
ST_OrderingEquals(geom1, geom2)

Copy to Clipboard Toggle word wrap

geom1 and geom2 are geometries. The returned value is a boolean.

ST_Relate

Test or return the intersection of geom1 and geom2.

 
ST_Relate(geom1, geom2, pattern)

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. Pattern is a nine character DE-9IM pattern string. The returned value is a boolean.

ST_Within

Returns true if geom1 is completely inside geom2.

 
ST_Within(geom1, geom2)

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are geometries. The returned value is a boolean.

Attributes and Tests

ST_Area

Returns the area of geom.

 
ST_Area(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is a double.

ST_CoordDim

Returns the coordinate dimensions of geom.

 
ST_CoordDim(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is an integer between 0 and 3.

ST_Dimension

This returns the dimension of geom.

 
ST_Dimension(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is an integer between 0 and 3.

ST_EndPoint

This returns the endpoint of the LineString geom. Returns null if geom is not a LineString.

 
ST_EndPoint(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_ExteriorRing

Returns the exterior ring or shell LineString of the Polygon geom. Returns null if geom is not a Polygon.

 
ST_ExteriorRing(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_GeometryN

Returns the nth geometry at the given 1-based index in geom. Returns null if a geometry at the given index does not exist. Non collection types return themselves at the first index.

 
ST_GeometryN(geom, index)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The index is an integer. The returned value is a geometry.

ST_GeometryType

Returns the type name of geom as ST_name, where the name will be LineString, Polygon, Point and so forth.

 
ST_GeometryType(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a string.

ST_HasArc

Tests if the geometry has a circular string. Will currently only report false as curved geometry types are not supported.

 
ST_HasArc(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_InteriorRingN

Returns the nth interior ring LinearString geometry at the given 1-based index in geom. Returns null if a geometry at the given index does not exist or if geom is not a Polygon.

 
ST_InteriorRingN(geom, index)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The index is an integer. The returned value is a geometry.

ST_IsClosed

Returns true if LineString geom is closed. Returns false if geom is not a LineString

 
ST_IsClosed(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The index is an integer. The returned value is a boolean.

ST_IsEmpty

Returns true if the set of points is empty.

 
ST_IsEmpty(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a boolean.

ST_IsRing

Returns true if the LineString geom is a ring. Returns false if geom is not a LineString.

 
ST_IsRing(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a boolean.

ST_IsSimple

Returns true if the geom is simple.

 
ST_IsSimple(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a boolean.

ST_IsValid

Returns true if the geom is valid.

 
ST_IsValid(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a boolean.

ST_Length

Returns the length of a (Multi)LineString otherwise 0.

 
ST_Length(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a double.

ST_NumGeometries

Returns the number of geometries in the geom. Will return 1 if it is not a geometry collection.

 
ST_NumGeometries(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is an integer.

ST_NumInteriorRings

Returns the number of interior rings in the Polygon geom. Returns null if geom is not a Polygon.

 
ST_NumInteriorRings(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is an integer.

ST_NunPoints

Returns the number of points in a geom.

 
ST_NunPoints(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is an integer.

ST_PointOnSurface

Returns a point that is guaranteed to be on the surface of the geom.

 
ST_PointOnSurface(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a point geometry.

ST_Perimeter

Returns the perimeter of the (Multi)Polygon geom. It ill return 0 if the geom is not a (multi)polygon.

 
ST_Perimeter(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a double.

ST_PointN

Returns the nth Point at the given 1-based index in geom. Returns null if a point at the given index does not exist or if the geom is not a LineString.

 
ST_PointN(geom, index)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The index is an integer. The returned value is a geometry.

ST_SRID

Returns the SRID for the geometry.

 
ST_SRID(geom) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. Return value is an integer. A 0 value rather than null will be returned for an unknown SRID on a non-null geometry.

ST_SetSRID

Set the SRID for the given geometry.

 
ST_SetSRID(geom, srid) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. The srid is an integer. The returned value is a geometry. Only the SRID metadata for the geometry is modified.

ST_StartPoint

Returns the start Point of the LineString geom. Returns null if geom is not a LineString.

 
ST_StartPoint(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_X

Returns the X ordinate value, or null if the point is empty. It throws an exception if the geometry is not a point.

 
ST_X(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a double.

ST_Y

Returns the Y ordinate value, or null if the point is empty. It throws an exception if the geometry is not a point.

 
ST_Y(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a double.

ST_Z

Returns the Z ordinate value, or null if the point is empty. It throws an exception if the geometry is not a point. It will typically return null as three dimensions are not fully supported.

 
ST_Z(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a double.

Miscellaneous Functions

ST_Boundary

Computes the boundary of the given geometry.

 
ST_Boundary(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_Buffer

Computes the geometry that has points within the given distance of a geom.

 
ST_Buffer(geom, distance)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The distance is a double. The returned value is a geometry.

ST_Centroid

Computes the geometric center point of a geom.

 
ST_Centroid(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_ConvexHull

Return the smallest convex polygon that contains all of the points in a geom.

 
ST_ConvexHull(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_Difference

Computes the closure of the set of the points contained in geom1 that are not in geom2.

 
ST_Difference(geom1, geom2)

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are the geometry. The returned value is a geometry.

ST_Envelope

Computes the 2D bounding box of the given geometry.

 
ST_Envelope(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_Force_2D

Removes the z coordinate value if it is present.

 
ST_Force_2D(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

ST_Intersection

Computes the point set intersection of the points contained in geom1 and geom2.

 
ST_Intersection(geom1, geom2)

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are the geometry. The returned value is a geometry.

ST_Simplify

Simplifies a geometry using the Douglas-Peucker algorithm, but may oversimplify to an invalid or empty geometry.

 
ST_Simplify(geom, distanceTolerance)

Copy to Clipboard Toggle word wrap

The geom is a geometry. distanceTolerance is a double. The returned value is a geometry.

ST_SimplifyPreserveTopology

Simplifies a Geometry using the Douglas-Peucker algorithm. This always returns a valid geometry.

 
ST_SimplifyPreserveTopology(geom, distanceTolerance)

Copy to Clipboard Toggle word wrap

The geom is a geometry. distanceTolerance is a double. The returned value is a geometry.

ST_SnapToGrid

Snaps all of the points in the geometry to a grid of a given size.

 
ST_SnapToGrid(geom, size)

Copy to Clipboard Toggle word wrap

The geom is a geometry. Size is a double. The returned value is a geometry.

ST_SymDifference

Return the part of geom1 that does not intersect with geom2 and vice versa.

 
ST_SymDifference(geom1, geom2)

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are the geometry. The returned value is a geometry.

ST_Transform

Transforms the geometry value from one coordinate system to another.

 
ST_Tranform(geom, srid) 

Copy to Clipboard Toggle word wrap

The geom is a geometry. srid is an integer. Return value is a geometry. The srid value and the srid of the geometry value must exist in the SPATIAL_REF_SYS view.

ST_Union

Returns a geometry that represents the point set containing all of geom1 and geom2.

 
ST_Union(geom1, geom2)

Copy to Clipboard Toggle word wrap

The geom1 and geom2 are the geometry. The returned value is a geometry.

Aggregate Functions

ST_Extent

Computes the 2D bounding box around all of the geometric values. All values should have the same srid.

 
ST_Extent(geom)

Copy to Clipboard Toggle word wrap

The geom is a geometry. The returned value is a geometry.

Construction Functions

ST_Point

Returns the point for the given coordinates.

 
ST_Point(x, y)

Copy to Clipboard Toggle word wrap

The x and y are doubles. The returned value is a point geometry.

ST_Polygon

Returns the polygon for the given shell and srid.

 
ST_Polygon(geom, srid)

Copy to Clipboard Toggle word wrap

The geom is a linear ring geometry and the srid is an integer. The returned value is a polygon geometry.

HASROLE

hasRole([roleType,] roleName)

Copy to Clipboard Toggle word wrap

Whether the current caller has the JBoss Data Virtualization data role roleName.

roleName must be a string, the return type is boolean.

The two argument form is provided for backwards compatibility. roleType is a string and must be 'data'.

Role names are case-sensitive and only match JBoss Data Virtualization data roles (see Section 7.1, “Data Roles”). JAAS roles/groups names are not valid for this function, unless there is corresponding data role with the same name.

array_get

array_get(array, index)

Copy to Clipboard Toggle word wrap

Returns the object value at a given array index.

array is the object type, index must be an integer, and the return type is object.

One-based indexing is used. The actual array value must be a java.sql.Array or Java array type. An exception will be thrown if the array value is the wrong type of the index is out of bounds.

array_length

array_length(array)

Copy to Clipboard Toggle word wrap

Returns the length for a given array.

array is the object type, and the return type is integer.

The actual array value must be a java.sql.Array or Java array type. An exception will be thrown if the array value is the wrong type.

uuid

uuid()

Copy to Clipboard Toggle word wrap

Returns a universally unique identifier.

The return type is string.

Generates a type 4 (pseudo randomly generated) UUID using a cryptographically strong random number generator. The format is XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX where each X is a hex digit.