1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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.27. System Information Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-srf.html" title="9.26. Set Returning Functions" /><link rel="next" href="functions-admin.html" title="9.28. System Administration Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.27. System Information Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-srf.html" title="9.26. 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-admin.html" title="9.28. System Administration Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-INFO"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.27. System Information Functions and Operators <a href="#FUNCTIONS-INFO" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-SESSION">9.27.1. Session Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-ACCESS">9.27.2. Access Privilege Inquiry Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-SCHEMA">9.27.3. Schema Visibility Inquiry Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-CATALOG">9.27.4. System Catalog Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-OBJECT">9.27.5. Object Information and Addressing Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-COMMENT">9.27.6. Comment Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-VALIDITY">9.27.7. Data Validity Checking Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-SNAPSHOT">9.27.8. Transaction ID and Snapshot Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-COMMIT-TIMESTAMP">9.27.9. Committed Transaction Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-CONTROLDATA">9.27.10. Control Data Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-VERSION">9.27.11. Version Information Functions</a></span></dt><dt><span class="sect2"><a href="functions-info.html#FUNCTIONS-INFO-WAL-SUMMARY">9.27.12. WAL Summarization Information Functions</a></span></dt></dl></div><p>
3 The functions described in this section are used to obtain various
4 information about a <span class="productname">PostgreSQL</span> installation.
5 </p><div class="sect2" id="FUNCTIONS-INFO-SESSION"><div class="titlepage"><div><div><h3 class="title">9.27.1. Session Information Functions <a href="#FUNCTIONS-INFO-SESSION" class="id_link">#</a></h3></div></div></div><p>
6 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-SESSION-TABLE" title="Table 9.71. Session Information Functions">Table 9.71</a> shows several
7 functions that extract session and system information.
9 In addition to the functions listed in this section, there are a number of
10 functions related to the statistics system that also provide system
11 information. See <a class="xref" href="monitoring-stats.html#MONITORING-STATS-FUNCTIONS" title="27.2.26. Statistics Functions">Section 27.2.26</a> for more
13 </p><div class="table" id="FUNCTIONS-INFO-SESSION-TABLE"><p class="title"><strong>Table 9.71. Session Information Functions</strong></p><div class="table-contents"><table class="table" summary="Session Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
18 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
19 <a id="id-1.5.8.33.3.4.2.2.1.1.1.1" class="indexterm"></a>
20 <code class="function">current_catalog</code>
21 → <code class="returnvalue">name</code>
23 <p class="func_signature">
24 <a id="id-1.5.8.33.3.4.2.2.1.1.2.1" class="indexterm"></a>
25 <code class="function">current_database</code> ()
26 → <code class="returnvalue">name</code>
29 Returns the name of the current database. (Databases are
30 called <span class="quote">“<span class="quote">catalogs</span>”</span> in the SQL standard,
31 so <code class="function">current_catalog</code> is the standard's
33 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
34 <a id="id-1.5.8.33.3.4.2.2.2.1.1.1" class="indexterm"></a>
35 <code class="function">current_query</code> ()
36 → <code class="returnvalue">text</code>
39 Returns the text of the currently executing query, as submitted
40 by the client (which might contain more than one statement).
41 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
42 <a id="id-1.5.8.33.3.4.2.2.3.1.1.1" class="indexterm"></a>
43 <code class="function">current_role</code>
44 → <code class="returnvalue">name</code>
47 This is equivalent to <code class="function">current_user</code>.
48 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
49 <a id="id-1.5.8.33.3.4.2.2.4.1.1.1" class="indexterm"></a>
50 <a id="id-1.5.8.33.3.4.2.2.4.1.1.2" class="indexterm"></a>
51 <code class="function">current_schema</code>
52 → <code class="returnvalue">name</code>
54 <p class="func_signature">
55 <code class="function">current_schema</code> ()
56 → <code class="returnvalue">name</code>
59 Returns the name of the schema that is first in the search path (or a
60 null value if the search path is empty). This is the schema that will
61 be used for any tables or other named objects that are created without
62 specifying a target schema.
63 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
64 <a id="id-1.5.8.33.3.4.2.2.5.1.1.1" class="indexterm"></a>
65 <a id="id-1.5.8.33.3.4.2.2.5.1.1.2" class="indexterm"></a>
66 <code class="function">current_schemas</code> ( <em class="parameter"><code>include_implicit</code></em> <code class="type">boolean</code> )
67 → <code class="returnvalue">name[]</code>
70 Returns an array of the names of all schemas presently in the
71 effective search path, in their priority order. (Items in the current
72 <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> setting that do not correspond to
73 existing, searchable schemas are omitted.) If the Boolean argument
74 is <code class="literal">true</code>, then implicitly-searched system schemas
75 such as <code class="literal">pg_catalog</code> are included in the result.
76 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
77 <a id="id-1.5.8.33.3.4.2.2.6.1.1.1" class="indexterm"></a>
78 <a id="id-1.5.8.33.3.4.2.2.6.1.1.2" class="indexterm"></a>
79 <code class="function">current_user</code>
80 → <code class="returnvalue">name</code>
83 Returns the user name of the current execution context.
84 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
85 <a id="id-1.5.8.33.3.4.2.2.7.1.1.1" class="indexterm"></a>
86 <code class="function">inet_client_addr</code> ()
87 → <code class="returnvalue">inet</code>
90 Returns the IP address of the current client,
91 or <code class="literal">NULL</code> if the current connection is via a
93 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
94 <a id="id-1.5.8.33.3.4.2.2.8.1.1.1" class="indexterm"></a>
95 <code class="function">inet_client_port</code> ()
96 → <code class="returnvalue">integer</code>
99 Returns the IP port number of the current client,
100 or <code class="literal">NULL</code> if the current connection is via a
102 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
103 <a id="id-1.5.8.33.3.4.2.2.9.1.1.1" class="indexterm"></a>
104 <code class="function">inet_server_addr</code> ()
105 → <code class="returnvalue">inet</code>
108 Returns the IP address on which the server accepted the current
110 or <code class="literal">NULL</code> if the current connection is via a
112 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
113 <a id="id-1.5.8.33.3.4.2.2.10.1.1.1" class="indexterm"></a>
114 <code class="function">inet_server_port</code> ()
115 → <code class="returnvalue">integer</code>
118 Returns the IP port number on which the server accepted the current
120 or <code class="literal">NULL</code> if the current connection is via a
122 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
123 <a id="id-1.5.8.33.3.4.2.2.11.1.1.1" class="indexterm"></a>
124 <code class="function">pg_backend_pid</code> ()
125 → <code class="returnvalue">integer</code>
128 Returns the process ID of the server process attached to the current
130 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
131 <a id="id-1.5.8.33.3.4.2.2.12.1.1.1" class="indexterm"></a>
132 <code class="function">pg_blocking_pids</code> ( <code class="type">integer</code> )
133 → <code class="returnvalue">integer[]</code>
136 Returns an array of the process ID(s) of the sessions that are
137 blocking the server process with the specified process ID from
138 acquiring a lock, or an empty array if there is no such server process
139 or it is not blocked.
142 One server process blocks another if it either holds a lock that
143 conflicts with the blocked process's lock request (hard block), or is
144 waiting for a lock that would conflict with the blocked process's lock
145 request and is ahead of it in the wait queue (soft block). When using
146 parallel queries the result always lists client-visible process IDs
147 (that is, <code class="function">pg_backend_pid</code> results) even if the
148 actual lock is held or awaited by a child worker process. As a result
149 of that, there may be duplicated PIDs in the result. Also note that
150 when a prepared transaction holds a conflicting lock, it will be
151 represented by a zero process ID.
154 Frequent calls to this function could have some impact on database
155 performance, because it needs exclusive access to the lock manager's
156 shared state for a short time.
157 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
158 <a id="id-1.5.8.33.3.4.2.2.13.1.1.1" class="indexterm"></a>
159 <code class="function">pg_conf_load_time</code> ()
160 → <code class="returnvalue">timestamp with time zone</code>
163 Returns the time when the server configuration files were last loaded.
164 If the current session was alive at the time, this will be the time
165 when the session itself re-read the configuration files (so the
166 reading will vary a little in different sessions). Otherwise it is
167 the time when the postmaster process re-read the configuration files.
168 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
169 <a id="id-1.5.8.33.3.4.2.2.14.1.1.1" class="indexterm"></a>
170 <a id="id-1.5.8.33.3.4.2.2.14.1.1.2" class="indexterm"></a>
171 <a id="id-1.5.8.33.3.4.2.2.14.1.1.3" class="indexterm"></a>
172 <a id="id-1.5.8.33.3.4.2.2.14.1.1.4" class="indexterm"></a>
173 <code class="function">pg_current_logfile</code> ( [<span class="optional"> <code class="type">text</code> </span>] )
174 → <code class="returnvalue">text</code>
177 Returns the path name of the log file currently in use by the logging
178 collector. The path includes the <a class="xref" href="runtime-config-logging.html#GUC-LOG-DIRECTORY">log_directory</a>
179 directory and the individual log file name. The result
180 is <code class="literal">NULL</code> if the logging collector is disabled.
181 When multiple log files exist, each in a different
182 format, <code class="function">pg_current_logfile</code> without an argument
183 returns the path of the file having the first format found in the
184 ordered list: <code class="literal">stderr</code>,
185 <code class="literal">csvlog</code>, <code class="literal">jsonlog</code>.
186 <code class="literal">NULL</code> is returned if no log file has any of these
188 To request information about a specific log file format, supply
189 either <code class="literal">csvlog</code>, <code class="literal">jsonlog</code> or
190 <code class="literal">stderr</code> as the
191 value of the optional parameter. The result is <code class="literal">NULL</code>
192 if the log format requested is not configured in
193 <a class="xref" href="runtime-config-logging.html#GUC-LOG-DESTINATION">log_destination</a>.
194 The result reflects the contents of
195 the <code class="filename">current_logfiles</code> file.
198 This function is restricted to superusers and roles with privileges of
199 the <code class="literal">pg_monitor</code> role by default, but other users can
200 be granted EXECUTE to run the function.
201 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
202 <a id="id-1.5.8.33.3.4.2.2.15.1.1.1" class="indexterm"></a>
203 <code class="function">pg_get_loaded_modules</code> ()
204 → <code class="returnvalue">setof record</code>
205 ( <em class="parameter"><code>module_name</code></em> <code class="type">text</code>,
206 <em class="parameter"><code>version</code></em> <code class="type">text</code>,
207 <em class="parameter"><code>file_name</code></em> <code class="type">text</code> )
210 Returns a list of the loadable modules that are loaded into the
211 current server session. The <em class="parameter"><code>module_name</code></em>
212 and <em class="parameter"><code>version</code></em> fields are NULL unless the
213 module author supplied values for them using
214 the <code class="literal">PG_MODULE_MAGIC_EXT</code> macro.
215 The <em class="parameter"><code>file_name</code></em> field gives the file
216 name of the module (shared library).
217 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
218 <a id="id-1.5.8.33.3.4.2.2.16.1.1.1" class="indexterm"></a>
219 <code class="function">pg_my_temp_schema</code> ()
220 → <code class="returnvalue">oid</code>
223 Returns the OID of the current session's temporary schema, or zero if
224 it has none (because it has not created any temporary tables).
225 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
226 <a id="id-1.5.8.33.3.4.2.2.17.1.1.1" class="indexterm"></a>
227 <code class="function">pg_is_other_temp_schema</code> ( <code class="type">oid</code> )
228 → <code class="returnvalue">boolean</code>
231 Returns true if the given OID is the OID of another session's
232 temporary schema. (This can be useful, for example, to exclude other
233 sessions' temporary tables from a catalog display.)
234 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
235 <a id="id-1.5.8.33.3.4.2.2.18.1.1.1" class="indexterm"></a>
236 <code class="function">pg_jit_available</code> ()
237 → <code class="returnvalue">boolean</code>
240 Returns true if a <acronym class="acronym">JIT</acronym> compiler extension is
241 available (see <a class="xref" href="jit.html" title="Chapter 30. Just-in-Time Compilation (JIT)">Chapter 30</a>) and the
242 <a class="xref" href="runtime-config-query.html#GUC-JIT">jit</a> configuration parameter is set to
243 <code class="literal">on</code>.
244 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
245 <a id="id-1.5.8.33.3.4.2.2.19.1.1.1" class="indexterm"></a>
246 <code class="function">pg_numa_available</code> ()
247 → <code class="returnvalue">boolean</code>
250 Returns true if the server has been compiled with <acronym class="acronym">NUMA</acronym> support.
251 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
252 <a id="id-1.5.8.33.3.4.2.2.20.1.1.1" class="indexterm"></a>
253 <code class="function">pg_listening_channels</code> ()
254 → <code class="returnvalue">setof text</code>
257 Returns the set of names of asynchronous notification channels that
258 the current session is listening to.
259 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
260 <a id="id-1.5.8.33.3.4.2.2.21.1.1.1" class="indexterm"></a>
261 <code class="function">pg_notification_queue_usage</code> ()
262 → <code class="returnvalue">double precision</code>
265 Returns the fraction (0–1) of the asynchronous notification
266 queue's maximum size that is currently occupied by notifications that
267 are waiting to be processed.
268 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>
269 for more information.
270 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
271 <a id="id-1.5.8.33.3.4.2.2.22.1.1.1" class="indexterm"></a>
272 <code class="function">pg_postmaster_start_time</code> ()
273 → <code class="returnvalue">timestamp with time zone</code>
276 Returns the time when the server started.
277 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
278 <a id="id-1.5.8.33.3.4.2.2.23.1.1.1" class="indexterm"></a>
279 <code class="function">pg_safe_snapshot_blocking_pids</code> ( <code class="type">integer</code> )
280 → <code class="returnvalue">integer[]</code>
283 Returns an array of the process ID(s) of the sessions that are blocking
284 the server process with the specified process ID from acquiring a safe
285 snapshot, or an empty array if there is no such server process or it
289 A session running a <code class="literal">SERIALIZABLE</code> transaction blocks
290 a <code class="literal">SERIALIZABLE READ ONLY DEFERRABLE</code> transaction
291 from acquiring a snapshot until the latter determines that it is safe
292 to avoid taking any predicate locks. See
293 <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
294 serializable and deferrable transactions.
297 Frequent calls to this function could have some impact on database
298 performance, because it needs access to the predicate lock manager's
299 shared state for a short time.
300 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
301 <a id="id-1.5.8.33.3.4.2.2.24.1.1.1" class="indexterm"></a>
302 <code class="function">pg_trigger_depth</code> ()
303 → <code class="returnvalue">integer</code>
306 Returns the current nesting level
307 of <span class="productname">PostgreSQL</span> triggers (0 if not called,
308 directly or indirectly, from inside a trigger).
309 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
310 <a id="id-1.5.8.33.3.4.2.2.25.1.1.1" class="indexterm"></a>
311 <code class="function">session_user</code>
312 → <code class="returnvalue">name</code>
315 Returns the session user's name.
316 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
317 <a id="id-1.5.8.33.3.4.2.2.26.1.1.1" class="indexterm"></a>
318 <code class="function">system_user</code>
319 → <code class="returnvalue">text</code>
322 Returns the authentication method and the identity (if any) that the
323 user presented during the authentication cycle before they were
324 assigned a database role. It is represented as
325 <code class="literal">auth_method:identity</code> or
326 <code class="literal">NULL</code> if the user has not been authenticated (for
327 example if <a class="link" href="auth-trust.html" title="20.4. Trust Authentication">Trust authentication</a> has
329 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
330 <a id="id-1.5.8.33.3.4.2.2.27.1.1.1" class="indexterm"></a>
331 <code class="function">user</code>
332 → <code class="returnvalue">name</code>
335 This is equivalent to <code class="function">current_user</code>.
336 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
337 <code class="function">current_catalog</code>,
338 <code class="function">current_role</code>,
339 <code class="function">current_schema</code>,
340 <code class="function">current_user</code>,
341 <code class="function">session_user</code>,
342 and <code class="function">user</code> have special syntactic status
343 in <acronym class="acronym">SQL</acronym>: they must be called without trailing
344 parentheses. In PostgreSQL, parentheses can optionally be used with
345 <code class="function">current_schema</code>, but not with the others.
347 The <code class="function">session_user</code> is normally the user who initiated
348 the current database connection; but superusers can change this setting
349 with <a class="xref" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION"><span class="refentrytitle">SET SESSION AUTHORIZATION</span></a>.
350 The <code class="function">current_user</code> is the user identifier
351 that is applicable for permission checking. Normally it is equal
352 to the session user, but it can be changed with
353 <a class="xref" href="sql-set-role.html" title="SET ROLE"><span class="refentrytitle">SET ROLE</span></a>.
354 It also changes during the execution of
355 functions with the attribute <code class="literal">SECURITY DEFINER</code>.
356 In Unix parlance, the session user is the <span class="quote">“<span class="quote">real user</span>”</span> and
357 the current user is the <span class="quote">“<span class="quote">effective user</span>”</span>.
358 <code class="function">current_role</code> and <code class="function">user</code> are
359 synonyms for <code class="function">current_user</code>. (The SQL standard draws
360 a distinction between <code class="function">current_role</code>
361 and <code class="function">current_user</code>, but <span class="productname">PostgreSQL</span>
362 does not, since it unifies users and roles into a single kind of entity.)
363 </p></div><div class="sect2" id="FUNCTIONS-INFO-ACCESS"><div class="titlepage"><div><div><h3 class="title">9.27.2. Access Privilege Inquiry Functions <a href="#FUNCTIONS-INFO-ACCESS" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.33.4.2" class="indexterm"></a><p>
364 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE" title="Table 9.72. Access Privilege Inquiry Functions">Table 9.72</a> lists functions that
365 allow querying object access privileges programmatically.
366 (See <a class="xref" href="ddl-priv.html" title="5.8. Privileges">Section 5.8</a> for more information about
368 In these functions, the user whose privileges are being inquired about
369 can be specified by name or by OID
370 (<code class="structname">pg_authid</code>.<code class="structfield">oid</code>), or if
371 the name is given as <code class="literal">public</code> then the privileges of the
372 PUBLIC pseudo-role are checked. Also, the <em class="parameter"><code>user</code></em>
373 argument can be omitted entirely, in which case
374 the <code class="function">current_user</code> is assumed.
375 The object that is being inquired about can be specified either by name or
376 by OID, too. When specifying by name, a schema name can be included if
378 The access privilege of interest is specified by a text string, which must
379 evaluate to one of the appropriate privilege keywords for the object's type
380 (e.g., <code class="literal">SELECT</code>). Optionally, <code class="literal">WITH GRANT
381 OPTION</code> can be added to a privilege type to test whether the
382 privilege is held with grant option. Also, multiple privilege types can be
383 listed separated by commas, in which case the result will be true if any of
384 the listed privileges is held. (Case of the privilege string is not
385 significant, and extra whitespace is allowed between but not within
388 </p><pre class="programlisting">
389 SELECT has_table_privilege('myschema.mytable', 'select');
390 SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION');
392 </p><div class="table" id="FUNCTIONS-INFO-ACCESS-TABLE"><p class="title"><strong>Table 9.72. Access Privilege Inquiry Functions</strong></p><div class="table-contents"><table class="table" summary="Access Privilege Inquiry Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
397 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
398 <a id="id-1.5.8.33.4.4.2.2.1.1.1.1" class="indexterm"></a>
399 <code class="function">has_any_column_privilege</code> (
400 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
401 <em class="parameter"><code>table</code></em> <code class="type">text</code> or <code class="type">oid</code>,
402 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
403 → <code class="returnvalue">boolean</code>
406 Does user have privilege for any column of table?
407 This succeeds either if the privilege is held for the whole table, or
408 if there is a column-level grant of the privilege for at least one
410 Allowable privilege types are
411 <code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
412 <code class="literal">UPDATE</code>, and <code class="literal">REFERENCES</code>.
413 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
414 <a id="id-1.5.8.33.4.4.2.2.2.1.1.1" class="indexterm"></a>
415 <code class="function">has_column_privilege</code> (
416 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
417 <em class="parameter"><code>table</code></em> <code class="type">text</code> or <code class="type">oid</code>,
418 <em class="parameter"><code>column</code></em> <code class="type">text</code> or <code class="type">smallint</code>,
419 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
420 → <code class="returnvalue">boolean</code>
423 Does user have privilege for the specified table column?
424 This succeeds either if the privilege is held for the whole table, or
425 if there is a column-level grant of the privilege for the column.
426 The column can be specified by name or by attribute number
427 (<code class="structname">pg_attribute</code>.<code class="structfield">attnum</code>).
428 Allowable privilege types are
429 <code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
430 <code class="literal">UPDATE</code>, and <code class="literal">REFERENCES</code>.
431 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
432 <a id="id-1.5.8.33.4.4.2.2.3.1.1.1" class="indexterm"></a>
433 <code class="function">has_database_privilege</code> (
434 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
435 <em class="parameter"><code>database</code></em> <code class="type">text</code> or <code class="type">oid</code>,
436 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
437 → <code class="returnvalue">boolean</code>
440 Does user have privilege for database?
441 Allowable privilege types are
442 <code class="literal">CREATE</code>,
443 <code class="literal">CONNECT</code>,
444 <code class="literal">TEMPORARY</code>, and
445 <code class="literal">TEMP</code> (which is equivalent to
446 <code class="literal">TEMPORARY</code>).
447 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
448 <a id="id-1.5.8.33.4.4.2.2.4.1.1.1" class="indexterm"></a>
449 <code class="function">has_foreign_data_wrapper_privilege</code> (
450 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
451 <em class="parameter"><code>fdw</code></em> <code class="type">text</code> or <code class="type">oid</code>,
452 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
453 → <code class="returnvalue">boolean</code>
456 Does user have privilege for foreign-data wrapper?
457 The only allowable privilege type is <code class="literal">USAGE</code>.
458 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
459 <a id="id-1.5.8.33.4.4.2.2.5.1.1.1" class="indexterm"></a>
460 <code class="function">has_function_privilege</code> (
461 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
462 <em class="parameter"><code>function</code></em> <code class="type">text</code> or <code class="type">oid</code>,
463 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
464 → <code class="returnvalue">boolean</code>
467 Does user have privilege for function?
468 The only allowable privilege type is <code class="literal">EXECUTE</code>.
471 When specifying a function by name rather than by OID, the allowed
472 input is the same as for the <code class="type">regprocedure</code> data type (see
473 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>).
475 </p><pre class="programlisting">
476 SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
478 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
479 <a id="id-1.5.8.33.4.4.2.2.6.1.1.1" class="indexterm"></a>
480 <code class="function">has_language_privilege</code> (
481 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
482 <em class="parameter"><code>language</code></em> <code class="type">text</code> or <code class="type">oid</code>,
483 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
484 → <code class="returnvalue">boolean</code>
487 Does user have privilege for language?
488 The only allowable privilege type is <code class="literal">USAGE</code>.
489 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
490 <a id="id-1.5.8.33.4.4.2.2.7.1.1.1" class="indexterm"></a>
491 <code class="function">has_largeobject_privilege</code> (
492 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
493 <em class="parameter"><code>largeobject</code></em> <code class="type">oid</code>,
494 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
495 → <code class="returnvalue">boolean</code>
498 Does user have privilege for large object?
499 Allowable privilege types are
500 <code class="literal">SELECT</code> and <code class="literal">UPDATE</code>.
501 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
502 <a id="id-1.5.8.33.4.4.2.2.8.1.1.1" class="indexterm"></a>
503 <code class="function">has_parameter_privilege</code> (
504 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
505 <em class="parameter"><code>parameter</code></em> <code class="type">text</code>,
506 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
507 → <code class="returnvalue">boolean</code>
510 Does user have privilege for configuration parameter?
511 The parameter name is case-insensitive.
512 Allowable privilege types are <code class="literal">SET</code>
513 and <code class="literal">ALTER SYSTEM</code>.
514 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
515 <a id="id-1.5.8.33.4.4.2.2.9.1.1.1" class="indexterm"></a>
516 <code class="function">has_schema_privilege</code> (
517 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
518 <em class="parameter"><code>schema</code></em> <code class="type">text</code> or <code class="type">oid</code>,
519 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
520 → <code class="returnvalue">boolean</code>
523 Does user have privilege for schema?
524 Allowable privilege types are
525 <code class="literal">CREATE</code> and
526 <code class="literal">USAGE</code>.
527 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
528 <a id="id-1.5.8.33.4.4.2.2.10.1.1.1" class="indexterm"></a>
529 <code class="function">has_sequence_privilege</code> (
530 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
531 <em class="parameter"><code>sequence</code></em> <code class="type">text</code> or <code class="type">oid</code>,
532 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
533 → <code class="returnvalue">boolean</code>
536 Does user have privilege for sequence?
537 Allowable privilege types are
538 <code class="literal">USAGE</code>,
539 <code class="literal">SELECT</code>, and
540 <code class="literal">UPDATE</code>.
541 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
542 <a id="id-1.5.8.33.4.4.2.2.11.1.1.1" class="indexterm"></a>
543 <code class="function">has_server_privilege</code> (
544 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
545 <em class="parameter"><code>server</code></em> <code class="type">text</code> or <code class="type">oid</code>,
546 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
547 → <code class="returnvalue">boolean</code>
550 Does user have privilege for foreign server?
551 The only allowable privilege type is <code class="literal">USAGE</code>.
552 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
553 <a id="id-1.5.8.33.4.4.2.2.12.1.1.1" class="indexterm"></a>
554 <code class="function">has_table_privilege</code> (
555 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
556 <em class="parameter"><code>table</code></em> <code class="type">text</code> or <code class="type">oid</code>,
557 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
558 → <code class="returnvalue">boolean</code>
561 Does user have privilege for table?
562 Allowable privilege types
563 are <code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
564 <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>,
565 <code class="literal">TRUNCATE</code>, <code class="literal">REFERENCES</code>,
566 <code class="literal">TRIGGER</code>, and <code class="literal">MAINTAIN</code>.
567 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
568 <a id="id-1.5.8.33.4.4.2.2.13.1.1.1" class="indexterm"></a>
569 <code class="function">has_tablespace_privilege</code> (
570 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
571 <em class="parameter"><code>tablespace</code></em> <code class="type">text</code> or <code class="type">oid</code>,
572 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
573 → <code class="returnvalue">boolean</code>
576 Does user have privilege for tablespace?
577 The only allowable privilege type is <code class="literal">CREATE</code>.
578 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
579 <a id="id-1.5.8.33.4.4.2.2.14.1.1.1" class="indexterm"></a>
580 <code class="function">has_type_privilege</code> (
581 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
582 <em class="parameter"><code>type</code></em> <code class="type">text</code> or <code class="type">oid</code>,
583 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
584 → <code class="returnvalue">boolean</code>
587 Does user have privilege for data type?
588 The only allowable privilege type is <code class="literal">USAGE</code>.
589 When specifying a type by name rather than by OID, the allowed input
590 is the same as for the <code class="type">regtype</code> data type (see
591 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>).
592 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
593 <a id="id-1.5.8.33.4.4.2.2.15.1.1.1" class="indexterm"></a>
594 <code class="function">pg_has_role</code> (
595 [<span class="optional"> <em class="parameter"><code>user</code></em> <code class="type">name</code> or <code class="type">oid</code>, </span>]
596 <em class="parameter"><code>role</code></em> <code class="type">text</code> or <code class="type">oid</code>,
597 <em class="parameter"><code>privilege</code></em> <code class="type">text</code> )
598 → <code class="returnvalue">boolean</code>
601 Does user have privilege for role?
602 Allowable privilege types are
603 <code class="literal">MEMBER</code>, <code class="literal">USAGE</code>,
604 and <code class="literal">SET</code>.
605 <code class="literal">MEMBER</code> denotes direct or indirect membership in
606 the role without regard to what specific privileges may be conferred.
607 <code class="literal">USAGE</code> denotes whether the privileges of the role
608 are immediately available without doing <code class="command">SET ROLE</code>,
609 while <code class="literal">SET</code> denotes whether it is possible to change
610 to the role using the <code class="literal">SET ROLE</code> command.
611 <code class="literal">WITH ADMIN OPTION</code> or <code class="literal">WITH GRANT
612 OPTION</code> can be added to any of these privilege types to
613 test whether the <code class="literal">ADMIN</code> privilege is held (all
614 six spellings test the same thing).
615 This function does not allow the special case of
616 setting <em class="parameter"><code>user</code></em> to <code class="literal">public</code>,
617 because the PUBLIC pseudo-role can never be a member of real roles.
618 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
619 <a id="id-1.5.8.33.4.4.2.2.16.1.1.1" class="indexterm"></a>
620 <code class="function">row_security_active</code> (
621 <em class="parameter"><code>table</code></em> <code class="type">text</code> or <code class="type">oid</code> )
622 → <code class="returnvalue">boolean</code>
625 Is row-level security active for the specified table in the context of
626 the current user and current environment?
627 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
628 <a class="xref" href="functions-info.html#FUNCTIONS-ACLITEM-OP-TABLE" title="Table 9.73. aclitem Operators">Table 9.73</a> shows the operators
629 available for the <code class="type">aclitem</code> type, which is the catalog
630 representation of access privileges. See <a class="xref" href="ddl-priv.html" title="5.8. Privileges">Section 5.8</a>
631 for information about how to read access privilege values.
632 </p><div class="table" id="FUNCTIONS-ACLITEM-OP-TABLE"><p class="title"><strong>Table 9.73. <code class="type">aclitem</code> Operators</strong></p><div class="table-contents"><table class="table" summary="aclitem Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
640 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
641 <a id="id-1.5.8.33.4.6.2.2.1.1.1.1" class="indexterm"></a>
642 <code class="type">aclitem</code> <code class="literal">=</code> <code class="type">aclitem</code>
643 → <code class="returnvalue">boolean</code>
646 Are <code class="type">aclitem</code>s equal? (Notice that
647 type <code class="type">aclitem</code> lacks the usual set of comparison
648 operators; it has only equality. In turn, <code class="type">aclitem</code>
649 arrays can only be compared for equality.)
652 <code class="literal">'calvin=r*w/hobbes'::aclitem = 'calvin=r*w*/hobbes'::aclitem</code>
653 → <code class="returnvalue">f</code>
654 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
655 <a id="id-1.5.8.33.4.6.2.2.2.1.1.1" class="indexterm"></a>
656 <code class="type">aclitem[]</code> <code class="literal">@></code> <code class="type">aclitem</code>
657 → <code class="returnvalue">boolean</code>
660 Does array contain the specified privileges? (This is true if there
661 is an array entry that matches the <code class="type">aclitem</code>'s grantee and
662 grantor, and has at least the specified set of privileges.)
665 <code class="literal">'{calvin=r*w/hobbes,hobbes=r*w*/postgres}'::aclitem[] @> 'calvin=r*/hobbes'::aclitem</code>
666 → <code class="returnvalue">t</code>
667 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
668 <code class="type">aclitem[]</code> <code class="literal">~</code> <code class="type">aclitem</code>
669 → <code class="returnvalue">boolean</code>
672 This is a deprecated alias for <code class="literal">@></code>.
675 <code class="literal">'{calvin=r*w/hobbes,hobbes=r*w*/postgres}'::aclitem[] ~ 'calvin=r*/hobbes'::aclitem</code>
676 → <code class="returnvalue">t</code>
677 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
678 <a class="xref" href="functions-info.html#FUNCTIONS-ACLITEM-FN-TABLE" title="Table 9.74. aclitem Functions">Table 9.74</a> shows some additional
679 functions to manage the <code class="type">aclitem</code> type.
680 </p><div class="table" id="FUNCTIONS-ACLITEM-FN-TABLE"><p class="title"><strong>Table 9.74. <code class="type">aclitem</code> Functions</strong></p><div class="table-contents"><table class="table" summary="aclitem Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
685 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
686 <a id="id-1.5.8.33.4.8.2.2.1.1.1.1" class="indexterm"></a>
687 <code class="function">acldefault</code> (
688 <em class="parameter"><code>type</code></em> <code class="type">"char"</code>,
689 <em class="parameter"><code>ownerId</code></em> <code class="type">oid</code> )
690 → <code class="returnvalue">aclitem[]</code>
693 Constructs an <code class="type">aclitem</code> array holding the default access
694 privileges for an object of type <em class="parameter"><code>type</code></em> belonging
695 to the role with OID <em class="parameter"><code>ownerId</code></em>. This represents
696 the access privileges that will be assumed when an object's
697 <acronym class="acronym">ACL</acronym> entry is null. (The default access privileges
698 are described in <a class="xref" href="ddl-priv.html" title="5.8. Privileges">Section 5.8</a>.)
699 The <em class="parameter"><code>type</code></em> parameter must be one of
700 'c' for <code class="literal">COLUMN</code>,
701 'r' for <code class="literal">TABLE</code> and table-like objects,
702 's' for <code class="literal">SEQUENCE</code>,
703 'd' for <code class="literal">DATABASE</code>,
704 'f' for <code class="literal">FUNCTION</code> or <code class="literal">PROCEDURE</code>,
705 'l' for <code class="literal">LANGUAGE</code>,
706 'L' for <code class="literal">LARGE OBJECT</code>,
707 'n' for <code class="literal">SCHEMA</code>,
708 'p' for <code class="literal">PARAMETER</code>,
709 't' for <code class="literal">TABLESPACE</code>,
710 'F' for <code class="literal">FOREIGN DATA WRAPPER</code>,
711 'S' for <code class="literal">FOREIGN SERVER</code>,
713 'T' for <code class="literal">TYPE</code> or <code class="literal">DOMAIN</code>.
714 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
715 <a id="id-1.5.8.33.4.8.2.2.2.1.1.1" class="indexterm"></a>
716 <code class="function">aclexplode</code> ( <code class="type">aclitem[]</code> )
717 → <code class="returnvalue">setof record</code>
718 ( <em class="parameter"><code>grantor</code></em> <code class="type">oid</code>,
719 <em class="parameter"><code>grantee</code></em> <code class="type">oid</code>,
720 <em class="parameter"><code>privilege_type</code></em> <code class="type">text</code>,
721 <em class="parameter"><code>is_grantable</code></em> <code class="type">boolean</code> )
724 Returns the <code class="type">aclitem</code> array as a set of rows.
725 If the grantee is the pseudo-role PUBLIC, it is represented by zero in
726 the <em class="parameter"><code>grantee</code></em> column. Each granted privilege is
727 represented as <code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
728 etc (see <a class="xref" href="ddl-priv.html#PRIVILEGE-ABBREVS-TABLE" title="Table 5.1. ACL Privilege Abbreviations">Table 5.1</a> for a full list).
729 Note that each privilege is broken out as a separate row, so
730 only one keyword appears in the <em class="parameter"><code>privilege_type</code></em>
732 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
733 <a id="id-1.5.8.33.4.8.2.2.3.1.1.1" class="indexterm"></a>
734 <code class="function">makeaclitem</code> (
735 <em class="parameter"><code>grantee</code></em> <code class="type">oid</code>,
736 <em class="parameter"><code>grantor</code></em> <code class="type">oid</code>,
737 <em class="parameter"><code>privileges</code></em> <code class="type">text</code>,
738 <em class="parameter"><code>is_grantable</code></em> <code class="type">boolean</code> )
739 → <code class="returnvalue">aclitem</code>
742 Constructs an <code class="type">aclitem</code> with the given properties.
743 <em class="parameter"><code>privileges</code></em> is a comma-separated list of
744 privilege names such as <code class="literal">SELECT</code>,
745 <code class="literal">INSERT</code>, etc, all of which are set in the
746 result. (Case of the privilege string is not significant, and
747 extra whitespace is allowed between but not within privilege
749 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-SCHEMA"><div class="titlepage"><div><div><h3 class="title">9.27.3. Schema Visibility Inquiry Functions <a href="#FUNCTIONS-INFO-SCHEMA" class="id_link">#</a></h3></div></div></div><p>
750 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-SCHEMA-TABLE" title="Table 9.75. Schema Visibility Inquiry Functions">Table 9.75</a> shows functions that
751 determine whether a certain object is <em class="firstterm">visible</em> in the
752 current schema search path.
753 For example, a table is said to be visible if its
754 containing schema is in the search path and no table of the same
755 name appears earlier in the search path. This is equivalent to the
756 statement that the table can be referenced by name without explicit
757 schema qualification. Thus, to list the names of all visible tables:
758 </p><pre class="programlisting">
759 SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
761 For functions and operators, an object in the search path is said to be
762 visible if there is no object of the same name <span class="emphasis"><em>and argument data
763 type(s)</em></span> earlier in the path. For operator classes and families,
764 both the name and the associated index access method are considered.
765 </p><a id="id-1.5.8.33.5.3" class="indexterm"></a><div class="table" id="FUNCTIONS-INFO-SCHEMA-TABLE"><p class="title"><strong>Table 9.75. Schema Visibility Inquiry Functions</strong></p><div class="table-contents"><table class="table" summary="Schema Visibility Inquiry Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
770 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
771 <a id="id-1.5.8.33.5.4.2.2.1.1.1.1" class="indexterm"></a>
772 <code class="function">pg_collation_is_visible</code> ( <em class="parameter"><code>collation</code></em> <code class="type">oid</code> )
773 → <code class="returnvalue">boolean</code>
776 Is collation visible in search path?
777 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
778 <a id="id-1.5.8.33.5.4.2.2.2.1.1.1" class="indexterm"></a>
779 <code class="function">pg_conversion_is_visible</code> ( <em class="parameter"><code>conversion</code></em> <code class="type">oid</code> )
780 → <code class="returnvalue">boolean</code>
783 Is conversion visible in search path?
784 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
785 <a id="id-1.5.8.33.5.4.2.2.3.1.1.1" class="indexterm"></a>
786 <code class="function">pg_function_is_visible</code> ( <em class="parameter"><code>function</code></em> <code class="type">oid</code> )
787 → <code class="returnvalue">boolean</code>
790 Is function visible in search path?
791 (This also works for procedures and aggregates.)
792 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
793 <a id="id-1.5.8.33.5.4.2.2.4.1.1.1" class="indexterm"></a>
794 <code class="function">pg_opclass_is_visible</code> ( <em class="parameter"><code>opclass</code></em> <code class="type">oid</code> )
795 → <code class="returnvalue">boolean</code>
798 Is operator class visible in search path?
799 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
800 <a id="id-1.5.8.33.5.4.2.2.5.1.1.1" class="indexterm"></a>
801 <code class="function">pg_operator_is_visible</code> ( <em class="parameter"><code>operator</code></em> <code class="type">oid</code> )
802 → <code class="returnvalue">boolean</code>
805 Is operator visible in search path?
806 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
807 <a id="id-1.5.8.33.5.4.2.2.6.1.1.1" class="indexterm"></a>
808 <code class="function">pg_opfamily_is_visible</code> ( <em class="parameter"><code>opclass</code></em> <code class="type">oid</code> )
809 → <code class="returnvalue">boolean</code>
812 Is operator family visible in search path?
813 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
814 <a id="id-1.5.8.33.5.4.2.2.7.1.1.1" class="indexterm"></a>
815 <code class="function">pg_statistics_obj_is_visible</code> ( <em class="parameter"><code>stat</code></em> <code class="type">oid</code> )
816 → <code class="returnvalue">boolean</code>
819 Is statistics object visible in search path?
820 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
821 <a id="id-1.5.8.33.5.4.2.2.8.1.1.1" class="indexterm"></a>
822 <code class="function">pg_table_is_visible</code> ( <em class="parameter"><code>table</code></em> <code class="type">oid</code> )
823 → <code class="returnvalue">boolean</code>
826 Is table visible in search path?
827 (This works for all types of relations, including views, materialized
828 views, indexes, sequences and foreign tables.)
829 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
830 <a id="id-1.5.8.33.5.4.2.2.9.1.1.1" class="indexterm"></a>
831 <code class="function">pg_ts_config_is_visible</code> ( <em class="parameter"><code>config</code></em> <code class="type">oid</code> )
832 → <code class="returnvalue">boolean</code>
835 Is text search configuration visible in search path?
836 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
837 <a id="id-1.5.8.33.5.4.2.2.10.1.1.1" class="indexterm"></a>
838 <code class="function">pg_ts_dict_is_visible</code> ( <em class="parameter"><code>dict</code></em> <code class="type">oid</code> )
839 → <code class="returnvalue">boolean</code>
842 Is text search dictionary visible in search path?
843 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
844 <a id="id-1.5.8.33.5.4.2.2.11.1.1.1" class="indexterm"></a>
845 <code class="function">pg_ts_parser_is_visible</code> ( <em class="parameter"><code>parser</code></em> <code class="type">oid</code> )
846 → <code class="returnvalue">boolean</code>
849 Is text search parser visible in search path?
850 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
851 <a id="id-1.5.8.33.5.4.2.2.12.1.1.1" class="indexterm"></a>
852 <code class="function">pg_ts_template_is_visible</code> ( <em class="parameter"><code>template</code></em> <code class="type">oid</code> )
853 → <code class="returnvalue">boolean</code>
856 Is text search template visible in search path?
857 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
858 <a id="id-1.5.8.33.5.4.2.2.13.1.1.1" class="indexterm"></a>
859 <code class="function">pg_type_is_visible</code> ( <em class="parameter"><code>type</code></em> <code class="type">oid</code> )
860 → <code class="returnvalue">boolean</code>
863 Is type (or domain) visible in search path?
864 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
865 All these functions require object OIDs to identify the object to be
866 checked. If you want to test an object by name, it is convenient to use
867 the OID alias types (<code class="type">regclass</code>, <code class="type">regtype</code>,
868 <code class="type">regprocedure</code>, <code class="type">regoperator</code>, <code class="type">regconfig</code>,
869 or <code class="type">regdictionary</code>),
871 </p><pre class="programlisting">
872 SELECT pg_type_is_visible('myschema.widget'::regtype);
874 Note that it would not make much sense to test a non-schema-qualified
875 type name in this way — if the name can be recognized at all, it must be visible.
876 </p></div><div class="sect2" id="FUNCTIONS-INFO-CATALOG"><div class="titlepage"><div><div><h3 class="title">9.27.4. System Catalog Information Functions <a href="#FUNCTIONS-INFO-CATALOG" class="id_link">#</a></h3></div></div></div><p>
877 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE" title="Table 9.76. System Catalog Information Functions">Table 9.76</a> lists functions that
878 extract information from the system catalogs.
879 </p><div class="table" id="FUNCTIONS-INFO-CATALOG-TABLE"><p class="title"><strong>Table 9.76. System Catalog Information Functions</strong></p><div class="table-contents"><table class="table" summary="System Catalog Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
884 </p></th></tr></thead><tbody><tr><td id="FORMAT-TYPE" class="func_table_entry"><p class="func_signature">
885 <a id="id-1.5.8.33.6.3.2.2.1.1.1.1" class="indexterm"></a>
886 <code class="function">format_type</code> ( <em class="parameter"><code>type</code></em> <code class="type">oid</code>, <em class="parameter"><code>typemod</code></em> <code class="type">integer</code> )
887 → <code class="returnvalue">text</code>
890 Returns the SQL name for a data type that is identified by its type
891 OID and possibly a type modifier. Pass NULL for the type modifier if
892 no specific modifier is known.
893 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
894 <a id="id-1.5.8.33.6.3.2.2.2.1.1.1" class="indexterm"></a>
895 <code class="function">pg_basetype</code> ( <code class="type">regtype</code> )
896 → <code class="returnvalue">regtype</code>
899 Returns the OID of the base type of a domain identified by its
900 type OID. If the argument is the OID of a non-domain type,
901 returns the argument as-is. Returns NULL if the argument is
902 not a valid type OID. If there's a chain of domain dependencies,
903 it will recurse until finding the base type.
906 Assuming <code class="literal">CREATE DOMAIN mytext AS text</code>:
909 <code class="literal">pg_basetype('mytext'::regtype)</code>
910 → <code class="returnvalue">text</code>
911 </p></td></tr><tr><td id="PG-CHAR-TO-ENCODING" class="func_table_entry"><p class="func_signature">
912 <a id="id-1.5.8.33.6.3.2.2.3.1.1.1" class="indexterm"></a>
913 <code class="function">pg_char_to_encoding</code> ( <em class="parameter"><code>encoding</code></em> <code class="type">name</code> )
914 → <code class="returnvalue">integer</code>
917 Converts the supplied encoding name into an integer representing the
918 internal identifier used in some system catalog tables.
919 Returns <code class="literal">-1</code> if an unknown encoding name is provided.
920 </p></td></tr><tr><td id="PG-ENCODING-TO-CHAR" class="func_table_entry"><p class="func_signature">
921 <a id="id-1.5.8.33.6.3.2.2.4.1.1.1" class="indexterm"></a>
922 <code class="function">pg_encoding_to_char</code> ( <em class="parameter"><code>encoding</code></em> <code class="type">integer</code> )
923 → <code class="returnvalue">name</code>
926 Converts the integer used as the internal identifier of an encoding in some
927 system catalog tables into a human-readable string.
928 Returns an empty string if an invalid encoding number is provided.
929 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
930 <a id="id-1.5.8.33.6.3.2.2.5.1.1.1" class="indexterm"></a>
931 <code class="function">pg_get_catalog_foreign_keys</code> ()
932 → <code class="returnvalue">setof record</code>
933 ( <em class="parameter"><code>fktable</code></em> <code class="type">regclass</code>,
934 <em class="parameter"><code>fkcols</code></em> <code class="type">text[]</code>,
935 <em class="parameter"><code>pktable</code></em> <code class="type">regclass</code>,
936 <em class="parameter"><code>pkcols</code></em> <code class="type">text[]</code>,
937 <em class="parameter"><code>is_array</code></em> <code class="type">boolean</code>,
938 <em class="parameter"><code>is_opt</code></em> <code class="type">boolean</code> )
941 Returns a set of records describing the foreign key relationships
942 that exist within the <span class="productname">PostgreSQL</span> system
944 The <em class="parameter"><code>fktable</code></em> column contains the name of the
945 referencing catalog, and the <em class="parameter"><code>fkcols</code></em> column
946 contains the name(s) of the referencing column(s). Similarly,
947 the <em class="parameter"><code>pktable</code></em> column contains the name of the
948 referenced catalog, and the <em class="parameter"><code>pkcols</code></em> column
949 contains the name(s) of the referenced column(s).
950 If <em class="parameter"><code>is_array</code></em> is true, the last referencing
951 column is an array, each of whose elements should match some entry
952 in the referenced catalog.
953 If <em class="parameter"><code>is_opt</code></em> is true, the referencing column(s)
954 are allowed to contain zeroes instead of a valid reference.
955 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
956 <a id="id-1.5.8.33.6.3.2.2.6.1.1.1" class="indexterm"></a>
957 <code class="function">pg_get_constraintdef</code> ( <em class="parameter"><code>constraint</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
958 → <code class="returnvalue">text</code>
961 Reconstructs the creating command for a constraint.
962 (This is a decompiled reconstruction, not the original text
964 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
965 <a id="id-1.5.8.33.6.3.2.2.7.1.1.1" class="indexterm"></a>
966 <code class="function">pg_get_expr</code> ( <em class="parameter"><code>expr</code></em> <code class="type">pg_node_tree</code>, <em class="parameter"><code>relation</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
967 → <code class="returnvalue">text</code>
970 Decompiles the internal form of an expression stored in the system
971 catalogs, such as the default value for a column. If the expression
972 might contain Vars, specify the OID of the relation they refer to as
973 the second parameter; if no Vars are expected, passing zero is
975 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
976 <a id="id-1.5.8.33.6.3.2.2.8.1.1.1" class="indexterm"></a>
977 <code class="function">pg_get_functiondef</code> ( <em class="parameter"><code>func</code></em> <code class="type">oid</code> )
978 → <code class="returnvalue">text</code>
981 Reconstructs the creating command for a function or procedure.
982 (This is a decompiled reconstruction, not the original text
984 The result is a complete <code class="command">CREATE OR REPLACE FUNCTION</code>
985 or <code class="command">CREATE OR REPLACE PROCEDURE</code> statement.
986 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
987 <a id="id-1.5.8.33.6.3.2.2.9.1.1.1" class="indexterm"></a>
988 <code class="function">pg_get_function_arguments</code> ( <em class="parameter"><code>func</code></em> <code class="type">oid</code> )
989 → <code class="returnvalue">text</code>
992 Reconstructs the argument list of a function or procedure, in the form
993 it would need to appear in within <code class="command">CREATE FUNCTION</code>
994 (including default values).
995 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
996 <a id="id-1.5.8.33.6.3.2.2.10.1.1.1" class="indexterm"></a>
997 <code class="function">pg_get_function_identity_arguments</code> ( <em class="parameter"><code>func</code></em> <code class="type">oid</code> )
998 → <code class="returnvalue">text</code>
1001 Reconstructs the argument list necessary to identify a function or
1002 procedure, in the form it would need to appear in within commands such
1003 as <code class="command">ALTER FUNCTION</code>. This form omits default values.
1004 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1005 <a id="id-1.5.8.33.6.3.2.2.11.1.1.1" class="indexterm"></a>
1006 <code class="function">pg_get_function_result</code> ( <em class="parameter"><code>func</code></em> <code class="type">oid</code> )
1007 → <code class="returnvalue">text</code>
1010 Reconstructs the <code class="literal">RETURNS</code> clause of a function, in
1011 the form it would need to appear in within <code class="command">CREATE
1012 FUNCTION</code>. Returns <code class="literal">NULL</code> for a procedure.
1013 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1014 <a id="id-1.5.8.33.6.3.2.2.12.1.1.1" class="indexterm"></a>
1015 <code class="function">pg_get_indexdef</code> ( <em class="parameter"><code>index</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>column</code></em> <code class="type">integer</code>, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
1016 → <code class="returnvalue">text</code>
1019 Reconstructs the creating command for an index.
1020 (This is a decompiled reconstruction, not the original text
1021 of the command.) If <em class="parameter"><code>column</code></em> is supplied and is
1022 not zero, only the definition of that column is reconstructed.
1023 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1024 <a id="id-1.5.8.33.6.3.2.2.13.1.1.1" class="indexterm"></a>
1025 <code class="function">pg_get_keywords</code> ()
1026 → <code class="returnvalue">setof record</code>
1027 ( <em class="parameter"><code>word</code></em> <code class="type">text</code>,
1028 <em class="parameter"><code>catcode</code></em> <code class="type">"char"</code>,
1029 <em class="parameter"><code>barelabel</code></em> <code class="type">boolean</code>,
1030 <em class="parameter"><code>catdesc</code></em> <code class="type">text</code>,
1031 <em class="parameter"><code>baredesc</code></em> <code class="type">text</code> )
1034 Returns a set of records describing the SQL keywords recognized by the
1035 server. The <em class="parameter"><code>word</code></em> column contains the
1036 keyword. The <em class="parameter"><code>catcode</code></em> column contains a
1037 category code: <code class="literal">U</code> for an unreserved
1038 keyword, <code class="literal">C</code> for a keyword that can be a column
1039 name, <code class="literal">T</code> for a keyword that can be a type or
1040 function name, or <code class="literal">R</code> for a fully reserved keyword.
1041 The <em class="parameter"><code>barelabel</code></em> column
1042 contains <code class="literal">true</code> if the keyword can be used as
1043 a <span class="quote">“<span class="quote">bare</span>”</span> column label in <code class="command">SELECT</code> lists,
1044 or <code class="literal">false</code> if it can only be used
1045 after <code class="literal">AS</code>.
1046 The <em class="parameter"><code>catdesc</code></em> column contains a
1047 possibly-localized string describing the keyword's category.
1048 The <em class="parameter"><code>baredesc</code></em> column contains a
1049 possibly-localized string describing the keyword's column label status.
1050 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1051 <a id="id-1.5.8.33.6.3.2.2.14.1.1.1" class="indexterm"></a>
1052 <code class="function">pg_get_partkeydef</code> ( <em class="parameter"><code>table</code></em> <code class="type">oid</code> )
1053 → <code class="returnvalue">text</code>
1056 Reconstructs the definition of a partitioned table's partition
1057 key, in the form it would have in the <code class="literal">PARTITION
1058 BY</code> clause of <code class="command">CREATE TABLE</code>.
1059 (This is a decompiled reconstruction, not the original text
1061 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1062 <a id="id-1.5.8.33.6.3.2.2.15.1.1.1" class="indexterm"></a>
1063 <code class="function">pg_get_ruledef</code> ( <em class="parameter"><code>rule</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
1064 → <code class="returnvalue">text</code>
1067 Reconstructs the creating command for a rule.
1068 (This is a decompiled reconstruction, not the original text
1070 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1071 <a id="id-1.5.8.33.6.3.2.2.16.1.1.1" class="indexterm"></a>
1072 <code class="function">pg_get_serial_sequence</code> ( <em class="parameter"><code>table</code></em> <code class="type">text</code>, <em class="parameter"><code>column</code></em> <code class="type">text</code> )
1073 → <code class="returnvalue">text</code>
1076 Returns the name of the sequence associated with a column,
1077 or NULL if no sequence is associated with the column.
1078 If the column is an identity column, the associated sequence is the
1079 sequence internally created for that column.
1080 For columns created using one of the serial types
1081 (<code class="type">serial</code>, <code class="type">smallserial</code>, <code class="type">bigserial</code>),
1082 it is the sequence created for that serial column definition.
1083 In the latter case, the association can be modified or removed
1084 with <code class="command">ALTER SEQUENCE OWNED BY</code>.
1085 (This function probably should have been
1086 called <code class="function">pg_get_owned_sequence</code>; its current name
1087 reflects the fact that it has historically been used with serial-type
1088 columns.) The first parameter is a table name with optional
1089 schema, and the second parameter is a column name. Because the first
1090 parameter potentially contains both schema and table names, it is
1091 parsed per usual SQL rules, meaning it is lower-cased by default.
1092 The second parameter, being just a column name, is treated literally
1093 and so has its case preserved. The result is suitably formatted
1094 for passing to the sequence functions (see
1095 <a class="xref" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions">Section 9.17</a>).
1098 A typical use is in reading the current value of the sequence for an
1099 identity or serial column, for example:
1100 </p><pre class="programlisting">
1101 SELECT currval(pg_get_serial_sequence('sometable', 'id'));
1103 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1104 <a id="id-1.5.8.33.6.3.2.2.17.1.1.1" class="indexterm"></a>
1105 <code class="function">pg_get_statisticsobjdef</code> ( <em class="parameter"><code>statobj</code></em> <code class="type">oid</code> )
1106 → <code class="returnvalue">text</code>
1109 Reconstructs the creating command for an extended statistics object.
1110 (This is a decompiled reconstruction, not the original text
1112 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1113 <a id="id-1.5.8.33.6.3.2.2.18.1.1.1" class="indexterm"></a>
1114 <code class="function">pg_get_triggerdef</code> ( <em class="parameter"><code>trigger</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
1115 → <code class="returnvalue">text</code>
1118 Reconstructs the creating command for a trigger.
1119 (This is a decompiled reconstruction, not the original text
1121 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1122 <a id="id-1.5.8.33.6.3.2.2.19.1.1.1" class="indexterm"></a>
1123 <code class="function">pg_get_userbyid</code> ( <em class="parameter"><code>role</code></em> <code class="type">oid</code> )
1124 → <code class="returnvalue">name</code>
1127 Returns a role's name given its OID.
1128 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1129 <a id="id-1.5.8.33.6.3.2.2.20.1.1.1" class="indexterm"></a>
1130 <code class="function">pg_get_viewdef</code> ( <em class="parameter"><code>view</code></em> <code class="type">oid</code> [<span class="optional">, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
1131 → <code class="returnvalue">text</code>
1134 Reconstructs the underlying <code class="command">SELECT</code> command for a
1135 view or materialized view. (This is a decompiled reconstruction, not
1136 the original text of the command.)
1137 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1138 <code class="function">pg_get_viewdef</code> ( <em class="parameter"><code>view</code></em> <code class="type">oid</code>, <em class="parameter"><code>wrap_column</code></em> <code class="type">integer</code> )
1139 → <code class="returnvalue">text</code>
1142 Reconstructs the underlying <code class="command">SELECT</code> command for a
1143 view or materialized view. (This is a decompiled reconstruction, not
1144 the original text of the command.) In this form of the function,
1145 pretty-printing is always enabled, and long lines are wrapped to try
1146 to keep them shorter than the specified number of columns.
1147 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1148 <code class="function">pg_get_viewdef</code> ( <em class="parameter"><code>view</code></em> <code class="type">text</code> [<span class="optional">, <em class="parameter"><code>pretty</code></em> <code class="type">boolean</code> </span>] )
1149 → <code class="returnvalue">text</code>
1152 Reconstructs the underlying <code class="command">SELECT</code> command for a
1153 view or materialized view, working from a textual name for the view
1154 rather than its OID. (This is deprecated; use the OID variant
1156 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1157 <a id="id-1.5.8.33.6.3.2.2.23.1.1.1" class="indexterm"></a>
1158 <code class="function">pg_index_column_has_property</code> ( <em class="parameter"><code>index</code></em> <code class="type">regclass</code>, <em class="parameter"><code>column</code></em> <code class="type">integer</code>, <em class="parameter"><code>property</code></em> <code class="type">text</code> )
1159 → <code class="returnvalue">boolean</code>
1162 Tests whether an index column has the named property.
1163 Common index column properties are listed in
1164 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-INDEX-COLUMN-PROPS" title="Table 9.77. Index Column Properties">Table 9.77</a>.
1165 (Note that extension access methods can define additional property
1166 names for their indexes.)
1167 <code class="literal">NULL</code> is returned if the property name is not known
1168 or does not apply to the particular object, or if the OID or column
1169 number does not identify a valid object.
1170 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1171 <a id="id-1.5.8.33.6.3.2.2.24.1.1.1" class="indexterm"></a>
1172 <code class="function">pg_index_has_property</code> ( <em class="parameter"><code>index</code></em> <code class="type">regclass</code>, <em class="parameter"><code>property</code></em> <code class="type">text</code> )
1173 → <code class="returnvalue">boolean</code>
1176 Tests whether an index has the named property.
1177 Common index properties are listed in
1178 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-INDEX-PROPS" title="Table 9.78. Index Properties">Table 9.78</a>.
1179 (Note that extension access methods can define additional property
1180 names for their indexes.)
1181 <code class="literal">NULL</code> is returned if the property name is not known
1182 or does not apply to the particular object, or if the OID does not
1183 identify a valid object.
1184 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1185 <a id="id-1.5.8.33.6.3.2.2.25.1.1.1" class="indexterm"></a>
1186 <code class="function">pg_indexam_has_property</code> ( <em class="parameter"><code>am</code></em> <code class="type">oid</code>, <em class="parameter"><code>property</code></em> <code class="type">text</code> )
1187 → <code class="returnvalue">boolean</code>
1190 Tests whether an index access method has the named property.
1191 Access method properties are listed in
1192 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-INDEXAM-PROPS" title="Table 9.79. Index Access Method Properties">Table 9.79</a>.
1193 <code class="literal">NULL</code> is returned if the property name is not known
1194 or does not apply to the particular object, or if the OID does not
1195 identify a valid object.
1196 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1197 <a id="id-1.5.8.33.6.3.2.2.26.1.1.1" class="indexterm"></a>
1198 <code class="function">pg_options_to_table</code> ( <em class="parameter"><code>options_array</code></em> <code class="type">text[]</code> )
1199 → <code class="returnvalue">setof record</code>
1200 ( <em class="parameter"><code>option_name</code></em> <code class="type">text</code>,
1201 <em class="parameter"><code>option_value</code></em> <code class="type">text</code> )
1204 Returns the set of storage options represented by a value from
1205 <code class="structname">pg_class</code>.<code class="structfield">reloptions</code> or
1206 <code class="structname">pg_attribute</code>.<code class="structfield">attoptions</code>.
1207 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1208 <a id="id-1.5.8.33.6.3.2.2.27.1.1.1" class="indexterm"></a>
1209 <code class="function">pg_settings_get_flags</code> ( <em class="parameter"><code>guc</code></em> <code class="type">text</code> )
1210 → <code class="returnvalue">text[]</code>
1213 Returns an array of the flags associated with the given GUC, or
1214 <code class="literal">NULL</code> if it does not exist. The result is
1215 an empty array if the GUC exists but there are no flags to show.
1216 Only the most useful flags listed in
1217 <a class="xref" href="functions-info.html#FUNCTIONS-PG-SETTINGS-FLAGS" title="Table 9.80. GUC Flags">Table 9.80</a> are exposed.
1218 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1219 <a id="id-1.5.8.33.6.3.2.2.28.1.1.1" class="indexterm"></a>
1220 <code class="function">pg_tablespace_databases</code> ( <em class="parameter"><code>tablespace</code></em> <code class="type">oid</code> )
1221 → <code class="returnvalue">setof oid</code>
1224 Returns the set of OIDs of databases that have objects stored in the
1225 specified tablespace. If this function returns any rows, the
1226 tablespace is not empty and cannot be dropped. To identify the specific
1227 objects populating the tablespace, you will need to connect to the
1228 database(s) identified by <code class="function">pg_tablespace_databases</code>
1229 and query their <code class="structname">pg_class</code> catalogs.
1230 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1231 <a id="id-1.5.8.33.6.3.2.2.29.1.1.1" class="indexterm"></a>
1232 <code class="function">pg_tablespace_location</code> ( <em class="parameter"><code>tablespace</code></em> <code class="type">oid</code> )
1233 → <code class="returnvalue">text</code>
1236 Returns the file system path that this tablespace is located in.
1237 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1238 <a id="id-1.5.8.33.6.3.2.2.30.1.1.1" class="indexterm"></a>
1239 <code class="function">pg_typeof</code> ( <code class="type">"any"</code> )
1240 → <code class="returnvalue">regtype</code>
1243 Returns the OID of the data type of the value that is passed to it.
1244 This can be helpful for troubleshooting or dynamically constructing
1245 SQL queries. The function is declared as
1246 returning <code class="type">regtype</code>, which is an OID alias type (see
1247 <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
1248 OID for comparison purposes but displays as a type name.
1251 <code class="literal">pg_typeof(33)</code>
1252 → <code class="returnvalue">integer</code>
1253 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1254 <a id="id-1.5.8.33.6.3.2.2.31.1.1.1" class="indexterm"></a>
1255 <code class="function">COLLATION FOR</code> ( <code class="type">"any"</code> )
1256 → <code class="returnvalue">text</code>
1259 Returns the name of the collation of the value that is passed to it.
1260 The value is quoted and schema-qualified if necessary. If no
1261 collation was derived for the argument expression,
1262 then <code class="literal">NULL</code> is returned. If the argument is not of a
1263 collatable data type, then an error is raised.
1266 <code class="literal">collation for ('foo'::text)</code>
1267 → <code class="returnvalue">"default"</code>
1270 <code class="literal">collation for ('foo' COLLATE "de_DE")</code>
1271 → <code class="returnvalue">"de_DE"</code>
1272 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1273 <a id="id-1.5.8.33.6.3.2.2.32.1.1.1" class="indexterm"></a>
1274 <code class="function">to_regclass</code> ( <code class="type">text</code> )
1275 → <code class="returnvalue">regclass</code>
1278 Translates a textual relation name to its OID. A similar result is
1279 obtained by casting the string to type <code class="type">regclass</code> (see
1280 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1281 <code class="literal">NULL</code> rather than throwing an error if the name is
1283 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1284 <a id="id-1.5.8.33.6.3.2.2.33.1.1.1" class="indexterm"></a>
1285 <code class="function">to_regcollation</code> ( <code class="type">text</code> )
1286 → <code class="returnvalue">regcollation</code>
1289 Translates a textual collation name to its OID. A similar result is
1290 obtained by casting the string to type <code class="type">regcollation</code> (see
1291 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1292 <code class="literal">NULL</code> rather than throwing an error if the name is
1294 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1295 <a id="id-1.5.8.33.6.3.2.2.34.1.1.1" class="indexterm"></a>
1296 <code class="function">to_regnamespace</code> ( <code class="type">text</code> )
1297 → <code class="returnvalue">regnamespace</code>
1300 Translates a textual schema name to its OID. A similar result is
1301 obtained by casting the string to type <code class="type">regnamespace</code> (see
1302 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1303 <code class="literal">NULL</code> rather than throwing an error if the name is
1305 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1306 <a id="id-1.5.8.33.6.3.2.2.35.1.1.1" class="indexterm"></a>
1307 <code class="function">to_regoper</code> ( <code class="type">text</code> )
1308 → <code class="returnvalue">regoper</code>
1311 Translates a textual operator name to its OID. A similar result is
1312 obtained by casting the string to type <code class="type">regoper</code> (see
1313 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1314 <code class="literal">NULL</code> rather than throwing an error if the name is
1315 not found or is ambiguous.
1316 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1317 <a id="id-1.5.8.33.6.3.2.2.36.1.1.1" class="indexterm"></a>
1318 <code class="function">to_regoperator</code> ( <code class="type">text</code> )
1319 → <code class="returnvalue">regoperator</code>
1322 Translates a textual operator name (with parameter types) to its OID. A similar result is
1323 obtained by casting the string to type <code class="type">regoperator</code> (see
1324 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1325 <code class="literal">NULL</code> rather than throwing an error if the name is
1327 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1328 <a id="id-1.5.8.33.6.3.2.2.37.1.1.1" class="indexterm"></a>
1329 <code class="function">to_regproc</code> ( <code class="type">text</code> )
1330 → <code class="returnvalue">regproc</code>
1333 Translates a textual function or procedure name to its OID. A similar result is
1334 obtained by casting the string to type <code class="type">regproc</code> (see
1335 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1336 <code class="literal">NULL</code> rather than throwing an error if the name is
1337 not found or is ambiguous.
1338 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1339 <a id="id-1.5.8.33.6.3.2.2.38.1.1.1" class="indexterm"></a>
1340 <code class="function">to_regprocedure</code> ( <code class="type">text</code> )
1341 → <code class="returnvalue">regprocedure</code>
1344 Translates a textual function or procedure name (with argument types) to its OID. A similar result is
1345 obtained by casting the string to type <code class="type">regprocedure</code> (see
1346 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1347 <code class="literal">NULL</code> rather than throwing an error if the name is
1349 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1350 <a id="id-1.5.8.33.6.3.2.2.39.1.1.1" class="indexterm"></a>
1351 <code class="function">to_regrole</code> ( <code class="type">text</code> )
1352 → <code class="returnvalue">regrole</code>
1355 Translates a textual role name to its OID. A similar result is
1356 obtained by casting the string to type <code class="type">regrole</code> (see
1357 <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>); however, this function will return
1358 <code class="literal">NULL</code> rather than throwing an error if the name is
1360 </p></td></tr><tr><td id="TO-REGTYPE" class="func_table_entry"><p class="func_signature">
1361 <a id="id-1.5.8.33.6.3.2.2.40.1.1.1" class="indexterm"></a>
1362 <code class="function">to_regtype</code> ( <code class="type">text</code> )
1363 → <code class="returnvalue">regtype</code>
1366 Parses a string of text, extracts a potential type name from it,
1367 and translates that name into a type OID. A syntax error in the
1368 string will result in an error; but if the string is a
1369 syntactically valid type name that happens not to be found in the
1370 catalogs, the result is <code class="literal">NULL</code>. A similar result
1371 is obtained by casting the string to type <code class="type">regtype</code>
1372 (see <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>), except that that will throw
1373 error for name not found.
1374 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1375 <a id="id-1.5.8.33.6.3.2.2.41.1.1.1" class="indexterm"></a>
1376 <code class="function">to_regtypemod</code> ( <code class="type">text</code> )
1377 → <code class="returnvalue">integer</code>
1380 Parses a string of text, extracts a potential type name from it,
1381 and translates its type modifier, if any. A syntax error in the
1382 string will result in an error; but if the string is a
1383 syntactically valid type name that happens not to be found in the
1384 catalogs, the result is <code class="literal">NULL</code>. The result is
1385 <code class="literal">-1</code> if no type modifier is present.
1388 <code class="function">to_regtypemod</code> can be combined with
1389 <a class="xref" href="functions-info.html#TO-REGTYPE">to_regtype</a> to produce appropriate inputs for
1390 <a class="xref" href="functions-info.html#FORMAT-TYPE">format_type</a>, allowing a string representing a
1391 type name to be canonicalized.
1394 <code class="literal">format_type(to_regtype('varchar(32)'), to_regtypemod('varchar(32)'))</code>
1395 → <code class="returnvalue">character varying(32)</code>
1396 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
1397 Most of the functions that reconstruct (decompile) database objects
1398 have an optional <em class="parameter"><code>pretty</code></em> flag, which
1399 if <code class="literal">true</code> causes the result to
1400 be <span class="quote">“<span class="quote">pretty-printed</span>”</span>. Pretty-printing suppresses unnecessary
1401 parentheses and adds whitespace for legibility.
1402 The pretty-printed format is more readable, but the default format
1403 is more likely to be interpreted the same way by future versions of
1404 <span class="productname">PostgreSQL</span>; so avoid using pretty-printed output
1405 for dump purposes. Passing <code class="literal">false</code> for
1406 the <em class="parameter"><code>pretty</code></em> parameter yields the same result as
1407 omitting the parameter.
1408 </p><div class="table" id="FUNCTIONS-INFO-INDEX-COLUMN-PROPS"><p class="title"><strong>Table 9.77. 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?
1409 </td></tr><tr><td><code class="literal">desc</code></td><td>Does the column sort in descending order on a forward scan?
1410 </td></tr><tr><td><code class="literal">nulls_first</code></td><td>Does the column sort with nulls first on a forward scan?
1411 </td></tr><tr><td><code class="literal">nulls_last</code></td><td>Does the column sort with nulls last on a forward scan?
1412 </td></tr><tr><td><code class="literal">orderable</code></td><td>Does the column possess any defined sort ordering?
1413 </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>
1414 operator, for example <code class="literal">ORDER BY col <-> constant</code> ?
1415 </td></tr><tr><td><code class="literal">returnable</code></td><td>Can the column value be returned by an index-only scan?
1416 </td></tr><tr><td><code class="literal">search_array</code></td><td>Does the column natively support <code class="literal">col = ANY(array)</code>
1418 </td></tr><tr><td><code class="literal">search_nulls</code></td><td>Does the column support <code class="literal">IS NULL</code> and
1419 <code class="literal">IS NOT NULL</code> searches?
1420 </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-INFO-INDEX-PROPS"><p class="title"><strong>Table 9.78. 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?
1421 </td></tr><tr><td><code class="literal">index_scan</code></td><td>Does the index support plain (non-bitmap) scans?
1422 </td></tr><tr><td><code class="literal">bitmap_scan</code></td><td>Does the index support bitmap scans?
1423 </td></tr><tr><td><code class="literal">backward_scan</code></td><td>Can the scan direction be changed in mid-scan (to
1424 support <code class="literal">FETCH BACKWARD</code> on a cursor without
1425 needing materialization)?
1426 </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-INFO-INDEXAM-PROPS"><p class="title"><strong>Table 9.79. 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>,
1427 <code class="literal">DESC</code> and related keywords in
1428 <code class="literal">CREATE INDEX</code>?
1429 </td></tr><tr><td><code class="literal">can_unique</code></td><td>Does the access method support unique indexes?
1430 </td></tr><tr><td><code class="literal">can_multi_col</code></td><td>Does the access method support indexes with multiple columns?
1431 </td></tr><tr><td><code class="literal">can_exclude</code></td><td>Does the access method support exclusion constraints?
1432 </td></tr><tr><td><code class="literal">can_include</code></td><td>Does the access method support the <code class="literal">INCLUDE</code>
1433 clause of <code class="literal">CREATE INDEX</code>?
1434 </td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-PG-SETTINGS-FLAGS"><p class="title"><strong>Table 9.80. GUC Flags</strong></p><div class="table-contents"><table class="table" summary="GUC Flags" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Flag</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">EXPLAIN</code></td><td>Parameters with this flag are included in
1435 <code class="command">EXPLAIN (SETTINGS)</code> commands.
1436 </td></tr><tr><td><code class="literal">NO_SHOW_ALL</code></td><td>Parameters with this flag are excluded from
1437 <code class="command">SHOW ALL</code> commands.
1438 </td></tr><tr><td><code class="literal">NO_RESET</code></td><td>Parameters with this flag do not support
1439 <code class="command">RESET</code> commands.
1440 </td></tr><tr><td><code class="literal">NO_RESET_ALL</code></td><td>Parameters with this flag are excluded from
1441 <code class="command">RESET ALL</code> commands.
1442 </td></tr><tr><td><code class="literal">NOT_IN_SAMPLE</code></td><td>Parameters with this flag are not included in
1443 <code class="filename">postgresql.conf</code> by default.
1444 </td></tr><tr><td><code class="literal">RUNTIME_COMPUTED</code></td><td>Parameters with this flag are runtime-computed ones.
1445 </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-OBJECT"><div class="titlepage"><div><div><h3 class="title">9.27.5. Object Information and Addressing Functions <a href="#FUNCTIONS-INFO-OBJECT" class="id_link">#</a></h3></div></div></div><p>
1446 <a class="xref" href="functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE" title="Table 9.81. Object Information and Addressing Functions">Table 9.81</a> lists functions related to
1447 database object identification and addressing.
1448 </p><div class="table" id="FUNCTIONS-INFO-OBJECT-TABLE"><p class="title"><strong>Table 9.81. Object Information and Addressing Functions</strong></p><div class="table-contents"><table class="table" summary="Object Information and Addressing Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1453 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1454 <a id="id-1.5.8.33.7.3.2.2.1.1.1.1" class="indexterm"></a>
1455 <code class="function">pg_get_acl</code> ( <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> )
1456 → <code class="returnvalue">aclitem[]</code>
1459 Returns the <acronym class="acronym">ACL</acronym> for a database object, specified
1460 by catalog OID, object OID and sub-object ID. This function returns
1461 <code class="literal">NULL</code> values for undefined objects.
1462 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1463 <a id="id-1.5.8.33.7.3.2.2.2.1.1.1" class="indexterm"></a>
1464 <code class="function">pg_describe_object</code> ( <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> )
1465 → <code class="returnvalue">text</code>
1468 Returns a textual description of a database object identified by
1469 catalog OID, object OID, and sub-object ID (such as a column number
1470 within a table; the sub-object ID is zero when referring to a whole
1471 object). This description is intended to be human-readable, and might
1472 be translated, depending on server configuration. This is especially
1473 useful to determine the identity of an object referenced in the
1474 <code class="structname">pg_depend</code> catalog. This function returns
1475 <code class="literal">NULL</code> values for undefined objects.
1476 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1477 <a id="id-1.5.8.33.7.3.2.2.3.1.1.1" class="indexterm"></a>
1478 <code class="function">pg_identify_object</code> ( <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> )
1479 → <code class="returnvalue">record</code>
1480 ( <em class="parameter"><code>type</code></em> <code class="type">text</code>,
1481 <em class="parameter"><code>schema</code></em> <code class="type">text</code>,
1482 <em class="parameter"><code>name</code></em> <code class="type">text</code>,
1483 <em class="parameter"><code>identity</code></em> <code class="type">text</code> )
1486 Returns a row containing enough information to uniquely identify the
1487 database object specified by catalog OID, object OID and sub-object
1489 This information is intended to be machine-readable, and is never
1491 <em class="parameter"><code>type</code></em> identifies the type of database object;
1492 <em class="parameter"><code>schema</code></em> is the schema name that the object
1493 belongs in, or <code class="literal">NULL</code> for object types that do not
1495 <em class="parameter"><code>name</code></em> is the name of the object, quoted if
1496 necessary, if the name (along with schema name, if pertinent) is
1497 sufficient to uniquely identify the object,
1498 otherwise <code class="literal">NULL</code>;
1499 <em class="parameter"><code>identity</code></em> is the complete object identity, with
1500 the precise format depending on object type, and each name within the
1501 format being schema-qualified and quoted as necessary. Undefined
1502 objects are identified with <code class="literal">NULL</code> values.
1503 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1504 <a id="id-1.5.8.33.7.3.2.2.4.1.1.1" class="indexterm"></a>
1505 <code class="function">pg_identify_object_as_address</code> ( <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> )
1506 → <code class="returnvalue">record</code>
1507 ( <em class="parameter"><code>type</code></em> <code class="type">text</code>,
1508 <em class="parameter"><code>object_names</code></em> <code class="type">text[]</code>,
1509 <em class="parameter"><code>object_args</code></em> <code class="type">text[]</code> )
1512 Returns a row containing enough information to uniquely identify the
1513 database object specified by catalog OID, object OID and sub-object
1515 The returned information is independent of the current server, that
1516 is, it could be used to identify an identically named object in
1518 <em class="parameter"><code>type</code></em> identifies the type of database object;
1519 <em class="parameter"><code>object_names</code></em> and
1520 <em class="parameter"><code>object_args</code></em>
1521 are text arrays that together form a reference to the object.
1522 These three values can be passed
1523 to <code class="function">pg_get_object_address</code> to obtain the internal
1524 address of the object.
1525 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1526 <a id="id-1.5.8.33.7.3.2.2.5.1.1.1" class="indexterm"></a>
1527 <code class="function">pg_get_object_address</code> ( <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> )
1528 → <code class="returnvalue">record</code>
1529 ( <em class="parameter"><code>classid</code></em> <code class="type">oid</code>,
1530 <em class="parameter"><code>objid</code></em> <code class="type">oid</code>,
1531 <em class="parameter"><code>objsubid</code></em> <code class="type">integer</code> )
1534 Returns a row containing enough information to uniquely identify the
1535 database object specified by a type code and object name and argument
1537 The returned values are the ones that would be used in system catalogs
1538 such as <code class="structname">pg_depend</code>; they can be passed to
1539 other system functions such as <code class="function">pg_describe_object</code>
1540 or <code class="function">pg_identify_object</code>.
1541 <em class="parameter"><code>classid</code></em> is the OID of the system catalog
1542 containing the object;
1543 <em class="parameter"><code>objid</code></em> is the OID of the object itself, and
1544 <em class="parameter"><code>objsubid</code></em> is the sub-object ID, or zero if none.
1545 This function is the inverse
1546 of <code class="function">pg_identify_object_as_address</code>.
1547 Undefined objects are identified with <code class="literal">NULL</code> values.
1548 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
1549 <code class="function">pg_get_acl</code> is useful for retrieving and inspecting
1550 the privileges associated with database objects without looking at
1551 specific catalogs. For example, to retrieve all the granted privileges
1552 on objects in the current database:
1553 </p><pre class="programlisting">
1555 (pg_identify_object(s.classid,s.objid,s.objsubid)).*,
1556 pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl
1557 FROM pg_catalog.pg_shdepend AS s
1558 JOIN pg_catalog.pg_database AS d
1559 ON d.datname = current_database() AND
1561 JOIN pg_catalog.pg_authid AS a
1562 ON a.oid = s.refobjid AND
1563 s.refclassid = 'pg_authid'::regclass
1564 WHERE s.deptype = 'a';
1565 -[ RECORD 1 ]-----------------------------------------
1569 identity | public.testtab
1570 acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
1572 </p></div><div class="sect2" id="FUNCTIONS-INFO-COMMENT"><div class="titlepage"><div><div><h3 class="title">9.27.6. Comment Information Functions <a href="#FUNCTIONS-INFO-COMMENT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.33.8.2" class="indexterm"></a><p>
1573 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE" title="Table 9.82. Comment Information Functions">Table 9.82</a>
1574 extract comments previously stored with the <a class="xref" href="sql-comment.html" title="COMMENT"><span class="refentrytitle">COMMENT</span></a>
1575 command. A null value is returned if no
1576 comment could be found for the specified parameters.
1577 </p><div class="table" id="FUNCTIONS-INFO-COMMENT-TABLE"><p class="title"><strong>Table 9.82. Comment Information Functions</strong></p><div class="table-contents"><table class="table" summary="Comment Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1582 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1583 <a id="id-1.5.8.33.8.4.2.2.1.1.1.1" class="indexterm"></a>
1584 <code class="function">col_description</code> ( <em class="parameter"><code>table</code></em> <code class="type">oid</code>, <em class="parameter"><code>column</code></em> <code class="type">integer</code> )
1585 → <code class="returnvalue">text</code>
1588 Returns the comment for a table column, which is specified by the OID
1589 of its table and its column number.
1590 (<code class="function">obj_description</code> cannot be used for table
1591 columns, since columns do not have OIDs of their own.)
1592 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1593 <a id="id-1.5.8.33.8.4.2.2.2.1.1.1" class="indexterm"></a>
1594 <code class="function">obj_description</code> ( <em class="parameter"><code>object</code></em> <code class="type">oid</code>, <em class="parameter"><code>catalog</code></em> <code class="type">name</code> )
1595 → <code class="returnvalue">text</code>
1598 Returns the comment for a database object specified by its OID and the
1599 name of the containing system catalog. For
1600 example, <code class="literal">obj_description(123456, 'pg_class')</code> would
1601 retrieve the comment for the table with OID 123456.
1602 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1603 <code class="function">obj_description</code> ( <em class="parameter"><code>object</code></em> <code class="type">oid</code> )
1604 → <code class="returnvalue">text</code>
1607 Returns the comment for a database object specified by its OID alone.
1608 This is <span class="emphasis"><em>deprecated</em></span> since there is no guarantee
1609 that OIDs are unique across different system catalogs; therefore, the
1610 wrong comment might be returned.
1611 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1612 <a id="id-1.5.8.33.8.4.2.2.4.1.1.1" class="indexterm"></a>
1613 <code class="function">shobj_description</code> ( <em class="parameter"><code>object</code></em> <code class="type">oid</code>, <em class="parameter"><code>catalog</code></em> <code class="type">name</code> )
1614 → <code class="returnvalue">text</code>
1617 Returns the comment for a shared database object specified by its OID
1618 and the name of the containing system catalog. This is just
1619 like <code class="function">obj_description</code> except that it is used for
1620 retrieving comments on shared objects (that is, databases, roles, and
1621 tablespaces). Some system catalogs are global to all databases within
1622 each cluster, and the descriptions for objects in them are stored
1624 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-VALIDITY"><div class="titlepage"><div><div><h3 class="title">9.27.7. Data Validity Checking Functions <a href="#FUNCTIONS-INFO-VALIDITY" class="id_link">#</a></h3></div></div></div><p>
1625 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-INFO-VALIDITY-TABLE" title="Table 9.83. Data Validity Checking Functions">Table 9.83</a>
1626 can be helpful for checking validity of proposed input data.
1627 </p><div class="table" id="FUNCTIONS-INFO-VALIDITY-TABLE"><p class="title"><strong>Table 9.83. Data Validity Checking Functions</strong></p><div class="table-contents"><table class="table" summary="Data Validity Checking Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1635 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1636 <a id="id-1.5.8.33.9.3.2.2.1.1.1.1" class="indexterm"></a>
1637 <code class="function">pg_input_is_valid</code> (
1638 <em class="parameter"><code>string</code></em> <code class="type">text</code>,
1639 <em class="parameter"><code>type</code></em> <code class="type">text</code>
1641 → <code class="returnvalue">boolean</code>
1644 Tests whether the given <em class="parameter"><code>string</code></em> is valid
1645 input for the specified data type, returning true or false.
1648 This function will only work as desired if the data type's input
1649 function has been updated to report invalid input as
1650 a <span class="quote">“<span class="quote">soft</span>”</span> error. Otherwise, invalid input will abort
1651 the transaction, just as if the string had been cast to the type
1655 <code class="literal">pg_input_is_valid('42', 'integer')</code>
1656 → <code class="returnvalue">t</code>
1659 <code class="literal">pg_input_is_valid('42000000000', 'integer')</code>
1660 → <code class="returnvalue">f</code>
1663 <code class="literal">pg_input_is_valid('1234.567', 'numeric(7,4)')</code>
1664 → <code class="returnvalue">f</code>
1665 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1666 <a id="id-1.5.8.33.9.3.2.2.2.1.1.1" class="indexterm"></a>
1667 <code class="function">pg_input_error_info</code> (
1668 <em class="parameter"><code>string</code></em> <code class="type">text</code>,
1669 <em class="parameter"><code>type</code></em> <code class="type">text</code>
1671 → <code class="returnvalue">record</code>
1672 ( <em class="parameter"><code>message</code></em> <code class="type">text</code>,
1673 <em class="parameter"><code>detail</code></em> <code class="type">text</code>,
1674 <em class="parameter"><code>hint</code></em> <code class="type">text</code>,
1675 <em class="parameter"><code>sql_error_code</code></em> <code class="type">text</code> )
1678 Tests whether the given <em class="parameter"><code>string</code></em> is valid
1679 input for the specified data type; if not, return the details of
1680 the error that would have been thrown. If the input is valid, the
1681 results are NULL. The inputs are the same as
1682 for <code class="function">pg_input_is_valid</code>.
1685 This function will only work as desired if the data type's input
1686 function has been updated to report invalid input as
1687 a <span class="quote">“<span class="quote">soft</span>”</span> error. Otherwise, invalid input will abort
1688 the transaction, just as if the string had been cast to the type
1692 <code class="literal">SELECT * FROM pg_input_error_info('42000000000', 'integer')</code>
1693 → <code class="returnvalue"></code>
1694 </p><pre class="programlisting">
1695 message | detail | hint | sql_error_code
1696 ------------------------------------------------------+--------+------+----------------
1697 value "42000000000" is out of range for type integer | | | 22003
1699 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-SNAPSHOT"><div class="titlepage"><div><div><h3 class="title">9.27.8. Transaction ID and Snapshot Information Functions <a href="#FUNCTIONS-INFO-SNAPSHOT" class="id_link">#</a></h3></div></div></div><p>
1700 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-PG-SNAPSHOT" title="Table 9.84. Transaction ID and Snapshot Information Functions">Table 9.84</a>
1701 provide server transaction information in an exportable form. The main
1702 use of these functions is to determine which transactions were committed
1703 between two snapshots.
1704 </p><div class="table" id="FUNCTIONS-PG-SNAPSHOT"><p class="title"><strong>Table 9.84. Transaction ID and Snapshot Information Functions</strong></p><div class="table-contents"><table class="table" summary="Transaction ID and Snapshot Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1709 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1710 <a id="id-1.5.8.33.10.3.2.2.1.1.1.1" class="indexterm"></a>
1711 <code class="function">age</code> ( <code class="type">xid</code> )
1712 → <code class="returnvalue">integer</code>
1715 Returns the number of transactions between the supplied
1716 transaction id and the current transaction counter.
1717 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1718 <a id="id-1.5.8.33.10.3.2.2.2.1.1.1" class="indexterm"></a>
1719 <code class="function">mxid_age</code> ( <code class="type">xid</code> )
1720 → <code class="returnvalue">integer</code>
1723 Returns the number of multixacts IDs between the supplied
1724 multixact ID and the current multixacts counter.
1725 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1726 <a id="id-1.5.8.33.10.3.2.2.3.1.1.1" class="indexterm"></a>
1727 <code class="function">pg_current_xact_id</code> ()
1728 → <code class="returnvalue">xid8</code>
1731 Returns the current transaction's ID. It will assign a new one if the
1732 current transaction does not have one already (because it has not
1733 performed any database updates); see <a class="xref" href="transaction-id.html" title="67.1. Transactions and Identifiers">Section 67.1</a> for details. If executed in a
1734 subtransaction, this will return the top-level transaction ID;
1735 see <a class="xref" href="subxacts.html" title="67.3. Subtransactions">Section 67.3</a> for details.
1736 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1737 <a id="id-1.5.8.33.10.3.2.2.4.1.1.1" class="indexterm"></a>
1738 <code class="function">pg_current_xact_id_if_assigned</code> ()
1739 → <code class="returnvalue">xid8</code>
1742 Returns the current transaction's ID, or <code class="literal">NULL</code> if no
1743 ID is assigned yet. (It's best to use this variant if the transaction
1744 might otherwise be read-only, to avoid unnecessary consumption of an
1746 If executed in a subtransaction, this will return the top-level
1748 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1749 <a id="id-1.5.8.33.10.3.2.2.5.1.1.1" class="indexterm"></a>
1750 <code class="function">pg_xact_status</code> ( <code class="type">xid8</code> )
1751 → <code class="returnvalue">text</code>
1754 Reports the commit status of a recent transaction.
1755 The result is one of <code class="literal">in progress</code>,
1756 <code class="literal">committed</code>, or <code class="literal">aborted</code>,
1757 provided that the transaction is recent enough that the system retains
1758 the commit status of that transaction.
1759 If it is old enough that no references to the transaction survive in
1760 the system and the commit status information has been discarded, the
1761 result is <code class="literal">NULL</code>.
1762 Applications might use this function, for example, to determine
1763 whether their transaction committed or aborted after the application
1764 and database server become disconnected while
1765 a <code class="literal">COMMIT</code> is in progress.
1766 Note that prepared transactions are reported as <code class="literal">in
1767 progress</code>; applications must check <a class="link" href="view-pg-prepared-xacts.html" title="53.17. pg_prepared_xacts"><code class="structname">pg_prepared_xacts</code></a>
1768 if they need to determine whether a transaction ID belongs to a
1769 prepared transaction.
1770 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1771 <a id="id-1.5.8.33.10.3.2.2.6.1.1.1" class="indexterm"></a>
1772 <code class="function">pg_current_snapshot</code> ()
1773 → <code class="returnvalue">pg_snapshot</code>
1776 Returns a current <em class="firstterm">snapshot</em>, a data structure
1777 showing which transaction IDs are now in-progress.
1778 Only top-level transaction IDs are included in the snapshot;
1779 subtransaction IDs are not shown; see <a class="xref" href="subxacts.html" title="67.3. Subtransactions">Section 67.3</a>
1781 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1782 <a id="id-1.5.8.33.10.3.2.2.7.1.1.1" class="indexterm"></a>
1783 <code class="function">pg_snapshot_xip</code> ( <code class="type">pg_snapshot</code> )
1784 → <code class="returnvalue">setof xid8</code>
1787 Returns the set of in-progress transaction IDs contained in a snapshot.
1788 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1789 <a id="id-1.5.8.33.10.3.2.2.8.1.1.1" class="indexterm"></a>
1790 <code class="function">pg_snapshot_xmax</code> ( <code class="type">pg_snapshot</code> )
1791 → <code class="returnvalue">xid8</code>
1794 Returns the <code class="structfield">xmax</code> of a snapshot.
1795 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1796 <a id="id-1.5.8.33.10.3.2.2.9.1.1.1" class="indexterm"></a>
1797 <code class="function">pg_snapshot_xmin</code> ( <code class="type">pg_snapshot</code> )
1798 → <code class="returnvalue">xid8</code>
1801 Returns the <code class="structfield">xmin</code> of a snapshot.
1802 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1803 <a id="id-1.5.8.33.10.3.2.2.10.1.1.1" class="indexterm"></a>
1804 <code class="function">pg_visible_in_snapshot</code> ( <code class="type">xid8</code>, <code class="type">pg_snapshot</code> )
1805 → <code class="returnvalue">boolean</code>
1808 Is the given transaction ID <em class="firstterm">visible</em> according
1809 to this snapshot (that is, was it completed before the snapshot was
1810 taken)? Note that this function will not give the correct answer for
1811 a subtransaction ID (subxid); see <a class="xref" href="subxacts.html" title="67.3. Subtransactions">Section 67.3</a> for
1813 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1814 <a id="id-1.5.8.33.10.3.2.2.11.1.1.1" class="indexterm"></a>
1815 <code class="function">pg_get_multixact_members</code> ( <em class="parameter"><code>multixid</code></em> <code class="type">xid</code> )
1816 → <code class="returnvalue">setof record</code>
1817 ( <em class="parameter"><code>xid</code></em> <code class="type">xid</code>,
1818 <em class="parameter"><code>mode</code></em> <code class="type">text</code> )
1821 Returns the transaction ID and lock mode for each member of the
1822 specified multixact ID. The lock modes <code class="literal">forupd</code>,
1823 <code class="literal">fornokeyupd</code>, <code class="literal">sh</code>, and
1824 <code class="literal">keysh</code> correspond to the row-level locks
1825 <code class="literal">FOR UPDATE</code>, <code class="literal">FOR NO KEY UPDATE</code>,
1826 <code class="literal">FOR SHARE</code>, and <code class="literal">FOR KEY SHARE</code>,
1827 respectively, as described in <a class="xref" href="explicit-locking.html#LOCKING-ROWS" title="13.3.2. Row-Level Locks">Section 13.3.2</a>. Two
1828 additional modes are specific to multixacts:
1829 <code class="literal">nokeyupd</code>, used by updates that do not modify key
1830 columns, and <code class="literal">upd</code>, used by updates or deletes that
1832 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
1833 The internal transaction ID type <code class="type">xid</code> is 32 bits wide and
1834 wraps around every 4 billion transactions. However,
1835 the functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-PG-SNAPSHOT" title="Table 9.84. Transaction ID and Snapshot Information Functions">Table 9.84</a>, except
1836 <code class="function">age</code>, <code class="function">mxid_age</code>, and
1837 <code class="function">pg_get_multixact_members</code>, use a
1838 64-bit type <code class="type">xid8</code> that does not wrap around during the life
1839 of an installation and can be converted to <code class="type">xid</code> by casting if
1840 required; see <a class="xref" href="transaction-id.html" title="67.1. Transactions and Identifiers">Section 67.1</a> for details.
1841 The data type <code class="type">pg_snapshot</code> stores information about
1842 transaction ID visibility at a particular moment in time. Its components
1843 are described in <a class="xref" href="functions-info.html#FUNCTIONS-PG-SNAPSHOT-PARTS" title="Table 9.85. Snapshot Components">Table 9.85</a>.
1844 <code class="type">pg_snapshot</code>'s textual representation is
1845 <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>.
1846 For example <code class="literal">10:20:10,14,15</code> means
1847 <code class="literal">xmin=10, xmax=20, xip_list=10, 14, 15</code>.
1848 </p><div class="table" id="FUNCTIONS-PG-SNAPSHOT-PARTS"><p class="title"><strong>Table 9.85. 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="structfield">xmin</code></td><td>
1849 Lowest transaction ID that was still active. All transaction IDs
1850 less than <code class="structfield">xmin</code> are either committed and visible,
1851 or rolled back and dead.
1852 </td></tr><tr><td><code class="structfield">xmax</code></td><td>
1853 One past the highest completed transaction ID. All transaction IDs
1854 greater than or equal to <code class="structfield">xmax</code> had not yet
1855 completed as of the time of the snapshot, and thus are invisible.
1856 </td></tr><tr><td><code class="structfield">xip_list</code></td><td>
1857 Transactions in progress at the time of the snapshot. A transaction
1858 ID that is <code class="literal">xmin <= <em class="replaceable"><code>X</code></em> <
1859 xmax</code> and not in this list was already completed at the time
1860 of the snapshot, and thus is either visible or dead according to its
1861 commit status. This list does not include the transaction IDs of
1862 subtransactions (subxids).
1863 </td></tr></tbody></table></div></div><br class="table-break" /><p>
1864 In releases of <span class="productname">PostgreSQL</span> before 13 there was
1865 no <code class="type">xid8</code> type, so variants of these functions were provided
1866 that used <code class="type">bigint</code> to represent a 64-bit XID, with a
1867 correspondingly distinct snapshot data type <code class="type">txid_snapshot</code>.
1868 These older functions have <code class="literal">txid</code> in their names. They
1869 are still supported for backward compatibility, but may be removed from a
1870 future release. See <a class="xref" href="functions-info.html#FUNCTIONS-TXID-SNAPSHOT" title="Table 9.86. Deprecated Transaction ID and Snapshot Information Functions">Table 9.86</a>.
1871 </p><div class="table" id="FUNCTIONS-TXID-SNAPSHOT"><p class="title"><strong>Table 9.86. Deprecated Transaction ID and Snapshot Information Functions</strong></p><div class="table-contents"><table class="table" summary="Deprecated Transaction ID and Snapshot Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1876 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1877 <a id="id-1.5.8.33.10.7.2.2.1.1.1.1" class="indexterm"></a>
1878 <code class="function">txid_current</code> ()
1879 → <code class="returnvalue">bigint</code>
1882 See <code class="function">pg_current_xact_id()</code>.
1883 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1884 <a id="id-1.5.8.33.10.7.2.2.2.1.1.1" class="indexterm"></a>
1885 <code class="function">txid_current_if_assigned</code> ()
1886 → <code class="returnvalue">bigint</code>
1889 See <code class="function">pg_current_xact_id_if_assigned()</code>.
1890 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1891 <a id="id-1.5.8.33.10.7.2.2.3.1.1.1" class="indexterm"></a>
1892 <code class="function">txid_current_snapshot</code> ()
1893 → <code class="returnvalue">txid_snapshot</code>
1896 See <code class="function">pg_current_snapshot()</code>.
1897 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1898 <a id="id-1.5.8.33.10.7.2.2.4.1.1.1" class="indexterm"></a>
1899 <code class="function">txid_snapshot_xip</code> ( <code class="type">txid_snapshot</code> )
1900 → <code class="returnvalue">setof bigint</code>
1903 See <code class="function">pg_snapshot_xip()</code>.
1904 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1905 <a id="id-1.5.8.33.10.7.2.2.5.1.1.1" class="indexterm"></a>
1906 <code class="function">txid_snapshot_xmax</code> ( <code class="type">txid_snapshot</code> )
1907 → <code class="returnvalue">bigint</code>
1910 See <code class="function">pg_snapshot_xmax()</code>.
1911 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1912 <a id="id-1.5.8.33.10.7.2.2.6.1.1.1" class="indexterm"></a>
1913 <code class="function">txid_snapshot_xmin</code> ( <code class="type">txid_snapshot</code> )
1914 → <code class="returnvalue">bigint</code>
1917 See <code class="function">pg_snapshot_xmin()</code>.
1918 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1919 <a id="id-1.5.8.33.10.7.2.2.7.1.1.1" class="indexterm"></a>
1920 <code class="function">txid_visible_in_snapshot</code> ( <code class="type">bigint</code>, <code class="type">txid_snapshot</code> )
1921 → <code class="returnvalue">boolean</code>
1924 See <code class="function">pg_visible_in_snapshot()</code>.
1925 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1926 <a id="id-1.5.8.33.10.7.2.2.8.1.1.1" class="indexterm"></a>
1927 <code class="function">txid_status</code> ( <code class="type">bigint</code> )
1928 → <code class="returnvalue">text</code>
1931 See <code class="function">pg_xact_status()</code>.
1932 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-COMMIT-TIMESTAMP"><div class="titlepage"><div><div><h3 class="title">9.27.9. Committed Transaction Information Functions <a href="#FUNCTIONS-INFO-COMMIT-TIMESTAMP" class="id_link">#</a></h3></div></div></div><p>
1933 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-COMMIT-TIMESTAMP" title="Table 9.87. Committed Transaction Information Functions">Table 9.87</a>
1934 provide information about when past transactions were committed.
1935 They only provide useful data when the
1936 <a class="xref" href="runtime-config-replication.html#GUC-TRACK-COMMIT-TIMESTAMP">track_commit_timestamp</a> configuration option is
1937 enabled, and only for transactions that were committed after it was
1938 enabled. Commit timestamp information is routinely removed during
1940 </p><div class="table" id="FUNCTIONS-COMMIT-TIMESTAMP"><p class="title"><strong>Table 9.87. Committed Transaction Information Functions</strong></p><div class="table-contents"><table class="table" summary="Committed Transaction Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1945 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1946 <a id="id-1.5.8.33.11.3.2.2.1.1.1.1" class="indexterm"></a>
1947 <code class="function">pg_xact_commit_timestamp</code> ( <code class="type">xid</code> )
1948 → <code class="returnvalue">timestamp with time zone</code>
1951 Returns the commit timestamp of a transaction.
1952 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1953 <a id="id-1.5.8.33.11.3.2.2.2.1.1.1" class="indexterm"></a>
1954 <code class="function">pg_xact_commit_timestamp_origin</code> ( <code class="type">xid</code> )
1955 → <code class="returnvalue">record</code>
1956 ( <em class="parameter"><code>timestamp</code></em> <code class="type">timestamp with time zone</code>,
1957 <em class="parameter"><code>roident</code></em> <code class="type">oid</code>)
1960 Returns the commit timestamp and replication origin of a transaction.
1961 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1962 <a id="id-1.5.8.33.11.3.2.2.3.1.1.1" class="indexterm"></a>
1963 <code class="function">pg_last_committed_xact</code> ()
1964 → <code class="returnvalue">record</code>
1965 ( <em class="parameter"><code>xid</code></em> <code class="type">xid</code>,
1966 <em class="parameter"><code>timestamp</code></em> <code class="type">timestamp with time zone</code>,
1967 <em class="parameter"><code>roident</code></em> <code class="type">oid</code> )
1970 Returns the transaction ID, commit timestamp and replication origin
1971 of the latest committed transaction.
1972 </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-CONTROLDATA"><div class="titlepage"><div><div><h3 class="title">9.27.10. Control Data Functions <a href="#FUNCTIONS-INFO-CONTROLDATA" class="id_link">#</a></h3></div></div></div><p>
1973 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-CONTROLDATA" title="Table 9.88. Control Data Functions">Table 9.88</a>
1974 print information initialized during <code class="command">initdb</code>, such
1975 as the catalog version. They also show information about write-ahead
1976 logging and checkpoint processing. This information is cluster-wide,
1977 not specific to any one database. These functions provide most of the same
1978 information, from the same source, as the
1979 <a class="xref" href="app-pgcontroldata.html" title="pg_controldata"><span class="refentrytitle"><span class="application">pg_controldata</span></span></a> application.
1980 </p><div class="table" id="FUNCTIONS-CONTROLDATA"><p class="title"><strong>Table 9.88. Control Data Functions</strong></p><div class="table-contents"><table class="table" summary="Control Data Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
1985 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
1986 <a id="id-1.5.8.33.12.3.2.2.1.1.1.1" class="indexterm"></a>
1987 <code class="function">pg_control_checkpoint</code> ()
1988 → <code class="returnvalue">record</code>
1991 Returns information about current checkpoint state, as shown in
1992 <a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-CHECKPOINT" title="Table 9.89. pg_control_checkpoint Output Columns">Table 9.89</a>.
1993 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
1994 <a id="id-1.5.8.33.12.3.2.2.2.1.1.1" class="indexterm"></a>
1995 <code class="function">pg_control_system</code> ()
1996 → <code class="returnvalue">record</code>
1999 Returns information about current control file state, as shown in
2000 <a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-SYSTEM" title="Table 9.90. pg_control_system Output Columns">Table 9.90</a>.
2001 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2002 <a id="id-1.5.8.33.12.3.2.2.3.1.1.1" class="indexterm"></a>
2003 <code class="function">pg_control_init</code> ()
2004 → <code class="returnvalue">record</code>
2007 Returns information about cluster initialization state, as shown in
2008 <a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-INIT" title="Table 9.91. pg_control_init Output Columns">Table 9.91</a>.
2009 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2010 <a id="id-1.5.8.33.12.3.2.2.4.1.1.1" class="indexterm"></a>
2011 <code class="function">pg_control_recovery</code> ()
2012 → <code class="returnvalue">record</code>
2015 Returns information about recovery state, as shown in
2016 <a class="xref" href="functions-info.html#FUNCTIONS-PG-CONTROL-RECOVERY" title="Table 9.92. pg_control_recovery Output Columns">Table 9.92</a>.
2017 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-PG-CONTROL-CHECKPOINT"><p class="title"><strong>Table 9.89. <code class="function">pg_control_checkpoint</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_checkpoint Output Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="structfield">checkpoint_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="structfield">redo_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="structfield">redo_wal_file</code></td><td><code class="type">text</code></td></tr><tr><td><code class="structfield">timeline_id</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">prev_timeline_id</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">full_page_writes</code></td><td><code class="type">boolean</code></td></tr><tr><td><code class="structfield">next_xid</code></td><td><code class="type">text</code></td></tr><tr><td><code class="structfield">next_oid</code></td><td><code class="type">oid</code></td></tr><tr><td><code class="structfield">next_multixact_id</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">next_multi_offset</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">oldest_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">oldest_xid_dbid</code></td><td><code class="type">oid</code></td></tr><tr><td><code class="structfield">oldest_active_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">oldest_multi_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">oldest_multi_dbid</code></td><td><code class="type">oid</code></td></tr><tr><td><code class="structfield">oldest_commit_ts_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">newest_commit_ts_xid</code></td><td><code class="type">xid</code></td></tr><tr><td><code class="structfield">checkpoint_time</code></td><td><code class="type">timestamp with time zone</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-PG-CONTROL-SYSTEM"><p class="title"><strong>Table 9.90. <code class="function">pg_control_system</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_system Output Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="structfield">pg_control_version</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">catalog_version_no</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">system_identifier</code></td><td><code class="type">bigint</code></td></tr><tr><td><code class="structfield">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" /><div class="table" id="FUNCTIONS-PG-CONTROL-INIT"><p class="title"><strong>Table 9.91. <code class="function">pg_control_init</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_init Output Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="structfield">max_data_alignment</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">database_block_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">blocks_per_segment</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">wal_block_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">bytes_per_wal_segment</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">max_identifier_length</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">max_index_columns</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">max_toast_chunk_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">large_object_chunk_size</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">float8_pass_by_value</code></td><td><code class="type">boolean</code></td></tr><tr><td><code class="structfield">data_page_checksum_version</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">default_char_signedness</code></td><td><code class="type">boolean</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-PG-CONTROL-RECOVERY"><p class="title"><strong>Table 9.92. <code class="function">pg_control_recovery</code> Output Columns</strong></p><div class="table-contents"><table class="table" summary="pg_control_recovery Output Columns" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Column Name</th><th>Data Type</th></tr></thead><tbody><tr><td><code class="structfield">min_recovery_end_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="structfield">min_recovery_end_timeline</code></td><td><code class="type">integer</code></td></tr><tr><td><code class="structfield">backup_start_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="structfield">backup_end_lsn</code></td><td><code class="type">pg_lsn</code></td></tr><tr><td><code class="structfield">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="sect2" id="FUNCTIONS-INFO-VERSION"><div class="titlepage"><div><div><h3 class="title">9.27.11. Version Information Functions <a href="#FUNCTIONS-INFO-VERSION" class="id_link">#</a></h3></div></div></div><p>
2018 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-VERSION" title="Table 9.93. Version Information Functions">Table 9.93</a>
2019 print version information.
2020 </p><div class="table" id="FUNCTIONS-VERSION"><p class="title"><strong>Table 9.93. Version Information Functions</strong></p><div class="table-contents"><table class="table" summary="Version Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
2025 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
2026 <a id="id-1.5.8.33.13.3.2.2.1.1.1.1" class="indexterm"></a>
2027 <code class="function">version</code> ()
2028 → <code class="returnvalue">text</code>
2031 Returns a string describing the <span class="productname">PostgreSQL</span>
2032 server's version. You can also get this information from
2033 <a class="xref" href="runtime-config-preset.html#GUC-SERVER-VERSION">server_version</a>, or for a machine-readable
2034 version use <a class="xref" href="runtime-config-preset.html#GUC-SERVER-VERSION-NUM">server_version_num</a>. Software
2035 developers should use <code class="varname">server_version_num</code> (available
2036 since 8.2) or <a class="xref" href="libpq-status.html#LIBPQ-PQSERVERVERSION"><code class="function">PQserverVersion</code></a> instead of
2037 parsing the text version.
2038 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2039 <a id="id-1.5.8.33.13.3.2.2.2.1.1.1" class="indexterm"></a>
2040 <code class="function">unicode_version</code> ()
2041 → <code class="returnvalue">text</code>
2044 Returns a string representing the version of Unicode used by
2045 <span class="productname">PostgreSQL</span>.
2046 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2047 <a id="id-1.5.8.33.13.3.2.2.3.1.1.1" class="indexterm"></a>
2048 <code class="function">icu_unicode_version</code> ()
2049 → <code class="returnvalue">text</code>
2052 Returns a string representing the version of Unicode used by ICU, if
2053 the server was built with ICU support; otherwise returns
2054 <code class="literal">NULL</code> </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="FUNCTIONS-INFO-WAL-SUMMARY"><div class="titlepage"><div><div><h3 class="title">9.27.12. WAL Summarization Information Functions <a href="#FUNCTIONS-INFO-WAL-SUMMARY" class="id_link">#</a></h3></div></div></div><p>
2055 The functions shown in <a class="xref" href="functions-info.html#FUNCTIONS-WAL-SUMMARY" title="Table 9.94. WAL Summarization Information Functions">Table 9.94</a>
2056 print information about the status of WAL summarization.
2057 See <a class="xref" href="runtime-config-wal.html#GUC-SUMMARIZE-WAL">summarize_wal</a>.
2058 </p><div class="table" id="FUNCTIONS-WAL-SUMMARY"><p class="title"><strong>Table 9.94. WAL Summarization Information Functions</strong></p><div class="table-contents"><table class="table" summary="WAL Summarization Information Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
2063 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
2064 <a id="id-1.5.8.33.14.3.2.2.1.1.1.1" class="indexterm"></a>
2065 <code class="function">pg_available_wal_summaries</code> ()
2066 → <code class="returnvalue">setof record</code>
2067 ( <em class="parameter"><code>tli</code></em> <code class="type">bigint</code>,
2068 <em class="parameter"><code>start_lsn</code></em> <code class="type">pg_lsn</code>,
2069 <em class="parameter"><code>end_lsn</code></em> <code class="type">pg_lsn</code> )
2072 Returns information about the WAL summary files present in the
2073 data directory, under <code class="literal">pg_wal/summaries</code>.
2074 One row will be returned per WAL summary file. Each file summarizes
2075 WAL on the indicated TLI within the indicated LSN range. This function
2076 might be useful to determine whether enough WAL summaries are present
2077 on the server to take an incremental backup based on some prior
2078 backup whose start LSN is known.
2079 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2080 <a id="id-1.5.8.33.14.3.2.2.2.1.1.1" class="indexterm"></a>
2081 <code class="function">pg_wal_summary_contents</code> ( <em class="parameter"><code>tli</code></em> <code class="type">bigint</code>, <em class="parameter"><code>start_lsn</code></em> <code class="type">pg_lsn</code>, <em class="parameter"><code>end_lsn</code></em> <code class="type">pg_lsn</code> )
2082 → <code class="returnvalue">setof record</code>
2083 ( <em class="parameter"><code>relfilenode</code></em> <code class="type">oid</code>,
2084 <em class="parameter"><code>reltablespace</code></em> <code class="type">oid</code>,
2085 <em class="parameter"><code>reldatabase</code></em> <code class="type">oid</code>,
2086 <em class="parameter"><code>relforknumber</code></em> <code class="type">smallint</code>,
2087 <em class="parameter"><code>relblocknumber</code></em> <code class="type">bigint</code>,
2088 <em class="parameter"><code>is_limit_block</code></em> <code class="type">boolean</code> )
2091 Returns one information about the contents of a single WAL summary file
2092 identified by TLI and starting and ending LSNs. Each row with
2093 <code class="literal">is_limit_block</code> false indicates that the block
2094 identified by the remaining output columns was modified by at least
2095 one WAL record within the range of records summarized by this file.
2096 Each row with <code class="literal">is_limit_block</code> true indicates either
2097 that (a) the relation fork was truncated to the length given by
2098 <code class="literal">relblocknumber</code> within the relevant range of WAL
2099 records or (b) that the relation fork was created or dropped within
2100 the relevant range of WAL records; in such cases,
2101 <code class="literal">relblocknumber</code> will be zero.
2102 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
2103 <a id="id-1.5.8.33.14.3.2.2.3.1.1.1" class="indexterm"></a>
2104 <code class="function">pg_get_wal_summarizer_state</code> ()
2105 → <code class="returnvalue">record</code>
2106 ( <em class="parameter"><code>summarized_tli</code></em> <code class="type">bigint</code>,
2107 <em class="parameter"><code>summarized_lsn</code></em> <code class="type">pg_lsn</code>,
2108 <em class="parameter"><code>pending_lsn</code></em> <code class="type">pg_lsn</code>,
2109 <em class="parameter"><code>summarizer_pid</code></em> <code class="type">int</code> )
2112 Returns information about the progress of the WAL summarizer. If the
2113 WAL summarizer has never run since the instance was started, then
2114 <code class="literal">summarized_tli</code> and <code class="literal">summarized_lsn</code>
2115 will be <code class="literal">0</code> and <code class="literal">0/0</code> respectively;
2116 otherwise, they will be the TLI and ending LSN of the last WAL summary
2117 file written to disk. If the WAL summarizer is currently running,
2118 <code class="literal">pending_lsn</code> will be the ending LSN of the last
2119 record that it has consumed, which must always be greater than or
2120 equal to <code class="literal">summarized_lsn</code>; if the WAL summarizer is
2121 not running, it will be equal to <code class="literal">summarized_lsn</code>.
2122 <code class="literal">summarizer_pid</code> is the PID of the WAL summarizer
2123 process, if it is running, and otherwise NULL.
2126 As a special exception, the WAL summarizer will refuse to generate
2127 WAL summary files if run on WAL generated under
2128 <code class="literal">wal_level=minimal</code>, since such summaries would be
2129 unsafe to use as the basis for an incremental backup. In this case,
2130 the fields above will continue to advance as if summaries were being
2131 generated, but nothing will be written to disk. Once the summarizer
2132 reaches WAL generated while <code class="literal">wal_level</code> was set
2133 to <code class="literal">replica</code> or higher, it will resume writing
2135 </p></td></tr></tbody></table></div></div><br class="table-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-srf.html" title="9.26. Set Returning Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-admin.html" title="9.28. System Administration Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.26. Set Returning Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.28. System Administration Functions</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob