1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Miscellaneous Functions</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="Functions and Operators"
16 HREF="functions.html"><LINK
18 TITLE="Conditional Expressions"
19 HREF="functions-conditional.html"><LINK
21 TITLE="Array Functions and Operators"
22 HREF="functions-array.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="functions-conditional.html"
67 >Chapter 9. Functions and Operators</TD
81 HREF="functions-array.html"
96 >9.13. Miscellaneous Functions</A
100 HREF="functions-misc.html#FUNCTIONS-MISC-SESSION-TABLE"
103 functions that extract session and system information.
108 NAME="FUNCTIONS-MISC-SESSION-TABLE"
112 >Table 9-35. Session Information Functions</B
117 ><COL><COL><COL><THEAD
132 >current_database()</CODE
140 >name of current database</TD
146 >current_schema()</CODE
154 >name of current schema</TD
160 >current_schemas(boolean)</CODE
168 >names of schemas in search path optionally including implicit schemas</TD
182 >user name of current execution context</TD
196 >session user name</TD
227 >PostgreSQL version information</TD
245 > is the user that initiated a
246 database connection; it is fixed for the duration of that
247 connection. The <CODE
250 > is the user identifier
251 that is applicable for permission checking. Normally, it is equal
252 to the session user, but it changes during the execution of
253 functions with the attribute <TT
255 >SECURITY DEFINER</TT
257 In Unix parlance, the session user is the <SPAN
261 the current user is the <SPAN
263 >"effective user"</SPAN
283 > have special syntactic status in <ACRONYM
287 they must be called without trailing parentheses.
294 >current_schema</CODE
295 > returns the name of the schema that is
296 at the front of the search path (or a null value if the search path is
297 empty). This is the schema that will be used for any tables or
298 other named objects that are created without specifying a target schema.
301 >current_schemas(boolean)</CODE
302 > returns an array of the names of all
303 schemas presently in the search path. The Boolean option determines whether or not
304 implicitly included system schemas such as <TT
307 > are included in the search
317 > The search path may be altered at run time. The command is:
319 CLASS="PROGRAMLISTING"
320 >SET search_path TO <VAR
341 > returns a string describing the
349 HREF="functions-misc.html#FUNCTIONS-MISC-SET-TABLE"
351 > shows the functions
352 available to query and alter run-time configuration parameters.
357 NAME="FUNCTIONS-MISC-SET-TABLE"
361 >Table 9-36. Configuration Settings Functions</B
366 ><COL><COL><COL><THEAD
383 >current_setting</CODE
396 >current value of setting</TD
425 >set parameter and return new value</TD
442 >current_setting</CODE
444 current value of the setting <VAR
448 It corresponds to the <ACRONYM
457 CLASS="PROGRAMLISTING"
458 >SELECT current_setting('datestyle');
486 new value will only apply to the current transaction. If you want
487 the new value to apply for the current session, use
491 > instead. The function corresponds to the
497 CLASS="PROGRAMLISTING"
498 >SELECT set_config('log_statement_stats', 'off', false);
511 HREF="functions-misc.html#FUNCTIONS-MISC-ACCESS-TABLE"
513 > lists functions that
514 allow the user to query object access privileges programmatically.
518 > for more information about
524 NAME="FUNCTIONS-MISC-ACCESS-TABLE"
528 >Table 9-37. Access Privilege Inquiry Functions</B
533 ><COL><COL><COL><THEAD
550 >has_table_privilege</CODE
571 >does user have privilege for table</TD
579 >has_table_privilege</CODE
596 >does current user have privilege for table</TD
604 >has_database_privilege</CODE
625 >does user have privilege for database</TD
633 >has_database_privilege</CODE
650 >does current user have privilege for database</TD
658 >has_function_privilege</CODE
679 >does user have privilege for function</TD
687 >has_function_privilege</CODE
704 >does current user have privilege for function</TD
712 >has_language_privilege</CODE
733 >does user have privilege for language</TD
741 >has_language_privilege</CODE
758 >does current user have privilege for language</TD
766 >has_schema_privilege</CODE
787 >does user have privilege for schema</TD
795 >has_schema_privilege</CODE
812 >does current user have privilege for schema</TD
835 >has_table_privilege</CODE
836 > checks whether a user
837 can access a table in a particular way. The user can be
838 specified by name or by ID
841 >pg_user.usesysid</TT
842 >), or if the argument is
847 > is assumed. The table can be specified
848 by name or by OID. (Thus, there are actually six variants of
851 >has_table_privilege</CODE
852 >, which can be distinguished by
853 the number and types of their arguments.) When specifying by name,
854 the name can be schema-qualified if necessary.
855 The desired access privilege type
856 is specified by a text string, which must evaluate to one of the
880 >. (Case of the string is not significant, however.)
883 CLASS="PROGRAMLISTING"
884 >SELECT has_table_privilege('myschema.mytable', 'select');</PRE
890 >has_database_privilege</CODE
891 > checks whether a user
892 can access a database in a particular way. The possibilities for its
893 arguments are analogous to <CODE
895 >has_table_privilege</CODE
897 The desired access privilege type must evaluate to
909 > (which is equivalent to
918 >has_function_privilege</CODE
919 > checks whether a user
920 can access a function in a particular way. The possibilities for its
921 arguments are analogous to <CODE
923 >has_table_privilege</CODE
925 When specifying a function by a text string rather than by OID,
926 the allowed input is the same as for the <TT
930 The desired access privilege type must currently evaluate to
939 >has_language_privilege</CODE
940 > checks whether a user
941 can access a procedural language in a particular way. The possibilities
942 for its arguments are analogous to <CODE
944 >has_table_privilege</CODE
946 The desired access privilege type must currently evaluate to
955 >has_schema_privilege</CODE
956 > checks whether a user
957 can access a schema in a particular way. The possibilities for its
958 arguments are analogous to <CODE
960 >has_table_privilege</CODE
962 The desired access privilege type must evaluate to
973 > To evaluate whether a user holds a grant option on the privilege,
976 > WITH GRANT OPTION</TT
977 > to the privilege key
978 word; for example <TT
980 >'UPDATE WITH GRANT OPTION'</TT
985 HREF="functions-misc.html#FUNCTIONS-MISC-SCHEMA-TABLE"
987 > shows functions that
988 determine whether a certain object is <I
992 current schema search path. A table is said to be visible if its
993 containing schema is in the search path and no table of the same
994 name appears earlier in the search path. This is equivalent to the
995 statement that the table can be referenced by name without explicit
996 schema qualification. For example, to list the names of all
999 CLASS="PROGRAMLISTING"
1000 >SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);</PRE
1006 NAME="FUNCTIONS-MISC-SCHEMA-TABLE"
1010 >Table 9-38. Schema Visibility Inquiry Functions</B
1015 ><COL><COL><COL><THEAD
1032 >pg_table_is_visible</CODE
1045 >is table visible in search path</TD
1053 >pg_type_is_visible</CODE
1066 >is type (or domain) visible in search path</TD
1074 >pg_function_is_visible</CODE
1087 >is function visible in search path</TD
1095 >pg_operator_is_visible</CODE
1108 >is operator visible in search path</TD
1116 >pg_opclass_is_visible</CODE
1129 >is operator class visible in search path</TD
1137 >pg_conversion_is_visible</CODE
1140 >conversion_oid</VAR
1150 >is conversion visible in search path</TD
1176 >pg_table_is_visible</CODE
1177 > performs the check for
1178 tables (or views, or any other kind of <TT
1184 >pg_type_is_visible</CODE
1188 >pg_function_is_visible</CODE
1192 >pg_operator_is_visible</CODE
1196 >pg_opclass_is_visible</CODE
1200 >pg_conversion_is_visible</CODE
1201 > perform the same sort of
1202 visibility check for types (and domains), functions, operators, operator classes
1203 and conversions, respectively. For functions and operators, an object in
1204 the search path is visible if there is no object of the same name
1209 >and argument data type(s)</I
1211 > earlier in the path. For
1212 operator classes, both name and associated index access method are
1216 > All these functions require object OIDs to identify the object to be
1217 checked. If you want to test an object by name, it is convenient to use
1218 the OID alias types (<TT
1233 CLASS="PROGRAMLISTING"
1234 >SELECT pg_type_is_visible('myschema.widget'::regtype);</PRE
1236 Note that it would not make much sense to test an unqualified name in
1237 this way --- if the name can be recognized at all, it must be visible.
1262 HREF="functions-misc.html#FUNCTIONS-MISC-CATALOG-TABLE"
1264 > lists functions that
1265 extract information from the system catalogs.
1268 >pg_get_viewdef</CODE
1272 >pg_get_ruledef</CODE
1276 >pg_get_indexdef</CODE
1280 >pg_get_triggerdef</CODE
1284 >pg_get_constraintdef</CODE
1286 reconstruct the creating command for a view, rule, index, trigger, or
1287 constraint. (Note that this is a decompiled reconstruction, not
1288 the original text of the command.) Most of these come in two
1289 variants, one of which can optionally <SPAN
1291 >"pretty-print"</SPAN
1293 The pretty-printed format is more readable, but the default format is more
1295 interpreted the same way by future versions of <SPAN
1299 avoid using pretty-printed output for dump purposes.
1303 > for the pretty-print parameter yields the
1304 same result as the variant that does not have the parameter at all.
1308 > decompiles the internal form of an
1309 individual expression, such as the default value for a column. It
1310 may be useful when examining the contents of system catalogs.
1313 >pg_get_userbyid</CODE
1315 extracts a user's name given a user ID number.
1320 NAME="FUNCTIONS-MISC-CATALOG-TABLE"
1324 >Table 9-39. System Catalog Information Functions</B
1329 ><COL><COL><COL><THEAD
1346 >pg_get_viewdef</CODE
1361 > command for view (<SPAN
1375 >pg_get_viewdef</CODE
1393 > command for view (<SPAN
1407 >pg_get_viewdef</CODE
1422 > command for view</TD
1430 >pg_get_viewdef</CODE
1448 > command for view</TD
1456 >pg_get_ruledef</CODE
1471 > command for rule</TD
1479 >pg_get_ruledef</CODE
1497 > command for rule</TD
1505 >pg_get_indexdef</CODE
1520 > command for index</TD
1528 >pg_get_indexdef</CODE
1549 > command for index,
1550 or definition of just one index column when
1560 >pg_get_triggerdef</CODE
1573 >CREATE [ CONSTRAINT ] TRIGGER</TT
1574 > command for trigger</TD
1582 >pg_get_constraintdef</CODE
1585 >constraint_oid</VAR
1594 >get definition of a constraint</TD
1602 >pg_get_constraintdef</CODE
1605 >constraint_oid</VAR
1617 >get definition of a constraint</TD
1640 >decompile internal form of an expression, assuming that any Vars
1641 in it refer to the relation indicated by the second parameter</TD
1667 >decompile internal form of an expression, assuming that any Vars
1668 in it refer to the relation indicated by the second parameter</TD
1676 >pg_get_userbyid</CODE
1688 >get user name with given ID</TD
1703 > The function shown in <A
1704 HREF="functions-misc.html#FUNCTIONS-MISC-COMMENT-TABLE"
1707 previously stored with the <TT
1711 null value is returned if no comment could be found matching the
1712 specified parameters.
1717 NAME="FUNCTIONS-MISC-COMMENT-TABLE"
1721 >Table 9-40. Comment Information Functions</B
1726 ><COL><COL><COL><THEAD
1743 >obj_description</CODE
1758 >get comment for a database object</TD
1766 >obj_description</CODE
1778 >get comment for a database object (<SPAN
1792 >col_description</CODE
1807 >get comment for a table column</TD
1813 > The two-parameter form of <CODE
1815 >obj_description</CODE
1817 comment for a database object specified by its OID and the name of the
1818 containing system catalog. For example,
1821 >obj_description(123456,'pg_class')</TT
1823 would retrieve the comment for a table with OID 123456.
1824 The one-parameter form of <CODE
1826 >obj_description</CODE
1828 the object OID. It is now deprecated since there is no guarantee that
1829 OIDs are unique across different system catalogs; therefore, the wrong
1830 comment could be returned.
1835 >col_description</CODE
1836 > returns the comment for a table column,
1837 which is specified by the OID of its table and its column number.
1840 >obj_description</CODE
1841 > cannot be used for table columns since
1842 columns do not have OIDs of their own.
1850 SUMMARY="Footer navigation table"
1861 HREF="functions-conditional.html"
1879 HREF="functions-array.html"
1889 >Conditional Expressions</TD
1895 HREF="functions.html"
1903 >Array Functions and Operators</TD