<?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>9.25. System Information Functions</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="functions-srf.html" title="9.24. Set Returning Functions" /><link rel="next" href="functions-admin.html" title="9.26. System Administration Functions" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.25. System Information Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-srf.html" title="9.24. Set Returning Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-admin.html" title="9.26. System Administration Functions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-INFO"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.25. System Information Functions</h2></div></div></div><p>
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-SESSION-TABLE" title="Table 9.60. Session Information Functions">Table 9.60</a> shows several
functions that extract session and system information.
</p><p>
In addition to the functions listed in this section, there are a number of
functions related to the statistics system that also provide system
information. See <a class="xref" href="monitoring-stats.html#MONITORING-STATS-VIEWS" title="28.2.2. Viewing Statistics">Section 28.2.2</a> for more
information.
</p><div class="table" id="FUNCTIONS-INFO-SESSION-TABLE"><p class="title"><strong>Table 9.60. Session Information Functions</strong></p><div class="table-contents"><table class="table" summary="Session Information Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">current_catalog</code></code></td><td><code class="type">name</code></td><td>name of current database (called <span class="quote">“<span class="quote">catalog</span>”</span> in the SQL standard)</td></tr><tr><td><code class="literal"><code class="function">current_database()</code></code></td><td><code class="type">name</code></td><td>name of current database</td></tr><tr><td><code class="literal"><code class="function">current_query()</code></code></td><td><code class="type">text</code></td><td>text of the currently executing query, as submitted
by the client (might contain more than one statement)</td></tr><tr><td><code class="literal"><code class="function">current_role</code></code></td><td><code class="type">name</code></td><td>equivalent to <code class="function">current_user</code></td></tr><tr><td><code class="literal"><code class="function">current_schema</code>[()]</code></td><td><code class="type">name</code></td><td>name of current schema</td></tr><tr><td><code class="literal"><code class="function">current_schemas(<code class="type">boolean</code>)</code></code></td><td><code class="type">name[]</code></td><td>names of schemas in search path, optionally including implicit schemas</td></tr><tr><td><code class="literal"><code class="function">current_user</code></code></td><td><code class="type">name</code></td><td>user name of current execution context</td></tr><tr><td><code class="literal"><code class="function">inet_client_addr()</code></code></td><td><code class="type">inet</code></td><td>address of the remote connection</td></tr><tr><td><code class="literal"><code class="function">inet_client_port()</code></code></td><td><code class="type">int</code></td><td>port of the remote connection</td></tr><tr><td><code class="literal"><code class="function">inet_server_addr()</code></code></td><td><code class="type">inet</code></td><td>address of the local connection</td></tr><tr><td><code class="literal"><code class="function">inet_server_port()</code></code></td><td><code class="type">int</code></td><td>port of the local connection</td></tr><tr><td><code class="literal"><code class="function">pg_backend_pid()</code></code></td><td><code class="type">int</code></td><td>
Process ID of the server process attached to the current session
</td></tr><tr><td><code class="literal"><code class="function">pg_blocking_pids(<code class="type">int</code>)</code></code></td><td><code class="type">int[]</code></td><td>Process ID(s) that are blocking specified server process ID from acquiring a lock</td></tr><tr><td><code class="literal"><code class="function">pg_conf_load_time()</code></code></td><td><code class="type">timestamp with time zone</code></td><td>configuration load time</td></tr><tr><td><code class="literal"><code class="function">pg_current_logfile([<span class="optional"><code class="type">text</code></span>])</code></code></td><td><code class="type">text</code></td><td>Primary log file name, or log in the requested format,
currently in use by the logging collector</td></tr><tr><td><code class="literal"><code class="function">pg_my_temp_schema()</code></code></td><td><code class="type">oid</code></td><td>OID of session's temporary schema, or 0 if none</td></tr><tr><td><code class="literal"><code class="function">pg_is_other_temp_schema(<code class="type">oid</code>)</code></code></td><td><code class="type">boolean</code></td><td>is schema another session's temporary schema?</td></tr><tr><td><code class="literal"><code class="function">pg_jit_available()</code></code></td><td><code class="type">boolean</code></td><td>is <acronym class="acronym">JIT</acronym> compilation available in this session
(see <a class="xref" href="jit.html" title="Chapter 32. Just-in-Time Compilation (JIT)">Chapter 32</a>)? Returns <code class="literal">false</code> if <a class="xref" href="runtime-config-query.html#GUC-JIT">jit</a> is set to false.</td></tr><tr><td><code class="literal"><code class="function">pg_listening_channels()</code></code></td><td><code class="type">setof text</code></td><td>channel names that the session is currently listening on</td></tr><tr><td><code class="literal"><code class="function">pg_notification_queue_usage()</code></code></td><td><code class="type">double</code></td><td>fraction of the asynchronous notification queue currently occupied (0-1)</td></tr><tr><td><code class="literal"><code class="function">pg_postmaster_start_time()</code></code></td><td><code class="type">timestamp with time zone</code></td><td>server start time</td></tr><tr><td><code class="literal"><code class="function">pg_safe_snapshot_blocking_pids(<code class="type">int</code>)</code></code></td><td><code class="type">int[]</code></td><td>Process ID(s) that are blocking specified server process ID from acquiring a safe snapshot</td></tr><tr><td><code class="literal"><code class="function">pg_trigger_depth()</code></code></td><td><code class="type">int</code></td><td>current nesting level of <span class="productname">PostgreSQL</span> triggers
(0 if not called, directly or indirectly, from inside a trigger)</td></tr><tr><td><code class="literal"><code class="function">session_user</code></code></td><td><code class="type">name</code></td><td>session user name</td></tr><tr><td><code class="literal"><code class="function">user</code></code></td><td><code class="type">name</code></td><td>equivalent to <code class="function">current_user</code></td></tr><tr><td><code class="literal"><code class="function">version()</code></code></td><td><code class="type">text</code></td><td><span class="productname">PostgreSQL</span> version information. See also <a class="xref" href="runtime-config-preset.html#GUC-SERVER-VERSION-NUM">server_version_num</a> for a machine-readable version.</td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
<code class="function">current_catalog</code>,
<code class="function">current_role</code>,
<code class="function">current_schema</code>,
<code class="function">current_user</code>,
<code class="function">session_user</code>,
and <code class="function">user</code> have special syntactic status
in <acronym class="acronym">SQL</acronym>: they must be called without trailing
parentheses. (In PostgreSQL, parentheses can optionally be used with
<code class="function">current_schema</code>, but not with the others.)
</p></div><a id="id-1.5.8.30.6" class="indexterm"></a><a id="id-1.5.8.30.7" class="indexterm"></a><a id="id-1.5.8.30.8" class="indexterm"></a><a id="id-1.5.8.30.9" class="indexterm"></a><a id="id-1.5.8.30.10" class="indexterm"></a><a id="id-1.5.8.30.11" class="indexterm"></a><a id="id-1.5.8.30.12" class="indexterm"></a><a id="id-1.5.8.30.13" class="indexterm"></a><a id="id-1.5.8.30.14" class="indexterm"></a><a id="id-1.5.8.30.15" class="indexterm"></a><a id="id-1.5.8.30.16" class="indexterm"></a><a id="id-1.5.8.30.17" class="indexterm"></a><a id="id-1.5.8.30.18" class="indexterm"></a><p>
The <code class="function">session_user</code> is normally the user who initiated
the current database connection; but superusers can change this setting
with <a class="xref" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION"><span class="refentrytitle">SET SESSION AUTHORIZATION</span></a>.
The <code class="function">current_user</code> is the user identifier
that is applicable for permission checking. Normally it is equal
to the session user, but it can be changed with
<a class="xref" href="sql-set-role.html" title="SET ROLE"><span class="refentrytitle">SET ROLE</span></a>.
It also changes during the execution of
functions with the attribute <code class="literal">SECURITY DEFINER</code>.
In Unix parlance, the session user is the <span class="quote">“<span class="quote">real user</span>”</span> and
the current user is the <span class="quote">“<span class="quote">effective user</span>”</span>.
<code class="function">current_role</code> and <code class="function">user</code> are
synonyms for <code class="function">current_user</code>. (The SQL standard draws
a distinction between <code class="function">current_role</code>
and <code class="function">current_user</code>, but <span class="productname">PostgreSQL</span>
does not, since it unifies users and roles into a single kind of entity.)
</p><p>
<code class="function">current_schema</code> returns the name of the schema that is
first in the search path (or a null value if the search path is
empty). This is the schema that will be used for any tables or
other named objects that are created without specifying a target schema.
<code class="function">current_schemas(boolean)</code> returns an array of the names of all
schemas presently in the search path. The Boolean option determines whether or not
implicitly included system schemas such as <code class="literal">pg_catalog</code> are included in the
returned search path.
</p><div class="note"><h3 class="title">Note</h3><p>
The search path can be altered at run time. The command is:
</p><pre class="programlisting">
SET search_path TO <em class="replaceable"><code>schema</code></em> [<span class="optional">, <em class="replaceable"><code>schema</code></em>, ...</span>]
</pre><p>
</p></div><a id="id-1.5.8.30.22" class="indexterm"></a><a id="id-1.5.8.30.23" class="indexterm"></a><a id="id-1.5.8.30.24" class="indexterm"></a><a id="id-1.5.8.30.25" class="indexterm"></a><p>
<code class="function">inet_client_addr</code> returns the IP address of the
current client, and <code class="function">inet_client_port</code> returns the
port number.
<code class="function">inet_server_addr</code> returns the IP address on which
the server accepted the current connection, and
<code class="function">inet_server_port</code> returns the port number.
All these functions return NULL if the current connection is via a
Unix-domain socket.
</p><a id="id-1.5.8.30.27" class="indexterm"></a><p>
<code class="function">pg_blocking_pids</code> returns an array of the process IDs
of the sessions that are blocking the server process with the specified
process ID, or an empty array if there is no such server process or it is
not blocked. One server process blocks another if it either holds a lock
that conflicts with the blocked process's lock request (hard block), or is
waiting for a lock that would conflict with the blocked process's lock
request and is ahead of it in the wait queue (soft block). When using
parallel queries the result always lists client-visible process IDs (that
is, <code class="function">pg_backend_pid</code> results) even if the actual lock is held
or awaited by a child worker process. As a result of that, there may be
duplicated PIDs in the result. Also note that when a prepared transaction
holds a conflicting lock, it will be represented by a zero process ID in
the result of this function.
Frequent calls to this function could have some impact on database
performance, because it needs exclusive access to the lock manager's
shared state for a short time.
</p><a id="id-1.5.8.30.29" class="indexterm"></a><p>
<code class="function">pg_conf_load_time</code> returns the
<code class="type">timestamp with time zone</code> when the
server configuration files were last loaded.
(If the current session was alive at the time, this will be the time
when the session itself re-read the configuration files, so the
reading will vary a little in different sessions. Otherwise it is
the time when the postmaster process re-read the configuration files.)
</p><a id="id-1.5.8.30.31" class="indexterm"></a><a id="id-1.5.8.30.32" class="indexterm"></a><a id="id-1.5.8.30.33" class="indexterm"></a><a id="id-1.5.8.30.34" class="indexterm"></a><p>
<code class="function">pg_current_logfile</code> returns, as <code class="type">text</code>,
the path of the log file(s) currently in use by the logging collector.
The path includes the <a class="xref" href="runtime-config-logging.html#GUC-LOG-DIRECTORY">log_directory</a> directory
and the log file name. Log collection must be enabled or the return value
is <code class="literal">NULL</code>. When multiple log files exist, each in a
different format, <code class="function">pg_current_logfile</code> called
without arguments returns the path of the file having the first format
found in the ordered list: <span class="systemitem">stderr</span>, <span class="systemitem">csvlog</span>.
<code class="literal">NULL</code> is returned when no log file has any of these
formats. To request a specific file format supply, as <code class="type">text</code>,
either <span class="systemitem">csvlog</span> or <span class="systemitem">stderr</span> as the value of the
optional parameter. The return value is <code class="literal">NULL</code> when the
log format requested is not a configured
<a class="xref" href="runtime-config-logging.html#GUC-LOG-DESTINATION">log_destination</a>. The
<code class="function">pg_current_logfile</code> reflects the contents of the
<code class="filename">current_logfiles</code> file.
</p><a id="id-1.5.8.30.36" class="indexterm"></a><a id="id-1.5.8.30.37" class="indexterm"></a><p>
<code class="function">pg_my_temp_schema</code> returns the OID of the current
session's temporary schema, or zero if it has none (because it has not
created any temporary tables).
<code class="function">pg_is_other_temp_schema</code> returns true if the
given OID is the OID of another session's temporary schema.
(This can be useful, for example, to exclude other sessions' temporary
tables from a catalog display.)
</p><a id="id-1.5.8.30.39" class="indexterm"></a><a id="id-1.5.8.30.40" class="indexterm"></a><p>
<code class="function">pg_listening_channels</code> returns a set of names of
asynchronous notification channels that the current session is listening
to. <code class="function">pg_notification_queue_usage</code> returns the
fraction of the total available space for notifications currently
occupied by notifications that are waiting to be processed, as a
<code class="type">double</code> in the range 0-1.
See <a class="xref" href="sql-listen.html" title="LISTEN"><span class="refentrytitle">LISTEN</span></a> and <a class="xref" href="sql-notify.html" title="NOTIFY"><span class="refentrytitle">NOTIFY</span></a>
for more information.
</p><a id="id-1.5.8.30.42" class="indexterm"></a><p>
<code class="function">pg_postmaster_start_time</code> returns the
<code class="type">timestamp with time zone</code> when the
server started.
</p><a id="id-1.5.8.30.44" class="indexterm"></a><p>
<code class="function">pg_safe_snapshot_blocking_pids</code> returns an array of
the process IDs of the sessions that are blocking the server process with
the specified process ID from acquiring a safe snapshot, or an empty array
if there is no such server process or it is not blocked. A session
running a <code class="literal">SERIALIZABLE</code> transaction blocks
a <code class="literal">SERIALIZABLE READ ONLY DEFERRABLE</code> transaction from
acquiring a snapshot until the latter determines that it is safe to avoid
taking any predicate locks. See <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a> for
more information about serializable and deferrable transactions. Frequent
calls to this function could have some impact on database performance,
because it needs access to the predicate lock manager's shared
state for a short time.
</p><a id="id-1.5.8.30.46" class="indexterm"></a><p>
<code class="function">version</code> returns a string describing the
<span class="productname">PostgreSQL</span> server's version. You can also
get this information from <a class="xref" href="runtime-config-preset.html#GUC-SERVER-VERSION">server_version</a> or
for a machine-readable version, <a class="xref" href="runtime-config-preset.html#GUC-SERVER-VERSION-NUM">server_version_num</a>.
Software developers should use <code class="literal">server_version_num</code>
(available since 8.2) or <a class="xref" href="libpq-status.html#LIBPQ-PQSERVERVERSION">
<code class="function">PQserverVersion</code>
</a> instead
of parsing the text version.
</p><a id="id-1.5.8.30.48" class="indexterm"></a><p>
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE" title="Table 9.61. Access Privilege Inquiry Functions">Table 9.61</a> lists functions that
allow the user to query object access privileges programmatically.
See <a class="xref" href="ddl-priv.html" title="5.6. Privileges">Section 5.6</a> for more information about
privileges.
</p><div class="table" id="FUNCTIONS-INFO-ACCESS-TABLE"><p class="title"><strong>Table 9.61. Access Privilege Inquiry Functions</strong></p><div class="table-contents"><table class="table" summary="Access Privilege Inquiry Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">has_any_column_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for any column of table</td></tr><tr><td><code class="literal"><code class="function">has_any_column_privilege</code>(<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for any column of table</td></tr><tr><td><code class="literal"><code class="function">has_column_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>column</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for column</td></tr><tr><td><code class="literal"><code class="function">has_column_privilege</code>(<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>column</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for column</td></tr><tr><td><code class="literal"><code class="function">has_database_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>database</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for database</td></tr><tr><td><code class="literal"><code class="function">has_database_privilege</code>(<em class="parameter"><code>database</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for database</td></tr><tr><td><code class="literal"><code class="function">has_foreign_data_wrapper_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>fdw</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for foreign-data wrapper</td></tr><tr><td><code class="literal"><code class="function">has_foreign_data_wrapper_privilege</code>(<em class="parameter"><code>fdw</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for foreign-data wrapper</td></tr><tr><td><code class="literal"><code class="function">has_function_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>function</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for function</td></tr><tr><td><code class="literal"><code class="function">has_function_privilege</code>(<em class="parameter"><code>function</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for function</td></tr><tr><td><code class="literal"><code class="function">has_language_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>language</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for language</td></tr><tr><td><code class="literal"><code class="function">has_language_privilege</code>(<em class="parameter"><code>language</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for language</td></tr><tr><td><code class="literal"><code class="function">has_schema_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>schema</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for schema</td></tr><tr><td><code class="literal"><code class="function">has_schema_privilege</code>(<em class="parameter"><code>schema</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for schema</td></tr><tr><td><code class="literal"><code class="function">has_sequence_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>sequence</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for sequence</td></tr><tr><td><code class="literal"><code class="function">has_sequence_privilege</code>(<em class="parameter"><code>sequence</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for sequence</td></tr><tr><td><code class="literal"><code class="function">has_server_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>server</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for foreign server</td></tr><tr><td><code class="literal"><code class="function">has_server_privilege</code>(<em class="parameter"><code>server</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for foreign server</td></tr><tr><td><code class="literal"><code class="function">has_table_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for table</td></tr><tr><td><code class="literal"><code class="function">has_table_privilege</code>(<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for table</td></tr><tr><td><code class="literal"><code class="function">has_tablespace_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>tablespace</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for tablespace</td></tr><tr><td><code class="literal"><code class="function">has_tablespace_privilege</code>(<em class="parameter"><code>tablespace</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for tablespace</td></tr><tr><td><code class="literal"><code class="function">has_type_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>type</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for type</td></tr><tr><td><code class="literal"><code class="function">has_type_privilege</code>(<em class="parameter"><code>type</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for type</td></tr><tr><td><code class="literal"><code class="function">pg_has_role</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>role</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does user have privilege for role</td></tr><tr><td><code class="literal"><code class="function">pg_has_role</code>(<em class="parameter"><code>role</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have privilege for role</td></tr><tr><td><code class="literal"><code class="function">row_security_active</code>(<em class="parameter"><code>table</code></em>)</code>
</td><td><code class="type">boolean</code></td><td>does current user have row level security active for table</td></tr></tbody></table></div></div><br class="table-break" /><a id="id-1.5.8.30.51" class="indexterm"></a><a id="id-1.5.8.30.52" class="indexterm"></a><a id="id-1.5.8.30.53" class="indexterm"></a><a id="id-1.5.8.30.54" class="indexterm"></a><a id="id-1.5.8.30.55" class="indexterm"></a><a id="id-1.5.8.30.56" class="indexterm"></a><a id="id-1.5.8.30.57" class="indexterm"></a><a id="id-1.5.8.30.58" class="indexterm"></a><a id="id-1.5.8.30.59" class="indexterm"></a><a id="id-1.5.8.30.60" class="indexterm"></a><a id="id-1.5.8.30.61" class="indexterm"></a><a id="id-1.5.8.30.62" class="indexterm"></a><a id="id-1.5.8.30.63" class="indexterm"></a><a id="id-1.5.8.30.64" class="indexterm"></a><p>
<code class="function">has_table_privilege</code> checks whether a user
can access a table in a particular way. The user can be
specified by name, by OID (<code class="literal">pg_authid.oid</code>),
<code class="literal">public</code> to indicate the PUBLIC pseudo-role, or if the argument is
omitted
<code class="function">current_user</code> is assumed. The table can be specified
by name or by OID. (Thus, there are actually six variants of
<code class="function">has_table_privilege</code>, which can be distinguished by
the number and types of their arguments.) When specifying by name,
the name can be schema-qualified if necessary.
The desired access privilege type
is specified by a text string, which must evaluate to one of the
values <code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
<code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, <code class="literal">TRUNCATE</code>,
<code class="literal">REFERENCES</code>, or <code class="literal">TRIGGER</code>. Optionally,
<code class="literal">WITH GRANT OPTION</code> can be added to a privilege type to test
whether the privilege is held with grant option. Also, multiple privilege
types can be listed separated by commas, in which case the result will
be <code class="literal">true</code> if any of the listed privileges is held.
(Case of the privilege string is not significant, and extra whitespace
is allowed between but not within privilege names.)
Some examples:
</p><pre class="programlisting">
SELECT has_table_privilege('myschema.mytable', 'select');
SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
</pre><p>
</p><p>
<code class="function">has_sequence_privilege</code> checks whether a user
can access a sequence in a particular way. The possibilities for its
arguments are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to one of
<code class="literal">USAGE</code>,
<code class="literal">SELECT</code>, or
<code class="literal">UPDATE</code>.
</p><p>
<code class="function">has_any_column_privilege</code> checks whether a user can
access any column of a table in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>,
except that the desired access privilege type must evaluate to some
combination of
<code class="literal">SELECT</code>,
<code class="literal">INSERT</code>,
<code class="literal">UPDATE</code>, or
<code class="literal">REFERENCES</code>. Note that having any of these privileges
at the table level implicitly grants it for each column of the table,
so <code class="function">has_any_column_privilege</code> will always return
<code class="literal">true</code> if <code class="function">has_table_privilege</code> does for the same
arguments. But <code class="function">has_any_column_privilege</code> also succeeds if
there is a column-level grant of the privilege for at least one column.
</p><p>
<code class="function">has_column_privilege</code> checks whether a user
can access a column in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>,
with the addition that the column can be specified either by name
or attribute number.
The desired access privilege type must evaluate to some combination of
<code class="literal">SELECT</code>,
<code class="literal">INSERT</code>,
<code class="literal">UPDATE</code>, or
<code class="literal">REFERENCES</code>. Note that having any of these privileges
at the table level implicitly grants it for each column of the table.
</p><p>
<code class="function">has_database_privilege</code> checks whether a user
can access a database in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to some combination of
<code class="literal">CREATE</code>,
<code class="literal">CONNECT</code>,
<code class="literal">TEMPORARY</code>, or
<code class="literal">TEMP</code> (which is equivalent to
<code class="literal">TEMPORARY</code>).
</p><p>
<code class="function">has_function_privilege</code> checks whether a user
can access a function in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
When specifying a function by a text string rather than by OID,
the allowed input is the same as for the <code class="type">regprocedure</code> data type
(see <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>).
The desired access privilege type must evaluate to
<code class="literal">EXECUTE</code>.
An example is:
</p><pre class="programlisting">
SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
</pre><p>
</p><p>
<code class="function">has_foreign_data_wrapper_privilege</code> checks whether a user
can access a foreign-data wrapper in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">USAGE</code>.
</p><p>
<code class="function">has_language_privilege</code> checks whether a user
can access a procedural language in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">USAGE</code>.
</p><p>
<code class="function">has_schema_privilege</code> checks whether a user
can access a schema in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to some combination of
<code class="literal">CREATE</code> or
<code class="literal">USAGE</code>.
</p><p>
<code class="function">has_server_privilege</code> checks whether a user
can access a foreign server in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">USAGE</code>.
</p><p>
<code class="function">has_tablespace_privilege</code> checks whether a user
can access a tablespace in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">CREATE</code>.
</p><p>
<code class="function">has_type_privilege</code> checks whether a user
can access a type in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>.
When specifying a type by a text string rather than by OID,
the allowed input is the same as for the <code class="type">regtype</code> data type
(see <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>).
The desired access privilege type must evaluate to
<code class="literal">USAGE</code>.
</p><p>
<code class="function">pg_has_role</code> checks whether a user
can access a role in a particular way.
Its argument possibilities
are analogous to <code class="function">has_table_privilege</code>,
except that <code class="literal">public</code> is not allowed as a user name.
The desired access privilege type must evaluate to some combination of
<code class="literal">MEMBER</code> or
<code class="literal">USAGE</code>.
<code class="literal">MEMBER</code> denotes direct or indirect membership in
the role (that is, the right to do <code class="command">SET ROLE</code>), while
<code class="literal">USAGE</code> denotes whether the privileges of the role
are immediately available without doing <code class="command">SET ROLE</code>.
</p><p>
<code class="function">row_security_active</code> checks whether row level
security is active for the specified table in the context of the
<code class="function">current_user</code> and environment. The table can
be specified by name or by OID.
</p><p>
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-SCHEMA-TABLE" title="Table 9.62. Schema Visibility Inquiry Functions">Table 9.62</a> shows functions that
determine whether a certain object is <em class="firstterm">visible</em> in the
current schema search path.
For example, a table is said to be visible if its
containing schema is in the search path and no table of the same
name appears earlier in the search path. This is equivalent to the
statement that the table can be referenced by name without explicit
schema qualification. To list the names of all visible tables:
</p><pre class="programlisting">
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
</pre><p>
</p><a id="id-1.5.8.30.80" class="indexterm"></a><div class="table" id="FUNCTIONS-INFO-SCHEMA-TABLE"><p class="title"><strong>Table 9.62. Schema Visibility Inquiry Functions</strong></p><div class="table-contents"><table class="table" summary="Schema Visibility Inquiry Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">pg_collation_is_visible(<em class="parameter"><code>collation_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is collation visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_conversion_is_visible(<em class="parameter"><code>conversion_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is conversion visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_function_is_visible(<em class="parameter"><code>function_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is function visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_opclass_is_visible(<em class="parameter"><code>opclass_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is operator class visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_operator_is_visible(<em class="parameter"><code>operator_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is operator visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_opfamily_is_visible(<em class="parameter"><code>opclass_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is operator family visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_statistics_obj_is_visible(<em class="parameter"><code>stat_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is statistics object visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_table_is_visible(<em class="parameter"><code>table_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is table visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_ts_config_is_visible(<em class="parameter"><code>config_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is text search configuration visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_ts_dict_is_visible(<em class="parameter"><code>dict_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is text search dictionary visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_ts_parser_is_visible(<em class="parameter"><code>parser_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is text search parser visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_ts_template_is_visible(<em class="parameter"><code>template_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is text search template visible in search path</td></tr><tr><td><code class="literal"><code class="function">pg_type_is_visible(<em class="parameter"><code>type_oid</code></em>)</code></code>
</td><td><code class="type">boolean</code></td><td>is type (or domain) visible in search path</td></tr></tbody></table></div></div><br class="table-break" /><a id="id-1.5.8.30.82" class="indexterm"></a><a id="id-1.5.8.30.83" class="indexterm"></a><a id="id-1.5.8.30.84" class="indexterm"></a><a id="id-1.5.8.30.85" class="indexterm"></a><a id="id-1.5.8.30.86" class="indexterm"></a><a id="id-1.5.8.30.87" class="indexterm"></a><a id="id-1.5.8.30.88" class="indexterm"></a><a id="id-1.5.8.30.89" class="indexterm"></a><a id="id-1.5.8.30.90" class="indexterm"></a><a id="id-1.5.8.30.91" class="indexterm"></a><a id="id-1.5.8.30.92" class="indexterm"></a><a id="id-1.5.8.30.93" class="indexterm"></a><a id="id-1.5.8.30.94" class="indexterm"></a><p>
Each function performs the visibility check for one type of database
object. Note that <code class="function">pg_table_is_visible</code> can also be used
with views, materialized views, indexes, sequences and foreign tables;
<code class="function">pg_function_is_visible</code> can also be used with
procedures and aggregates;
<code class="function">pg_type_is_visible</code> can also be used with domains.
For functions and operators, an object in
the search path is visible if there is no object of the same name
<span class="emphasis"><em>and argument data type(s)</em></span> earlier in the path. For operator
classes, both name and associated index access method are considered.
</p><p>
All these functions require object OIDs to identify the object to be
checked. If you want to test an object by name, it is convenient to use
the OID alias types (<code class="type">regclass</code>, <code class="type">regtype</code>,
<code class="type">regprocedure</code>, <code class="type">regoperator</code>, <code class="type">regconfig</code>,
or <code class="type">regdictionary</code>),
for example:
</p><pre class="programlisting">
SELECT pg_type_is_visible('myschema.widget'::regtype);
</pre><p>
Note that it would not make much sense to test a non-schema-qualified
type name in this way — if the name can be recognized at all, it must be visible.
</p><a id="id-1.5.8.30.97" class="indexterm"></a><a id="id-1.5.8.30.98" class="indexterm"></a><a id="id-1.5.8.30.99" class="indexterm"></a><a id="id-1.5.8.30.100" class="indexterm"></a><a id="id-1.5.8.30.101" class="indexterm"></a><a id="id-1.5.8.30.102" class="indexterm"></a><a id="id-1.5.8.30.103" class="indexterm"></a><a id="id-1.5.8.30.104" class="indexterm"></a><a id="id-1.5.8.30.105" class="indexterm"></a><a id="id-1.5.8.30.106" class="indexterm"></a><a id="id-1.5.8.30.107" class="indexterm"></a><a id="id-1.5.8.30.108" class="indexterm"></a><a id="id-1.5.8.30.109" class="indexterm"></a><a id="id-1.5.8.30.110" class="indexterm"></a><a id="id-1.5.8.30.111" class="indexterm"></a><a id="id-1.5.8.30.112" class="indexterm"></a><a id="id-1.5.8.30.113" class="indexterm"></a><a id="id-1.5.8.30.114" class="indexterm"></a><a id="id-1.5.8.30.115" class="indexterm"></a><a id="id-1.5.8.30.116" class="indexterm"></a><a id="id-1.5.8.30.117" class="indexterm"></a><a id="id-1.5.8.30.118" class="indexterm"></a><a id="id-1.5.8.30.119" class="indexterm"></a><a id="id-1.5.8.30.120" class="indexterm"></a><a id="id-1.5.8.30.121" class="indexterm"></a><a id="id-1.5.8.30.122" class="indexterm"></a><a id="id-1.5.8.30.123" class="indexterm"></a><a id="id-1.5.8.30.124" class="indexterm"></a><a id="id-1.5.8.30.125" class="indexterm"></a><a id="id-1.5.8.30.126" class="indexterm"></a><a id="id-1.5.8.30.127" class="indexterm"></a><p>
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE" title="Table 9.63. System Catalog Information Functions">Table 9.63</a> lists functions that
extract information from the system catalogs.
</p><div class="table" id="FUNCTIONS-INFO-CATALOG-TABLE"><p class="title"><strong>Table 9.63. System Catalog Information Functions</strong></p><div class="table-contents"><table class="table" summary="System Catalog Information Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">format_type(<em class="parameter"><code>type_oid</code></em>, <em class="parameter"><code>typemod</code></em>)</code></code></td><td><code class="type">text</code></td><td>get SQL name of a data type</td></tr><tr><td><code class="literal"><code class="function">pg_get_constraintdef(<em class="parameter"><code>constraint_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get definition of a constraint</td></tr><tr><td><code class="literal"><code class="function">pg_get_constraintdef(<em class="parameter"><code>constraint_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></code></td><td><code class="type">text</code></td><td>get definition of a constraint</td></tr><tr><td><code class="literal"><code class="function">pg_get_expr(<em class="parameter"><code>pg_node_tree</code></em>, <em class="parameter"><code>relation_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>decompile internal form of an expression, assuming that any Vars
in it refer to the relation indicated by the second parameter</td></tr><tr><td><code class="literal"><code class="function">pg_get_expr(<em class="parameter"><code>pg_node_tree</code></em>, <em class="parameter"><code>relation_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></code></td><td><code class="type">text</code></td><td>decompile internal form of an expression, assuming that any Vars
in it refer to the relation indicated by the second parameter</td></tr><tr><td><code class="literal"><code class="function">pg_get_functiondef(<em class="parameter"><code>func_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get definition of a function or procedure</td></tr><tr><td><code class="literal"><code class="function">pg_get_function_arguments(<em class="parameter"><code>func_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get argument list of function's or procedure's definition (with default values)</td></tr><tr><td><code class="literal"><code class="function">pg_get_function_identity_arguments(<em class="parameter"><code>func_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get argument list to identify a function or procedure (without default values)</td></tr><tr><td><code class="literal"><code class="function">pg_get_function_result(<em class="parameter"><code>func_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get <code class="literal">RETURNS</code> clause for function (returns null for a procedure)</td></tr><tr><td><code class="literal"><code class="function">pg_get_indexdef(<em class="parameter"><code>index_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get <code class="command">CREATE INDEX</code> command for index</td></tr><tr><td><code class="literal"><code class="function">pg_get_indexdef(<em class="parameter"><code>index_oid</code></em>, <em class="parameter"><code>column_no</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></code></td><td><code class="type">text</code></td><td>get <code class="command">CREATE INDEX</code> command for index,
or definition of just one index column when
<em class="parameter"><code>column_no</code></em> is not zero</td></tr><tr><td><code class="literal"><code class="function">pg_get_keywords()</code></code></td><td><code class="type">setof record</code></td><td>get list of SQL keywords and their categories</td></tr><tr><td><code class="literal"><code class="function">pg_get_ruledef(<em class="parameter"><code>rule_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get <code class="command">CREATE RULE</code> command for rule</td></tr><tr><td><code class="literal"><code class="function">pg_get_ruledef(<em class="parameter"><code>rule_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></code></td><td><code class="type">text</code></td><td>get <code class="command">CREATE RULE</code> command for rule</td></tr><tr><td><code class="literal"><code class="function">pg_get_serial_sequence(<em class="parameter"><code>table_name</code></em>, <em class="parameter"><code>column_name</code></em>)</code></code></td><td><code class="type">text</code></td><td>get name of the sequence that a serial or identity column uses</td></tr><tr><td><code class="literal"><code class="function">pg_get_statisticsobjdef(<em class="parameter"><code>statobj_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get <code class="command">CREATE STATISTICS</code> command for extended statistics object</td></tr><tr><td><code class="function">pg_get_triggerdef</code>(<em class="parameter"><code>trigger_oid</code></em>)</td><td><code class="type">text</code></td><td>get <code class="command">CREATE [ CONSTRAINT ] TRIGGER</code> command for trigger</td></tr><tr><td><code class="function">pg_get_triggerdef</code>(<em class="parameter"><code>trigger_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</td><td><code class="type">text</code></td><td>get <code class="command">CREATE [ CONSTRAINT ] TRIGGER</code> command for trigger</td></tr><tr><td><code class="literal"><code class="function">pg_get_userbyid(<em class="parameter"><code>role_oid</code></em>)</code></code></td><td><code class="type">name</code></td><td>get role name with given OID</td></tr><tr><td><code class="literal"><code class="function">pg_get_viewdef(<em class="parameter"><code>view_name</code></em>)</code></code></td><td><code class="type">text</code></td><td>get underlying <code class="command">SELECT</code> command for view or materialized view (<span class="emphasis"><em>deprecated</em></span>)</td></tr><tr><td><code class="literal"><code class="function">pg_get_viewdef(<em class="parameter"><code>view_name</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></code></td><td><code class="type">text</code></td><td>get underlying <code class="command">SELECT</code> command for view or materialized view (<span class="emphasis"><em>deprecated</em></span>)</td></tr><tr><td><code class="literal"><code class="function">pg_get_viewdef(<em class="parameter"><code>view_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get underlying <code class="command">SELECT</code> command for view or materialized view</td></tr><tr><td><code class="literal"><code class="function">pg_get_viewdef(<em class="parameter"><code>view_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></code></td><td><code class="type">text</code></td><td>get underlying <code class="command">SELECT</code> command for view or materialized view</td></tr><tr><td><code class="literal"><code class="function">pg_get_viewdef(<em class="parameter"><code>view_oid</code></em>, <em class="parameter"><code>wrap_column_int</code></em>)</code></code></td><td><code class="type">text</code></td><td>get underlying <code class="command">SELECT</code> command for view or
materialized view; lines with fields are wrapped to specified
number of columns, pretty-printing is implied</td></tr><tr><td><code class="literal"><code class="function">pg_index_column_has_property(<em class="parameter"><code>index_oid</code></em>, <em class="parameter"><code>column_no</code></em>, <em class="parameter"><code>prop_name</code></em>)</code></code></td><td><code class="type">boolean</code></td><td>test whether an index column has a specified property</td></tr><tr><td><code class="literal"><code class="function">pg_index_has_property(<em class="parameter"><code>index_oid</code></em>, <em class="parameter"><code>prop_name</code></em>)</code></code></td><td><code class="type">boolean</code></td><td>test whether an index has a specified property</td></tr><tr><td><code class="literal"><code class="function">pg_indexam_has_property(<em class="parameter"><code>am_oid</code></em>, <em class="parameter"><code>prop_name</code></em>)</code></code></td><td><code class="type">boolean</code></td><td>test whether an index access method has a specified property</td></tr><tr><td><code class="literal"><code class="function">pg_options_to_table(<em class="parameter"><code>reloptions</code></em>)</code></code></td><td><code class="type">setof record</code></td><td>get the set of storage option name/value pairs</td></tr><tr><td><code class="literal"><code class="function">pg_tablespace_databases(<em class="parameter"><code>tablespace_oid</code></em>)</code></code></td><td><code class="type">setof oid</code></td><td>get the set of database OIDs that have objects in the tablespace</td></tr><tr><td><code class="literal"><code class="function">pg_tablespace_location(<em class="parameter"><code>tablespace_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get the path in the file system that this tablespace is located in</td></tr><tr><td><code class="literal"><code class="function">pg_typeof(<em class="parameter"><code>any</code></em>)</code></code></td><td><code class="type">regtype</code></td><td>get the data type of any value</td></tr><tr><td><code class="literal"><code class="function">collation for (<em class="parameter"><code>any</code></em>)</code></code></td><td><code class="type">text</code></td><td>get the collation of the argument</td></tr><tr><td><code class="literal"><code class="function">to_regclass(<em class="parameter"><code>rel_name</code></em>)</code></code></td><td><code class="type">regclass</code></td><td>get the OID of the named relation</td></tr><tr><td><code class="literal"><code class="function">to_regproc(<em class="parameter"><code>func_name</code></em>)</code></code></td><td><code class="type">regproc</code></td><td>get the OID of the named function</td></tr><tr><td><code class="literal"><code class="function">to_regprocedure(<em class="parameter"><code>func_name</code></em>)</code></code></td><td><code class="type">regprocedure</code></td><td>get the OID of the named function</td></tr><tr><td><code class="literal"><code class="function">to_regoper(<em class="parameter"><code>operator_name</code></em>)</code></code></td><td><code class="type">regoper</code></td><td>get the OID of the named operator</td></tr><tr><td><code class="literal"><code class="function">to_regoperator(<em class="parameter"><code>operator_name</code></em>)</code></code></td><td><code class="type">regoperator</code></td><td>get the OID of the named operator</td></tr><tr><td><code class="literal"><code class="function">to_regtype(<em class="parameter"><code>type_name</code></em>)</code></code></td><td><code class="type">regtype</code></td><td>get the OID of the named type</td></tr><tr><td><code class="literal"><code class="function">to_regnamespace(<em class="parameter"><code>schema_name</code></em>)</code></code></td><td><code class="type">regnamespace</code></td><td>get the OID of the named schema</td></tr><tr><td><code class="literal"><code class="function">to_regrole(<em class="parameter"><code>role_name</code></em>)</code></code></td><td><code class="type">regrole</code></td><td>get the OID of the named role</td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">format_type</code> returns the SQL name of a data type that
is identified by its type OID and possibly a type modifier. Pass NULL
for the type modifier if no specific modifier is known.
</p><p>
<code class="function">pg_get_keywords</code> returns a set of records describing
the SQL keywords recognized by the server. The <code class="structfield">word</code> column
contains the keyword. The <code class="structfield">catcode</code> column contains a
category code: <code class="literal">U</code> for unreserved, <code class="literal">C</code> for column name,
<code class="literal">T</code> for type or function name, or <code class="literal">R</code> for reserved.
The <code class="structfield">catdesc</code> column contains a possibly-localized string
describing the category.
</p><p>
<code class="function">pg_get_constraintdef</code>,
<code class="function">pg_get_indexdef</code>, <code class="function">pg_get_ruledef</code>,
<code class="function">pg_get_statisticsobjdef</code>, and
<code class="function">pg_get_triggerdef</code>, respectively reconstruct the
creating command for a constraint, index, rule, extended statistics object,
or trigger. (Note that this is a decompiled reconstruction, not the
original text of the command.) <code class="function">pg_get_expr</code> decompiles
the internal form of an individual expression, such as the default value
for a column. It can be useful when examining the contents of system
catalogs. If the expression might contain Vars, specify the OID of the
relation they refer to as the second parameter; if no Vars are expected,
zero is sufficient. <code class="function">pg_get_viewdef</code> reconstructs the
<code class="command">SELECT</code> query that defines a view. Most of these functions come
in two variants, one of which can optionally <span class="quote">“<span class="quote">pretty-print</span>”</span> the
result. The pretty-printed format is more readable, but the default format
is more likely to be interpreted the same way by future versions of
<span class="productname">PostgreSQL</span>; avoid using pretty-printed output for dump
purposes. Passing <code class="literal">false</code> for the pretty-print parameter yields
the same result as the variant that does not have the parameter at all.
</p><p>
<code class="function">pg_get_functiondef</code> returns a complete
<code class="command">CREATE OR REPLACE FUNCTION</code> statement for a function.
<code class="function">pg_get_function_arguments</code> returns the argument list
of a function, in the form it would need to appear in within
<code class="command">CREATE FUNCTION</code>.
<code class="function">pg_get_function_result</code> similarly returns the
appropriate <code class="literal">RETURNS</code> clause for the function.
<code class="function">pg_get_function_identity_arguments</code> returns the
argument list necessary to identify a function, in the form it
would need to appear in within <code class="command">ALTER FUNCTION</code>, for
instance. This form omits default values.
</p><p>
<code class="function">pg_get_serial_sequence</code> returns the name of the
sequence associated with a column, or NULL if no sequence is associated
with the column. If the column is an identity column, the associated
sequence is the sequence internally created for the identity column. For
columns created using one of the serial types
(<code class="type">serial</code>, <code class="type">smallserial</code>, <code class="type">bigserial</code>), it
is the sequence created for that serial column definition. In the latter
case, this association can be modified or removed with <code class="command">ALTER
SEQUENCE OWNED BY</code>. (The function probably should have been called
<code class="function">pg_get_owned_sequence</code>; its current name reflects the
fact that it has typically been used with <code class="type">serial</code>
or <code class="type">bigserial</code> columns.) The first input parameter is a table name
with optional schema, and the second parameter is a column name. Because
the first parameter is potentially a schema and table, it is not treated as
a double-quoted identifier, meaning it is lower cased by default, while the
second parameter, being just a column name, is treated as double-quoted and
has its case preserved. The function returns a value suitably formatted
for passing to sequence functions
(see <a class="xref" href="functions-sequence.html" title="9.16. Sequence Manipulation Functions">Section 9.16</a>). A typical use is in reading the
current value of a sequence for an identity or serial column, for example:
</p><pre class="programlisting">
SELECT currval(pg_get_serial_sequence('sometable', 'id'));
</pre><p>
</p><p>
<code class="function">pg_get_userbyid</code> extracts a role's name given
its OID.
</p><p>
<code class="function">pg_index_column_has_property</code>,
<code class="function">pg_index_has_property</code>, and
<code class="function">pg_indexam_has_property</code> return whether the
specified index column, index, or index access method possesses the named
property. <code class="literal">NULL</code> is returned if the property name is not
known or does not apply to the particular object, or if the OID or column
number does not identify a valid object. Refer to
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS" title="Table 9.64. Index Column Properties">Table 9.64</a> for column properties,
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-INDEX-PROPS" title="Table 9.65. Index Properties">Table 9.65</a> for index properties, and
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-INDEXAM-PROPS" title="Table 9.66. Index Access Method Properties">Table 9.66</a> for access method properties.
(Note that extension access methods can define additional property names
for their indexes.)
</p><div class="table" id="FUNCTIONS-INFO-INDEX-COLUMN-PROPS"><p class="title"><strong>Table 9.64. Index Column Properties</strong></p><div class="table-contents"><table class="table" summary="Index Column Properties" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Name</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">asc</code></td><td>Does the column sort in ascending order on a forward scan?
</td></tr><tr><td><code class="literal">desc</code></td><td>Does the column sort in descending order on a forward scan?
</td></tr><tr><td><code class="literal">nulls_first</code></td><td>Does the column sort with nulls first on a forward scan?
</td></tr><tr><td><code class="literal">nulls_last</code></td><td>Does the column sort with nulls last on a forward scan?
</td></tr><tr><td><code class="literal">orderable</code></td><td>Does the column possess any defined sort ordering?
</td></tr><tr><td><code class="literal">distance_orderable</code></td><td>Can the column be scanned in order by a <span class="quote">“<span class="quote">distance</span>”</span>
operator, for example <code class="literal">ORDER BY col <-> constant</code> ?
</td></tr><tr><td><code class="literal">returnable</code></td><td>Can the column value be returned by an index-only scan?
</td></tr><tr><td><code class="literal">search_array</code></td><td>Does the column natively support <code class="literal">col = ANY(array)</code>
searches?
</td></tr><tr><td><code class="literal">search_nulls</code></td><td>Does the column support <code class="literal">IS NULL</code> and
<code class="literal">IS NOT NULL</code> searches?
</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-INFO-INDEX-PROPS"><p class="title"><strong>Table 9.65. Index Properties</strong></p><div class="table-contents"><table class="table" summary="Index Properties" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Name</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">clusterable</code></td><td>Can the index be used in a <code class="literal">CLUSTER</code> command?
</td></tr><tr><td><code class="literal">index_scan</code></td><td>Does the index support plain (non-bitmap) scans?
</td></tr><tr><td><code class="literal">bitmap_scan</code></td><td>Does the index support bitmap scans?
</td></tr><tr><td><code class="literal">backward_scan</code></td><td>Can the scan direction be changed in mid-scan (to
support <code class="literal">FETCH BACKWARD</code> on a cursor without
needing materialization)?
</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-INFO-INDEXAM-PROPS"><p class="title"><strong>Table 9.66. Index Access Method Properties</strong></p><div class="table-contents"><table class="table" summary="Index Access Method Properties" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Name</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">can_order</code></td><td>Does the access method support <code class="literal">ASC</code>,
<code class="literal">DESC</code> and related keywords in
<code class="literal">CREATE INDEX</code>?
</td></tr><tr><td><code class="literal">can_unique</code></td><td>Does the access method support unique indexes?
</td></tr><tr><td><code class="literal">can_multi_col</code></td><td>Does the access method support indexes with multiple columns?
</td></tr><tr><td><code class="literal">can_exclude</code></td><td>Does the access method support exclusion constraints?
</td></tr><tr><td><code class="literal">can_include</code></td><td>Does the access method support the <code class="literal">INCLUDE</code>
clause of <code class="literal">CREATE INDEX</code>?
</td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">pg_options_to_table</code> returns the set of storage
option name/value pairs
(<em class="replaceable"><code>option_name</code></em>/<em class="replaceable"><code>option_value</code></em>) when passed
<code class="structname">pg_class</code>.<code class="structfield">reloptions</code> or
<code class="structname">pg_attribute</code>.<code class="structfield">attoptions</code>.
</p><p>
<code class="function">pg_tablespace_databases</code> allows a tablespace to be
examined. It returns the set of OIDs of databases that have objects stored
in the tablespace. If this function returns any rows, the tablespace is not
empty and cannot be dropped. To display the specific objects populating the
tablespace, you will need to connect to the databases identified by
<code class="function">pg_tablespace_databases</code> and query their
<code class="structname">pg_class</code> catalogs.
</p><p>
<code class="function">pg_typeof</code> returns the OID of the data type of the
value that is passed to it. This can be helpful for troubleshooting or
dynamically constructing SQL queries. The function is declared as
returning <code class="type">regtype</code>, which is an OID alias type (see
<a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); this means that it is the same as an
OID for comparison purposes but displays as a type name. For example:
</p><pre class="programlisting">
SELECT pg_typeof(33);
pg_typeof
-----------
integer
(1 row)
SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
typlen
--------
4
(1 row)
</pre><p>
</p><p>
The expression <code class="literal">collation for</code> returns the collation of the
value that is passed to it. Example:
</p><pre class="programlisting">
SELECT collation for (description) FROM pg_description LIMIT 1;
pg_collation_for
------------------
"default"
(1 row)
SELECT collation for ('foo' COLLATE "de_DE");
pg_collation_for
------------------
"de_DE"
(1 row)
</pre><p>
The value might be quoted and schema-qualified. If no collation is derived
for the argument expression, then a null value is returned. If the argument
is not of a collatable data type, then an error is raised.
</p><p>
The <code class="function">to_regclass</code>, <code class="function">to_regproc</code>,
<code class="function">to_regprocedure</code>, <code class="function">to_regoper</code>,
<code class="function">to_regoperator</code>, <code class="function">to_regtype</code>,
<code class="function">to_regnamespace</code>, and <code class="function">to_regrole</code>
functions translate relation, function, operator, type, schema, and role
names (given as <code class="type">text</code>) to objects of
type <code class="type">regclass</code>, <code class="type">regproc</code>, <code class="type">regprocedure</code>,
<code class="type">regoper</code>, <code class="type">regoperator</code>, <code class="type">regtype</code>,
<code class="type">regnamespace</code>, and <code class="type">regrole</code>
respectively. These functions differ from a cast from
text in that they don't accept a numeric OID, and that they return null
rather than throwing an error if the name is not found (or, for
<code class="function">to_regproc</code> and <code class="function">to_regoper</code>, if
the given name matches multiple objects).
</p><a id="id-1.5.8.30.145" class="indexterm"></a><a id="id-1.5.8.30.146" class="indexterm"></a><a id="id-1.5.8.30.147" class="indexterm"></a><a id="id-1.5.8.30.148" class="indexterm"></a><p>
<a class="xref" href="functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE" title="Table 9.67. Object Information and Addressing Functions">Table 9.67</a> lists functions related to
database object identification and addressing.
</p><div class="table" id="FUNCTIONS-INFO-OBJECT-TABLE"><p class="title"><strong>Table 9.67. Object Information and Addressing Functions</strong></p><div class="table-contents"><table class="table" summary="Object Information and Addressing Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">pg_describe_object(<em class="parameter"><code>classid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objsubid</code></em> <code class="type">integer</code>)</code></code></td><td><code class="type">text</code></td><td>get description of a database object</td></tr><tr><td><code class="literal"><code class="function">pg_identify_object(<em class="parameter"><code>classid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objsubid</code></em> <code class="type">integer</code>)</code></code></td><td><em class="parameter"><code>type</code></em> <code class="type">text</code>, <em class="parameter"><code>schema</code></em> <code class="type">text</code>, <em class="parameter"><code>name</code></em> <code class="type">text</code>, <em class="parameter"><code>identity</code></em> <code class="type">text</code></td><td>get identity of a database object</td></tr><tr><td><code class="literal"><code class="function">pg_identify_object_as_address(<em class="parameter"><code>classid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objsubid</code></em> <code class="type">integer</code>)</code></code></td><td><em class="parameter"><code>type</code></em> <code class="type">text</code>, <em class="parameter"><code>object_names</code></em> <code class="type">text[]</code>, <em class="parameter"><code>object_args</code></em> <code class="type">text[]</code></td><td>get external representation of a database object's address</td></tr><tr><td><code class="literal"><code class="function">pg_get_object_address(<em class="parameter"><code>type</code></em> <code class="type">text</code>, <em class="parameter"><code>object_names</code></em> <code class="type">text[]</code>, <em class="parameter"><code>object_args</code></em> <code class="type">text[]</code>)</code></code></td><td><em class="parameter"><code>classid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objid</code></em> <code class="type">oid</code>, <em class="parameter"><code>objsubid</code></em> <code class="type">integer</code></td><td>get address of a database object from its external representation</td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">pg_describe_object</code> returns a textual description of a database
object specified by catalog OID, object OID, and sub-object ID (such as
a column number within a table; the sub-object ID is zero when referring
to a whole object).
This description is intended to be human-readable, and might be translated,
depending on server configuration.
This is useful to determine the identity of an object as stored in the
<code class="structname">pg_depend</code> catalog.
</p><p>
<code class="function">pg_identify_object</code> returns a row containing enough information
to uniquely identify the database object specified by catalog OID, object OID and
sub-object ID. This information is intended to be machine-readable,
and is never translated.
<em class="parameter"><code>type</code></em> identifies the type of database object;
<em class="parameter"><code>schema</code></em> is the schema name that the object belongs in, or
<code class="literal">NULL</code> for object types that do not belong to schemas;
<em class="parameter"><code>name</code></em> is the name of the object, quoted if necessary,
if the name (along with schema name, if pertinent) is sufficient to
uniquely identify the object, otherwise <code class="literal">NULL</code>;
<em class="parameter"><code>identity</code></em> is the complete object identity, with the
precise format depending on object type, and each name within the format
being schema-qualified and quoted as necessary.
</p><p>
<code class="function">pg_identify_object_as_address</code> returns a row containing
enough information to uniquely identify the database object specified by
catalog OID, object OID and sub-object ID. The returned
information is independent of the current server, that is, it could be used
to identify an identically named object in another server.
<em class="parameter"><code>type</code></em> identifies the type of database object;
<em class="parameter"><code>object_names</code></em> and <em class="parameter"><code>object_args</code></em>
are text arrays that together form a reference to the object.
These three values can be passed to
<code class="function">pg_get_object_address</code> to obtain the internal address
of the object.
This function is the inverse of <code class="function">pg_get_object_address</code>.
</p><p>
<code class="function">pg_get_object_address</code> returns a row containing enough
information to uniquely identify the database object specified by its
type and object name and argument arrays. The returned values are the
ones that would be used in system catalogs such as <code class="structname">pg_depend</code>
and can be passed to other system functions such as
<code class="function">pg_identify_object</code> or <code class="function">pg_describe_object</code>.
<em class="parameter"><code>classid</code></em> is the OID of the system catalog containing the
object;
<em class="parameter"><code>objid</code></em> is the OID of the object itself, and
<em class="parameter"><code>objsubid</code></em> is the sub-object ID, or zero if none.
This function is the inverse of <code class="function">pg_identify_object_as_address</code>.
</p><a id="id-1.5.8.30.155" class="indexterm"></a><a id="id-1.5.8.30.156" class="indexterm"></a><a id="id-1.5.8.30.157" class="indexterm"></a><a id="id-1.5.8.30.158" class="indexterm"></a><p>
The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE" title="Table 9.68. Comment Information Functions">Table 9.68</a>
extract comments previously stored with the <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a>
command. A null value is returned if no
comment could be found for the specified parameters.
</p><div class="table" id="FUNCTIONS-INFO-COMMENT-TABLE"><p class="title"><strong>Table 9.68. Comment Information Functions</strong></p><div class="table-contents"><table class="table" summary="Comment Information Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">col_description(<em class="parameter"><code>table_oid</code></em>, <em class="parameter"><code>column_number</code></em>)</code></code></td><td><code class="type">text</code></td><td>get comment for a table column</td></tr><tr><td><code class="literal"><code class="function">obj_description(<em class="parameter"><code>object_oid</code></em>, <em class="parameter"><code>catalog_name</code></em>)</code></code></td><td><code class="type">text</code></td><td>get comment for a database object</td></tr><tr><td><code class="literal"><code class="function">obj_description(<em class="parameter"><code>object_oid</code></em>)</code></code></td><td><code class="type">text</code></td><td>get comment for a database object (<span class="emphasis"><em>deprecated</em></span>)</td></tr><tr><td><code class="literal"><code class="function">shobj_description(<em class="parameter"><code>object_oid</code></em>, <em class="parameter"><code>catalog_name</code></em>)</code></code></td><td><code class="type">text</code></td><td>get comment for a shared database object</td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">col_description</code> returns the comment for a table
column, which is specified by the OID of its table and its column number.
(<code class="function">obj_description</code> cannot be used for table columns
since columns do not have OIDs of their own.)
</p><p>
The two-parameter form of <code class="function">obj_description</code> returns the
comment for a database object specified by its OID and the name of the
containing system catalog. For example,
<code class="literal">obj_description(123456,'pg_class')</code>
would retrieve the comment for the table with OID 123456.
The one-parameter form of <code class="function">obj_description</code> requires only
the object OID. It is deprecated since there is no guarantee that
OIDs are unique across different system catalogs; therefore, the wrong
comment might be returned.
</p><p>
<code class="function">shobj_description</code> is used just like
<code class="function">obj_description</code> except it is used for retrieving
comments on shared objects. Some system catalogs are global to all
databases within each cluster, and the descriptions for objects in them
are stored globally as well.
</p><a id="id-1.5.8.30.164" class="indexterm"></a><a id="id-1.5.8.30.165" class="indexterm"></a><a id="id-1.5.8.30.166" class="indexterm"></a><a id="id-1.5.8.30.167" class="indexterm"></a><a id="id-1.5.8.30.168" class="indexterm"></a><a id="id-1.5.8.30.169" class="indexterm"></a><a id="id-1.5.8.30.170" class="indexterm"></a><a id="id-1.5.8.30.171" class="indexterm"></a><p>
The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-TXID-SNAPSHOT" title="Table 9.69. Transaction IDs and Snapshots">Table 9.69</a>
provide server transaction information in an exportable form. The main
use of these functions is to determine which transactions were committed
between two snapshots.
</p><div class="table" id="FUNCTIONS-TXID-SNAPSHOT"><p class="title"><strong>Table 9.69. Transaction IDs and Snapshots</strong></p><div class="table-contents"><table class="table" summary="Transaction IDs and Snapshots" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal"><code class="function">txid_current()</code></code></td><td><code class="type">bigint</code></td><td>get current transaction ID, assigning a new one if the current transaction does not have one</td></tr><tr><td><code class="literal"><code class="function">txid_current_if_assigned()</code></code></td><td><code class="type">bigint</code></td><td>same as <code class="function">txid_current()</code> but returns null instead of assigning a new transaction ID if none is already assigned</td></tr><tr><td><code class="literal"><code class="function">txid_current_snapshot()</code></code></td><td><code class="type">txid_snapshot</code></td><td>get current snapshot</td></tr><tr><td><code class="literal"><code class="function">txid_snapshot_xip(<em class="parameter"><code>txid_snapshot</code></em>)</code></code></td><td><code class="type">setof bigint</code></td><td>get in-progress transaction IDs in snapshot</td></tr><tr><td><code class="literal"><code class="function">txid_snapshot_xmax(<em class="parameter"><code>txid_snapshot</code></em>)</code></code></td><td><code class="type">bigint</code></td><td>get <code class="literal">xmax</code> of snapshot</td></tr><tr><td><code class="literal"><code class="function">txid_snapshot_xmin(<em class="parameter"><code>txid_snapshot</code></em>)</code></code></td><td><code class="type">bigint</code></td><td>get <code class="literal">xmin</code> of snapshot</td></tr><tr><td><code class="literal"><code class="function">txid_visible_in_snapshot(<em class="parameter"><code>bigint</code></em>, <em class="parameter"><code>txid_snapshot</code></em>)</code></code></td><td><code class="type">boolean</code></td><td>is transaction ID visible in snapshot? (do not use with subtransaction ids)</td></tr><tr><td><code class="literal"><code class="function">txid_status(<em class="parameter"><code>bigint</code></em>)</code></code></td><td><code class="type">text</code></td><td>report the status of the given transaction: <code class="literal">committed</code>, <code class="literal">aborted</code>, <code class="literal">in progress</code>, or null if the transaction ID is too old</td></tr></tbody></table></div></div><br class="table-break" /><p>
The internal transaction ID type (<code class="type">xid</code>) is 32 bits wide and
wraps around every 4 billion transactions. However, these functions
export a 64-bit format that is extended with an <span class="quote">“<span class="quote">epoch</span>”</span> counter
so it will not wrap around during the life of an installation.
The data type used by these functions, <code class="type">txid_snapshot</code>,
stores information about transaction ID
visibility at a particular moment in time. Its components are
described in <a class="xref" href="functions-info.html#FUNCTIONS-TXID-SNAPSHOT-PARTS" title="Table 9.70. Snapshot Components">Table 9.70</a>.
</p><div class="table" id="FUNCTIONS-TXID-SNAPSHOT-PARTS"><p class="title"><strong>Table 9.70. Snapshot Components</strong></p><div class="table-contents"><table class="table" summary="Snapshot Components" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Name</th><th>Description</th></tr></thead><tbody><tr><td><code class="type">xmin</code></td><td>
Earliest transaction ID (txid) that is still active. All earlier
transactions will either be committed and visible, or rolled
back and dead.
</td></tr><tr><td><code class="type">xmax</code></td><td>
First as-yet-unassigned txid. All txids greater than or equal to this
are not yet started as of the time of the snapshot, and thus invisible.
</td></tr><tr><td><code class="type">xip_list</code></td><td>
Active txids at the time of the snapshot. The list
includes only those active txids between <code class="literal">xmin</code>
and <code class="literal">xmax</code>; there might be active txids higher
than <code class="literal">xmax</code>. A txid that is <code class="literal">xmin <= txid <
xmax</code> and not in this list was already completed
at the time of the snapshot, and thus either visible or
dead according to its commit status. The list does not
include txids of subtransactions.
</td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="type">txid_snapshot</code>'s textual representation is
<code class="literal"><em class="replaceable"><code>xmin</code></em>:<em class="replaceable"><code>xmax</code></em>:<em class="replaceable"><code>xip_list</code></em></code>.
For example <code class="literal">10:20:10,14,15</code> means
<code class="literal">xmin=10, xmax=20, xip_list=10, 14, 15</code>.
</p><p>
<code class="function">txid_status(bigint)</code> reports the commit status of a recent
transaction. Applications may use it to determine whether a transaction
committed or aborted when the application and database server become
disconnected while a <code class="literal">COMMIT</code> is in progress.
The status of a transaction will be reported as either
<code class="literal">in progress</code>,
<code class="literal">committed</code>, or <code class="literal">aborted</code>, provided that the
transaction is recent enough that the system retains the commit status
of that transaction. If is old enough that no references to that
transaction survive in the system and the commit status information has
been discarded, this function will return NULL. Note that prepared
transactions are reported as <code class="literal">in progress</code>; applications must
check <a class="link" href="view-pg-prepared-xacts.html" title="52.77. pg_prepared_xacts"><code class="literal">pg_prepared_xacts</code></a> if they
need to determine whether the txid is a prepared transaction.
</p><p>
The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-COMMIT-TIMESTAMP" title="Table 9.71. Committed transaction information">Table 9.71</a>
provide information about transactions that have been already committed.
These functions mainly provide information about when the transactions
were committed. They only provide useful data when
<a class="xref" href="runtime-config-replication.html#GUC-TRACK-COMMIT-TIMESTAMP">track_commit_timestamp</a> configuration option is enabled
and only for transactions that were committed after it was enabled.
</p><div class="table" id="FUNCTIONS-COMMIT-TIMESTAMP"><p class="title"><strong>Table 9.71. Committed transaction information</strong></p><div class="table-contents"><table class="table" summary="Committed transaction information" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td>
<a id="id-1.5.8.30.179.2.2.1.1.1" class="indexterm"></a>
<code class="literal"><code class="function">pg_xact_commit_timestamp(<em class="parameter"><code>xid</code></em>)</code></code>
</td><td><code class="type">timestamp with time zone</code></td><td>get commit timestamp of a transaction</td></tr><tr><td>
<a id="id-1.5.8.30.179.2.2.2.1.1" class="indexterm"></a>
<code class="literal"><code class="function">pg_last_committed_xact()</code></code>
</td><td><em class="parameter"><code>xid</code></em> <code class="type">xid</code>, <em class="parameter"><code>timestamp</code></em> <code class="type">timestamp with time zone</code></td><td>get transaction ID and commit timestamp of latest committed transaction</td></tr></tbody></table></div></div><br class="table-break" /><p>
The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-CONTROLDATA" title="Table 9.72. Control Data Functions">Table 9.72</a>
print information initialized during <code class="command">initdb</code>, such
as the catalog version. They also show information about write-ahead
logging and checkpoint processing. This information is cluster-wide,
and not specific to any one database. They provide most of the same
information, from the same source, as
<a class="xref" href="app-pgcontroldata.html" title="pg_controldata"><span class="refentrytitle"><span class="application">pg_controldata</span></span></a>, although in a form better suited
to <acronym class="acronym">SQL</acronym> functions.
</p><div class="table" id="FUNCTIONS-CONTROLDATA"><p class="title"><strong>Table 9.72. Control Data Functions</strong></p><div class="table-contents"><table class="table" summary="Control Data Functions" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Return Type</th><th>Description</th></tr></thead><tbody><tr><td>
<a id="id-1.5.8.30.181.2.2.1.1.1" class="indexterm"></a>
<code class="literal"><code class="function">pg_control_checkpoint()</code></code>
</td><td><code class="type">record</code></td><td>
Returns information about current checkpoint state.
</td></tr><tr><td>
<a id="id-1.5.8.30.181.2.2.2.1.1" class="indexterm"></a>
<code class="literal"><code class="function">pg_control_system()</code></code>
</td><td><code class="type">record</code></td><td>
Returns information about current control file state.
</td></tr><tr><td>
<a id="id-1.5.8.30.181.2.2.3.1.1" class="indexterm"></a>
<code class="literal"><code class="function">pg_control_init()</code></code>
</td><td><code class="type">record</code></td><td>
Returns information about cluster initialization state.
</td></tr><tr><td>
<a id="id-1.5.8.30.181.2.2.4.1.1" class="indexterm"></a>
<code class="literal"><code class="function">pg_control_recovery()</code></code>
</td><td><code class="type">record</code></td><td>
Returns information about recovery state.
</td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">pg_control_checkpoint</code> returns a record, shown in
<a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-CHECKPOINT" title="Table 9.73. pg_control_checkpoint Columns">Table 9.73</a>
</p><div class="table" id="FUNCTIONS-PG-CONTROL-CHECKPOINT"><p class="title"><strong>Table 9.73. <code class="function">pg_control_checkpoint</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_checkpoint Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="literal">checkpoint_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="literal">redo_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="literal">redo_wal_file</code></td><td><code class="type">text</code></td></tr><tr><td><code class="literal">timeline_id</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">prev_timeline_id</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">full_page_writes</code></td><td><code class="type">boolean</code></td></tr><tr><td><code class="literal">next_xid</code></td><td><code class="type">text</code></td></tr><tr><td><code class="literal">next_oid</code></td><td><code class="type">oid</code></td></tr><tr><td><code class="literal">next_multixact_id</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">next_multi_offset</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">oldest_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">oldest_xid_dbid</code></td><td><code class="type">oid</code></td></tr><tr><td><code class="literal">oldest_active_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">oldest_multi_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">oldest_multi_dbid</code></td><td><code class="type">oid</code></td></tr><tr><td><code class="literal">oldest_commit_ts_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">newest_commit_ts_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="literal">checkpoint_time</code></td><td><code class="type">timestamp with time zone</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">pg_control_system</code> returns a record, shown in
<a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-SYSTEM" title="Table 9.74. pg_control_system Columns">Table 9.74</a>
</p><div class="table" id="FUNCTIONS-PG-CONTROL-SYSTEM"><p class="title"><strong>Table 9.74. <code class="function">pg_control_system</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_system Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="literal">pg_control_version</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">catalog_version_no</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">system_identifier</code></td><td><code class="type">bigint</code></td></tr><tr><td><code class="literal">pg_control_last_modified</code></td><td><code class="type">timestamp with time zone</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">pg_control_init</code> returns a record, shown in
<a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-INIT" title="Table 9.75. pg_control_init Columns">Table 9.75</a>
</p><div class="table" id="FUNCTIONS-PG-CONTROL-INIT"><p class="title"><strong>Table 9.75. <code class="function">pg_control_init</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_init Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="literal">max_data_alignment</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">database_block_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">blocks_per_segment</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">wal_block_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">bytes_per_wal_segment</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">max_identifier_length</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">max_index_columns</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">max_toast_chunk_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">large_object_chunk_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">float4_pass_by_value</code></td><td><code class="type">boolean</code></td></tr><tr><td><code class="literal">float8_pass_by_value</code></td><td><code class="type">boolean</code></td></tr><tr><td><code class="literal">data_page_checksum_version</code></td><td><code class="type">integer</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
<code class="function">pg_control_recovery</code> returns a record, shown in
<a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-RECOVERY" title="Table 9.76. pg_control_recovery Columns">Table 9.76</a>
</p><div class="table" id="FUNCTIONS-PG-CONTROL-RECOVERY"><p class="title"><strong>Table 9.76. <code class="function">pg_control_recovery</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_recovery Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="literal">min_recovery_end_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="literal">min_recovery_end_timeline</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="literal">backup_start_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="literal">backup_end_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="literal">end_of_backup_record_required</code></td><td><code class="type">boolean</code></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-srf.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-admin.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.24. Set Returning Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.26. System Administration Functions</td></tr></table></div></body></html>
