1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>9.19.System Information Functions</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="prev" href="functions-srf.html" title="9.18.Set Returning Functions">
<link rel="next" href="functions-admin.html" title="9.20.System Administration Functions">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-info"></a>9.19.System Information Functions</h2></div></div></div>
<p> <a href="functions-info.html#functions-info-session-table" title="Table9.39.Session Information Functions">Table9.39, “Session Information Functions”</a> shows several
functions that extract session and system information.
</p>
<div class="table">
<a name="functions-info-session-table"></a><p class="title"><b>Table9.39.Session Information Functions</b></p>
<div class="table-contents"><table summary="Session Information Functions" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Name</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td><code class="literal"><code class="function">current_database</code>()</code></td>
<td><code class="type">name</code></td>
<td>name of current database</td>
</tr>
<tr>
<td><code class="literal"><code class="function">current_schema</code>()</code></td>
<td><code class="type">name</code></td>
<td>name of current schema</td>
</tr>
<tr>
<td><code class="literal"><code class="function">current_schemas</code>(<code class="type">boolean</code>)</code></td>
<td><code class="type">name[]</code></td>
<td>names of schemas in search path optionally including implicit schemas</td>
</tr>
<tr>
<td><code class="literal"><code class="function">current_user</code></code></td>
<td><code class="type">name</code></td>
<td>user name of current execution context</td>
</tr>
<tr>
<td><code class="literal"><code class="function">inet_client_addr</code>()</code></td>
<td><code class="type">inet</code></td>
<td>address of the remote connection</td>
</tr>
<tr>
<td><code class="literal"><code class="function">inet_client_port</code>()</code></td>
<td><code class="type">int</code></td>
<td>port of the remote connection</td>
</tr>
<tr>
<td><code class="literal"><code class="function">inet_server_addr</code>()</code></td>
<td><code class="type">inet</code></td>
<td>address of the local connection</td>
</tr>
<tr>
<td><code class="literal"><code class="function">inet_server_port</code>()</code></td>
<td><code class="type">int</code></td>
<td>port of the local connection</td>
</tr>
<tr>
<td><code class="literal"><code class="function">session_user</code></code></td>
<td><code class="type">name</code></td>
<td>session user name</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_postmaster_start_time</code>()</code></td>
<td><code class="type">timestamp with time zone</code></td>
<td>
<code class="command">postmaster</code> start time</td>
</tr>
<tr>
<td><code class="literal"><code class="function">user</code></code></td>
<td><code class="type">name</code></td>
<td>equivalent to <code class="function">current_user</code>
</td>
</tr>
<tr>
<td><code class="literal"><code class="function">version</code>()</code></td>
<td><code class="type">text</code></td>
<td>
<span class="productname">PostgreSQL</span> version information</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><a name="id621710"></a><a name="id621723"></a><a name="id621736"></a><p> The <code class="function">session_user</code> is normally the user who initiated
the current database connection; but superusers can change this setting
with <a href="sql-set-session-authorization.html">SET SESSION AUTHORIZATION</a>.
The <code class="function">current_user</code> is the user identifier
that is applicable for permission checking. Normally, it is equal
to the session user, but it can be changed with
<a href="sql-set-role.html">SET ROLE</a>.
It also changes during the execution of
functions with the attribute <code class="literal">SECURITY DEFINER</code>.
In Unix parlance, the session user is the “<span class="quote">real user</span>” and
the current user is the “<span class="quote">effective user</span>”.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> <code class="function">current_user</code>, <code class="function">session_user</code>, and
<code class="function">user</code> have special syntactic status in <acronym class="acronym">SQL</acronym>:
they must be called without trailing parentheses.
</p>
</div>
<p> <code class="function">current_schema</code> returns the name of the schema that is
at the front of the search path (or a null value if the search path is
empty). This is the schema that will be used for any tables or
other named objects that are created without specifying a target schema.
<code class="function">current_schemas(boolean)</code> returns an array of the names of all
schemas presently in the search path. The Boolean option determines whether or not
implicitly included system schemas such as <code class="literal">pg_catalog</code> are included in the search
path returned.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> The search path may be altered at run time. The command is:
</p>
<pre class="programlisting">SET search_path TO <em class="replaceable"><code>schema</code></em> [<span class="optional">, <em class="replaceable"><code>schema</code></em>, ...</span>]</pre>
<p>
</p>
</div>
<a name="id621898"></a><a name="id621909"></a><a name="id621919"></a><a name="id621929"></a><p> <code class="function">inet_client_addr</code> returns the IP address of the
current client, and <code class="function">inet_client_port</code> returns the
port number.
<code class="function">inet_server_addr</code> returns the IP address on which
the server accepted the current connection, and
<code class="function">inet_server_port</code> returns the port number.
All these functions return NULL if the current connection is via a
Unix-domain socket.
</p>
<a name="id621974"></a><p> <code class="function">pg_postmaster_start_time</code> returns the
<code class="type">timestamp with time zone</code> when the
<code class="command">postmaster</code> started.
</p>
<a name="id622006"></a><p> <code class="function">version</code> returns a string describing the
<span class="productname">PostgreSQL</span> server's version.
</p>
<a name="id622032"></a><p> <a href="functions-info.html#functions-info-access-table" title="Table9.40.Access Privilege Inquiry Functions">Table9.40, “Access Privilege Inquiry Functions”</a> lists functions that
allow the user to query object access privileges programmatically.
See <a href="ddl-priv.html" title="5.6.Privileges">Section5.6, “Privileges”</a> for more information about
privileges.
</p>
<div class="table">
<a name="functions-info-access-table"></a><p class="title"><b>Table9.40.Access Privilege Inquiry Functions</b></p>
<div class="table-contents"><table summary="Access Privilege Inquiry Functions" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Name</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td>
<code class="literal"><code class="function">has_table_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for table</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_table_privilege</code>(<em class="parameter"><code>table</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for table</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_database_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>database</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for database</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_database_privilege</code>(<em class="parameter"><code>database</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for database</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_function_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>function</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for function</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_function_privilege</code>(<em class="parameter"><code>function</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for function</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_language_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>language</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for language</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_language_privilege</code>(<em class="parameter"><code>language</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for language</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_has_role</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>role</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for role</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_has_role</code>(<em class="parameter"><code>role</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for role</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_schema_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>schema</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for schema</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_schema_privilege</code>(<em class="parameter"><code>schema</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for schema</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_tablespace_privilege</code>(<em class="parameter"><code>user</code></em>,
<em class="parameter"><code>tablespace</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does user have privilege for tablespace</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">has_tablespace_privilege</code>(<em class="parameter"><code>tablespace</code></em>,
<em class="parameter"><code>privilege</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>does current user have privilege for tablespace</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><a name="id622617"></a><a name="id622628"></a><a name="id622638"></a><a name="id622648"></a><a name="id622659"></a><a name="id622669"></a><a name="id622679"></a><p> <code class="function">has_table_privilege</code> checks whether a user
can access a table in a particular way. The user can be
specified by name or by OID
(<code class="literal">pg_authid.oid</code>), or if the argument is
omitted
<code class="function">current_user</code> is assumed. The table can be specified
by name or by OID. (Thus, there are actually six variants of
<code class="function">has_table_privilege</code>, which can be distinguished by
the number and types of their arguments.) When specifying by name,
the name can be schema-qualified if necessary.
The desired access privilege type
is specified by a text string, which must evaluate to one of the
values <code class="literal">SELECT</code>, <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>,
<code class="literal">DELETE</code>, <code class="literal">RULE</code>, <code class="literal">REFERENCES</code>, or
<code class="literal">TRIGGER</code>. (Case of the string is not significant, however.)
An example is:
</p>
<pre class="programlisting">SELECT has_table_privilege('myschema.mytable', 'select');</pre>
<p>
</p>
<p> <code class="function">has_database_privilege</code> checks whether a user
can access a database in a particular way. The possibilities for its
arguments are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">CREATE</code>,
<code class="literal">TEMPORARY</code>, or
<code class="literal">TEMP</code> (which is equivalent to
<code class="literal">TEMPORARY</code>).
</p>
<p> <code class="function">has_function_privilege</code> checks whether a user
can access a function in a particular way. The possibilities for its
arguments are analogous to <code class="function">has_table_privilege</code>.
When specifying a function by a text string rather than by OID,
the allowed input is the same as for the <code class="type">regprocedure</code> data type
(see <a href="datatype-oid.html" title="8.12.Object Identifier Types">Section8.12, “Object Identifier Types”</a>).
The desired access privilege type must evaluate to
<code class="literal">EXECUTE</code>.
An example is:
</p>
<pre class="programlisting">SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');</pre>
<p>
</p>
<p> <code class="function">has_language_privilege</code> checks whether a user
can access a procedural language in a particular way. The possibilities
for its arguments are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">USAGE</code>.
</p>
<p> <code class="function">pg_has_role</code> checks whether a user
can access a role in a particular way. The possibilities for its
arguments are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">MEMBER</code> or
<code class="literal">USAGE</code>.
<code class="literal">MEMBER</code> denotes direct or indirect membership in
the role (that is, the right to do <code class="literal">SET ROLE</code>), while
<code class="literal">USAGE</code> denotes whether the privileges of the role
are immediately available without doing <code class="literal">SET ROLE</code>.
</p>
<p> <code class="function">has_schema_privilege</code> checks whether a user
can access a schema in a particular way. The possibilities for its
arguments are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">CREATE</code> or
<code class="literal">USAGE</code>.
</p>
<p> <code class="function">has_tablespace_privilege</code> checks whether a user
can access a tablespace in a particular way. The possibilities for its
arguments are analogous to <code class="function">has_table_privilege</code>.
The desired access privilege type must evaluate to
<code class="literal">CREATE</code>.
</p>
<p> To test whether a user holds a grant option on the privilege,
append <code class="literal"> WITH GRANT OPTION</code> to the privilege key
word; for example <code class="literal">'UPDATE WITH GRANT OPTION'</code>.
</p>
<p> <a href="functions-info.html#functions-info-schema-table" title="Table9.41.Schema Visibility Inquiry Functions">Table9.41, “Schema Visibility Inquiry Functions”</a> shows functions that
determine whether a certain object is <em class="firstterm">visible</em> in the
current schema search path. A table is said to be visible if its
containing schema is in the search path and no table of the same
name appears earlier in the search path. This is equivalent to the
statement that the table can be referenced by name without explicit
schema qualification. For example, to list the names of all
visible tables:
</p>
<pre class="programlisting">SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);</pre>
<p>
</p>
<div class="table">
<a name="functions-info-schema-table"></a><p class="title"><b>Table9.41.Schema Visibility Inquiry Functions</b></p>
<div class="table-contents"><table summary="Schema Visibility Inquiry Functions" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Name</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td>
<code class="literal"><code class="function">pg_table_is_visible</code>(<em class="parameter"><code>table_oid</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>is table visible in search path</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_type_is_visible</code>(<em class="parameter"><code>type_oid</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>is type (or domain) visible in search path</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_function_is_visible</code>(<em class="parameter"><code>function_oid</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>is function visible in search path</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_operator_is_visible</code>(<em class="parameter"><code>operator_oid</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>is operator visible in search path</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_opclass_is_visible</code>(<em class="parameter"><code>opclass_oid</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>is operator class visible in search path</td>
</tr>
<tr>
<td>
<code class="literal"><code class="function">pg_conversion_is_visible</code>(<em class="parameter"><code>conversion_oid</code></em>)</code>
</td>
<td><code class="type">boolean</code></td>
<td>is conversion visible in search path</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><a name="id623253"></a><a name="id623264"></a><a name="id623274"></a><a name="id623285"></a><a name="id623295"></a><a name="id623306"></a><p> <code class="function">pg_table_is_visible</code> performs the check for
tables (or views, or any other kind of <code class="literal">pg_class</code> entry).
<code class="function">pg_type_is_visible</code>,
<code class="function">pg_function_is_visible</code>,
<code class="function">pg_operator_is_visible</code>,
<code class="function">pg_opclass_is_visible</code>, and
<code class="function">pg_conversion_is_visible</code> perform the same sort of
visibility check for types (and domains), functions, operators, operator classes
and conversions, respectively. For functions and operators, an object in
the search path is visible if there is no object of the same name
<span class="emphasis"><em>and argument data type(s)</em></span> earlier in the path. For
operator classes, both name and associated index access method are
considered.
</p>
<p> All these functions require object OIDs to identify the object to be
checked. If you want to test an object by name, it is convenient to use
the OID alias types (<code class="type">regclass</code>, <code class="type">regtype</code>,
<code class="type">regprocedure</code>, or <code class="type">regoperator</code>), for example
</p>
<pre class="programlisting">SELECT pg_type_is_visible('myschema.widget'::regtype);</pre>
<p>
Note that it would not make much sense to test an unqualified name in
this way [mdash ] if the name can be recognized at all, it must be visible.
</p>
<a name="id623410"></a><a name="id623420"></a><a name="id623430"></a><a name="id623441"></a><a name="id623451"></a><a name="id623462"></a><a name="id623472"></a><a name="id623482"></a><a name="id623492"></a><a name="id623503"></a><p> <a href="functions-info.html#functions-info-catalog-table" title="Table9.42.System Catalog Information Functions">Table9.42, “System Catalog Information Functions”</a> lists functions that
extract information from the system catalogs.
</p>
<div class="table">
<a name="functions-info-catalog-table"></a><p class="title"><b>Table9.42.System Catalog Information Functions</b></p>
<div class="table-contents"><table summary="System Catalog Information Functions" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Name</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td><code class="literal"><code class="function">format_type</code>(<em class="parameter"><code>type_oid</code></em>, <em class="parameter"><code>typemod</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get SQL name of a data type</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_viewdef</code>(<em class="parameter"><code>view_name</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE VIEW</code> command for view (<span class="emphasis"><em>deprecated</em></span>)</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_viewdef</code>(<em class="parameter"><code>view_name</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE VIEW</code> command for view (<span class="emphasis"><em>deprecated</em></span>)</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_viewdef</code>(<em class="parameter"><code>view_oid</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE VIEW</code> command for view</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_viewdef</code>(<em class="parameter"><code>view_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE VIEW</code> command for view</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_ruledef</code>(<em class="parameter"><code>rule_oid</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE RULE</code> command for rule</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_ruledef</code>(<em class="parameter"><code>rule_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE RULE</code> command for rule</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_indexdef</code>(<em class="parameter"><code>index_oid</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE INDEX</code> command for index</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_indexdef</code>(<em class="parameter"><code>index_oid</code></em>, <em class="parameter"><code>column_no</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE INDEX</code> command for index,
or definition of just one index column when
<em class="parameter"><code>column_no</code></em> is not zero</td>
</tr>
<tr>
<td>
<code class="function">pg_get_triggerdef</code>(<em class="parameter"><code>trigger_oid</code></em>)</td>
<td><code class="type">text</code></td>
<td>get <code class="command">CREATE [ CONSTRAINT ] TRIGGER</code> command for trigger</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_constraintdef</code>(<em class="parameter"><code>constraint_oid</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get definition of a constraint</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_constraintdef</code>(<em class="parameter"><code>constraint_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get definition of a constraint</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_expr</code>(<em class="parameter"><code>expr_text</code></em>, <em class="parameter"><code>relation_oid</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>decompile internal form of an expression, assuming that any Vars
in it refer to the relation indicated by the second parameter</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_expr</code>(<em class="parameter"><code>expr_text</code></em>, <em class="parameter"><code>relation_oid</code></em>, <em class="parameter"><code>pretty_bool</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>decompile internal form of an expression, assuming that any Vars
in it refer to the relation indicated by the second parameter</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_userbyid</code>(<em class="parameter"><code>roleid</code></em>)</code></td>
<td><code class="type">name</code></td>
<td>get role name with given ID</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_get_serial_sequence</code>(<em class="parameter"><code>table_name</code></em>, <em class="parameter"><code>column_name</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get name of the sequence that a <code class="type">serial</code> or <code class="type">bigserial</code> column
uses</td>
</tr>
<tr>
<td><code class="literal"><code class="function">pg_tablespace_databases</code>(<em class="parameter"><code>tablespace_oid</code></em>)</code></td>
<td><code class="type">setof oid</code></td>
<td>get the set of database OIDs that have objects in the tablespace</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><p> <code class="function">format_type</code> returns the SQL name of a data type that
is identified by its type OID and possibly a type modifier. Pass NULL
for the type modifier if no specific modifier is known.
</p>
<p> <code class="function">pg_get_viewdef</code>,
<code class="function">pg_get_ruledef</code>,
<code class="function">pg_get_indexdef</code>,
<code class="function">pg_get_triggerdef</code>, and
<code class="function">pg_get_constraintdef</code> respectively
reconstruct the creating command for a view, rule, index, trigger, or
constraint. (Note that this is a decompiled reconstruction, not
the original text of the command.)
<code class="function">pg_get_expr</code> decompiles the internal form of an
individual expression, such as the default value for a column. It
may be useful when examining the contents of system catalogs.
Most of these functions come in two
variants, one of which can optionally “<span class="quote">pretty-print</span>” the result.
The pretty-printed format is more readable, but the default format is more
likely to be
interpreted the same way by future versions of <span class="productname">PostgreSQL</span>;
avoid using pretty-printed output for dump purposes.
Passing <code class="literal">false</code> for the pretty-print parameter yields the
same result as the variant that does not have the parameter at all.
</p>
<p> <code class="function">pg_get_userbyid</code> extracts a role's name given
its OID.
</p>
<p> <code class="function">pg_get_serial_sequence</code> fetches the name of the
sequence associated with a <code class="type">serial</code> or <code class="type">bigserial</code>
column. The name is suitably formatted for passing to the sequence
functions (see <a href="functions-sequence.html" title="9.12.Sequence Manipulation Functions">Section9.12, “Sequence Manipulation Functions”</a>). NULL is
returned if the column does not have an associated sequence.
</p>
<p> <code class="function">pg_tablespace_databases</code> allows a tablespace to
be examined. It returns the set of OIDs of databases that have objects
stored in the tablespace. If this function returns any rows, the
tablespace is not empty and cannot be dropped. To
display the specific objects populating the tablespace, you will need
to connect to the databases identified by
<code class="function">pg_tablespace_databases</code> and query their
<code class="structname">pg_class</code> catalogs.
</p>
<a name="id624291"></a><a name="id624302"></a><a name="id624312"></a><p> The functions shown in <a href="functions-info.html#functions-info-comment-table" title="Table9.43.Comment Information Functions">Table9.43, “Comment Information Functions”</a> extract comments
previously stored with the <code class="command">COMMENT</code> command. A
null value is returned if no comment could be found matching the
specified parameters.
</p>
<div class="table">
<a name="functions-info-comment-table"></a><p class="title"><b>Table9.43.Comment Information Functions</b></p>
<div class="table-contents"><table summary="Comment Information Functions" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Name</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td><code class="literal"><code class="function">obj_description</code>(<em class="parameter"><code>object_oid</code></em>, <em class="parameter"><code>catalog_name</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get comment for a database object</td>
</tr>
<tr>
<td><code class="literal"><code class="function">obj_description</code>(<em class="parameter"><code>object_oid</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get comment for a database object (<span class="emphasis"><em>deprecated</em></span>)</td>
</tr>
<tr>
<td><code class="literal"><code class="function">col_description</code>(<em class="parameter"><code>table_oid</code></em>, <em class="parameter"><code>column_number</code></em>)</code></td>
<td><code class="type">text</code></td>
<td>get comment for a table column</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><p> The two-parameter form of <code class="function">obj_description</code> returns the
comment for a database object specified by its OID and the name of the
containing system catalog. For example,
<code class="literal">obj_description(123456,'pg_class')</code>
would retrieve the comment for a table with OID 123456.
The one-parameter form of <code class="function">obj_description</code> requires only
the object OID. It is now deprecated since there is no guarantee that
OIDs are unique across different system catalogs; therefore, the wrong
comment could be returned.
</p>
<p> <code class="function">col_description</code> returns the comment for a table column,
which is specified by the OID of its table and its column number.
<code class="function">obj_description</code> cannot be used for table columns since
columns do not have OIDs of their own.
</p>
</div></body>
</html>
|
