blob: a34265749c32cfad96c02e6b0904d9cbe3e6aee7 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>Chapter 9. Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="datatype-pseudo.html" title="8.21. Pseudo-Types" /><link rel="next" href="functions-logical.html" title="9.1. Logical Operators" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">Chapter 9. Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="datatype-pseudo.html" title="8.21. Pseudo-Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql.html" title="Part II. The SQL Language">Up</a></td><th width="60%" align="center">Part II. The SQL Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-logical.html" title="9.1. Logical Operators">Next</a></td></tr></table><hr /></div><div class="chapter" id="FUNCTIONS"><div class="titlepage"><div><div><h2 class="title">Chapter 9. Functions and Operators</h2></div></div></div><div class="toc"><p><strong>Table of Contents</strong></p><dl class="toc"><dt><span class="sect1"><a href="functions-logical.html">9.1. Logical Operators</a></span></dt><dt><span class="sect1"><a href="functions-comparison.html">9.2. Comparison Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-math.html">9.3. Mathematical Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-string.html">9.4. String Functions and Operators</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-string.html#FUNCTIONS-STRING-FORMAT">9.4.1. <code class="function">format</code></a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-binarystring.html">9.5. Binary String Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-bitstring.html">9.6. Bit String Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-matching.html">9.7. Pattern Matching</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-matching.html#FUNCTIONS-LIKE">9.7.1. <code class="function">LIKE</code></a></span></dt><dt><span class="sect2"><a href="functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP">9.7.2. <code class="function">SIMILAR TO</code> Regular Expressions</a></span></dt><dt><span class="sect2"><a href="functions-matching.html#FUNCTIONS-POSIX-REGEXP">9.7.3. <acronym class="acronym">POSIX</acronym> Regular Expressions</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-formatting.html">9.8. Data Type Formatting Functions</a></span></dt><dt><span class="sect1"><a href="functions-datetime.html">9.9. Date/Time Functions and Operators</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT">9.9.1. <code class="function">EXTRACT</code>, <code class="function">date_part</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC">9.9.2. <code class="function">date_trunc</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-BIN">9.9.3. <code class="function">date_bin</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT">9.9.4. <code class="literal">AT TIME ZONE</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT">9.9.5. Current Date/Time</a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-DELAY">9.9.6. Delaying Execution</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-enum.html">9.10. Enum Support Functions</a></span></dt><dt><span class="sect1"><a href="functions-geometry.html">9.11. Geometric Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-net.html">9.12. Network Address Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-textsearch.html">9.13. Text Search Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-uuid.html">9.14. UUID Functions</a></span></dt><dt><span class="sect1"><a href="functions-xml.html">9.15. XML Functions</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-PRODUCING-XML">9.15.1. Producing XML Content</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-PREDICATES">9.15.2. XML Predicates</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-PROCESSING">9.15.3. Processing XML</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-MAPPING">9.15.4. Mapping Tables to XML</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-json.html">9.16. JSON Functions and Operators</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-json.html#FUNCTIONS-JSON-PROCESSING">9.16.1. Processing and Creating JSON Data</a></span></dt><dt><span class="sect2"><a href="functions-json.html#FUNCTIONS-SQLJSON-PATH">9.16.2. The SQL/JSON Path Language</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-sequence.html">9.17. Sequence Manipulation Functions</a></span></dt><dt><span class="sect1"><a href="functions-conditional.html">9.18. Conditional Expressions</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-conditional.html#FUNCTIONS-CASE">9.18.1. <code class="literal">CASE</code></a></span></dt><dt><span class="sect2"><a href="functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL">9.18.2. <code class="literal">COALESCE</code></a></span></dt><dt><span class="sect2"><a href="functions-conditional.html#FUNCTIONS-NULLIF">9.18.3. <code class="literal">NULLIF</code></a></span></dt><dt><span class="sect2"><a href="functions-conditional.html#FUNCTIONS-GREATEST-LEAST">9.18.4. <code class="literal">GREATEST</code> and <code class="literal">LEAST</code></a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-array.html">9.19. Array Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-range.html">9.20. Range/Multirange Functions and Operators</a></span></dt><dt><span class="sect1"><a href="functions-aggregate.html">9.21. Aggregate Functions</a></span></dt><dt><span class="sect1"><a href="functions-window.html">9.22. Window Functions</a></span></dt><dt><span class="sect1"><a href="functions-subquery.html">9.23. Subquery Expressions</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS">9.23.1. <code class="literal">EXISTS</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-IN">9.23.2. <code class="literal">IN</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-NOTIN">9.23.3. <code class="literal">NOT IN</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME">9.23.4. <code class="literal">ANY</code>/<code class="literal">SOME</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-ALL">9.23.5. <code class="literal">ALL</code></a></span></dt><dt><span class="sect2"><a href="functions-subquery.html#FUNCTIONS-SUBQUERY-SINGLE-ROW-COMP">9.23.6. Single-Row Comparison</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-comparisons.html">9.24. Row and Array Comparisons</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR">9.24.1. <code class="literal">IN</code></a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-NOT-IN">9.24.2. <code class="literal">NOT IN</code></a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-ANY-SOME">9.24.3. <code class="literal">ANY</code>/<code class="literal">SOME</code> (array)</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#FUNCTIONS-COMPARISONS-ALL">9.24.4. <code class="literal">ALL</code> (array)</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#ROW-WISE-COMPARISON">9.24.5. Row Constructor Comparison</a></span></dt><dt><span class="sect2"><a href="functions-comparisons.html#COMPOSITE-TYPE-COMPARISON">9.24.6. Composite Type Comparison</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-srf.html">9.25. Set Returning Functions</a></span></dt><dt><span class="sect1"><a href="functions-info.html">9.26. System Information Functions and Operators</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-SESSION">9.26.1. Session Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-ACCESS">9.26.2. Access Privilege Inquiry Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-SCHEMA">9.26.3. Schema Visibility Inquiry Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-CATALOG">9.26.4. System Catalog Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-OBJECT">9.26.5. Object Information and Addressing Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-COMMENT">9.26.6. Comment Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-VALIDITY">9.26.7. Data Validity Checking Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-SNAPSHOT">9.26.8. Transaction ID and Snapshot Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-COMMIT-TIMESTAMP">9.26.9. Committed Transaction Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-CONTROLDATA">9.26.10. Control Data Functions</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-admin.html">9.27. System Administration Functions</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-SET">9.27.1. Configuration Settings Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-SIGNAL">9.27.2. Server Signaling Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-BACKUP">9.27.3. Backup Control Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-RECOVERY-CONTROL">9.27.4. Recovery Control Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION">9.27.5. Snapshot Synchronization Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-REPLICATION">9.27.6. Replication Management Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT">9.27.7. Database Object Management Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-INDEX">9.27.8. Index Maintenance Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADMIN-GENFILE">9.27.9. Generic File Access Functions</a></span></dt><dt><span class="sect2"><a href="functions-admin.html#FUNCTIONS-ADVISORY-LOCKS">9.27.10. Advisory Lock Functions</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-trigger.html">9.28. Trigger Functions</a></span></dt><dt><span class="sect1"><a href="functions-event-triggers.html">9.29. Event Trigger Functions</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-event-triggers.html#PG-EVENT-TRIGGER-DDL-COMMAND-END-FUNCTIONS">9.29.1. Capturing Changes at Command End</a></span></dt><dt><span class="sect2"><a href="functions-event-triggers.html#PG-EVENT-TRIGGER-SQL-DROP-FUNCTIONS">9.29.2. Processing Objects Dropped by a DDL Command</a></span></dt><dt><span class="sect2"><a href="functions-event-triggers.html#PG-EVENT-TRIGGER-TABLE-REWRITE-FUNCTIONS">9.29.3. Handling a Table Rewrite Event</a></span></dt></dl></dd><dt><span class="sect1"><a href="functions-statistics.html">9.30. Statistics Information Functions</a></span></dt><dd><dl><dt><span class="sect2"><a href="functions-statistics.html#FUNCTIONS-STATISTICS-MCV">9.30.1. Inspecting MCV Lists</a></span></dt></dl></dd></dl></div><a id="id-1.5.8.2" class="indexterm"></a><a id="id-1.5.8.3" class="indexterm"></a><p>
<span class="productname">PostgreSQL</span> provides a large number of
functions and operators for the built-in data types. This chapter
describes most of them, although additional special-purpose functions
appear in relevant sections of the manual. Users can also
define their own functions and operators, as described in
<a class="xref" href="server-programming.html" title="Part V. Server Programming">Part V</a>. The
<span class="application">psql</span> commands <code class="command">\df</code> and
<code class="command">\do</code> can be used to list all
available functions and operators, respectively.
</p><p>
The notation used throughout this chapter to describe the argument and
result data types of a function or operator is like this:
</p><pre class="synopsis">
<code class="function">repeat</code> ( <code class="type">text</code>, <code class="type">integer</code> ) → <code class="returnvalue">text</code>
</pre><p>
which says that the function <code class="function">repeat</code> takes one text and
one integer argument and returns a result of type text. The right arrow
is also used to indicate the result of an example, thus:
</p><pre class="programlisting">
repeat('Pg', 4) → <code class="returnvalue">PgPgPgPg</code>
</pre><p>
</p><p>
If you are concerned about portability then note that most of
the functions and operators described in this chapter, with the
exception of the most trivial arithmetic and comparison operators
and some explicitly marked functions, are not specified by the
<acronym class="acronym">SQL</acronym> standard. Some of this extended functionality
is present in other <acronym class="acronym">SQL</acronym> database management
systems, and in many cases this functionality is compatible and
consistent between the various implementations.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="datatype-pseudo.html" title="8.21. Pseudo-Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql.html" title="Part II. The SQL Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-logical.html" title="9.1. Logical Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.21. Pseudo-Types </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.1. Logical Operators</td></tr></table></div></body></html>
|
