33.40. routines

Note: The following description applies both to Postgres-XC and PostgreSQL if not described explicitly. You can read PostgreSQL as Postgres-XC except for version number, which is specific to each product.

The view routines contains all functions in the current database. Only those functions are shown that the current user has access to (by way of being the owner or having some privilege).

Table 33-38. routines Columns

NameData TypeDescription
specific_catalogsql_identifierName of the database containing the function (always the current database)
specific_schemasql_identifierName of the schema containing the function
specific_namesql_identifier The "specific name" of the function. This is a name that uniquely identifies the function in the schema, even if the real name of the function is overloaded. The format of the specific name is not defined, it should only be used to compare it to other instances of specific routine names.
routine_catalogsql_identifierName of the database containing the function (always the current database)
routine_schemasql_identifierName of the schema containing the function
routine_namesql_identifierName of the function (might be duplicated in case of overloading)
routine_typecharacter_data Always FUNCTION (In the future there might be other types of routines.)
module_catalogsql_identifierApplies to a feature not available in PostgreSQL
module_schemasql_identifierApplies to a feature not available in PostgreSQL
module_namesql_identifierApplies to a feature not available in PostgreSQL
udt_catalogsql_identifierApplies to a feature not available in PostgreSQL
udt_schemasql_identifierApplies to a feature not available in PostgreSQL
udt_namesql_identifierApplies to a feature not available in PostgreSQL
data_typecharacter_data Return data type of the function, if it is a built-in type, or ARRAY if it is some array (in that case, see the view element_types), else USER-DEFINED (in that case, the type is identified in type_udt_name and associated columns).
character_maximum_lengthcardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
character_octet_lengthcardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
character_set_catalogsql_identifierApplies to a feature not available in PostgreSQL
character_set_schemasql_identifierApplies to a feature not available in PostgreSQL
character_set_namesql_identifierApplies to a feature not available in PostgreSQL
collation_catalogsql_identifierAlways null, since this information is not applied to return data types in PostgreSQL
collation_schemasql_identifierAlways null, since this information is not applied to return data types in PostgreSQL
collation_namesql_identifierAlways null, since this information is not applied to return data types in PostgreSQL
numeric_precisioncardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
numeric_precision_radixcardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
numeric_scalecardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
datetime_precisioncardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
interval_typecharacter_dataAlways null, since this information is not applied to return data types in PostgreSQL
interval_precisioncardinal_numberAlways null, since this information is not applied to return data types in PostgreSQL
type_udt_catalogsql_identifier Name of the database that the return data type of the function is defined in (always the current database)
type_udt_schemasql_identifier Name of the schema that the return data type of the function is defined in
type_udt_namesql_identifier Name of the return data type of the function
scope_catalogsql_identifierApplies to a feature not available in PostgreSQL
scope_schemasql_identifierApplies to a feature not available in PostgreSQL
scope_namesql_identifierApplies to a feature not available in PostgreSQL
maximum_cardinalitycardinal_numberAlways null, because arrays always have unlimited maximum cardinality in PostgreSQL
dtd_identifiersql_identifier An identifier of the data type descriptor of the return data type of this function, unique among the data type descriptors pertaining to the function. This is mainly useful for joining with other instances of such identifiers. (The specific format of the identifier is not defined and not guaranteed to remain the same in future versions.)
routine_bodycharacter_data If the function is an SQL function, then SQL, else EXTERNAL.
routine_definitioncharacter_data The source text of the function (null if the function is not owned by a currently enabled role). (According to the SQL standard, this column is only applicable if routine_body is SQL, but in PostgreSQL it will contain whatever source text was specified when the function was created.)
external_namecharacter_data If this function is a C function, then the external name (link symbol) of the function; else null. (This works out to be the same value that is shown in routine_definition.)
external_languagecharacter_dataThe language the function is written in
parameter_stylecharacter_data Always GENERAL (The SQL standard defines other parameter styles, which are not available in PostgreSQL.)
is_deterministicyes_or_no If the function is declared immutable (called deterministic in the SQL standard), then YES, else NO. (You cannot query the other volatility levels available in PostgreSQL through the information schema.)
sql_data_accesscharacter_data Always MODIFIES, meaning that the function possibly modifies SQL data. This information is not useful for PostgreSQL.
is_null_callyes_or_no If the function automatically returns null if any of its arguments are null, then YES, else NO.
sql_pathcharacter_dataApplies to a feature not available in PostgreSQL
schema_level_routineyes_or_no Always YES (The opposite would be a method of a user-defined type, which is a feature not available in PostgreSQL.)
max_dynamic_result_setscardinal_numberApplies to a feature not available in PostgreSQL
is_user_defined_castyes_or_noApplies to a feature not available in PostgreSQL
is_implicitly_invocableyes_or_noApplies to a feature not available in PostgreSQL
security_typecharacter_data If the function runs with the privileges of the current user, then INVOKER, if the function runs with the privileges of the user who defined it, then DEFINER.
to_sql_specific_catalogsql_identifierApplies to a feature not available in PostgreSQL
to_sql_specific_schemasql_identifierApplies to a feature not available in PostgreSQL
to_sql_specific_namesql_identifierApplies to a feature not available in PostgreSQL
as_locatoryes_or_noApplies to a feature not available in PostgreSQL
createdtime_stampApplies to a feature not available in PostgreSQL
last_alteredtime_stampApplies to a feature not available in PostgreSQL
new_savepoint_levelyes_or_noApplies to a feature not available in PostgreSQL
is_udt_dependentyes_or_no Currently always NO. The alternative YES applies to a feature not available in PostgreSQL.
result_cast_from_data_typecharacter_dataApplies to a feature not available in PostgreSQL
result_cast_as_locatoryes_or_noApplies to a feature not available in PostgreSQL
result_cast_char_max_lengthcardinal_numberApplies to a feature not available in PostgreSQL
result_cast_char_octet_lengthcharacter_dataApplies to a feature not available in PostgreSQL
result_cast_char_set_catalogsql_identifierApplies to a feature not available in PostgreSQL
result_cast_char_set_schemasql_identifierApplies to a feature not available in PostgreSQL
result_cast_char_set_namesql_identifierApplies to a feature not available in PostgreSQL
result_cast_collation_catalogsql_identifierApplies to a feature not available in PostgreSQL
result_cast_collation_schemasql_identifierApplies to a feature not available in PostgreSQL
result_cast_collation_namesql_identifierApplies to a feature not available in PostgreSQL
result_cast_numeric_precisioncardinal_numberApplies to a feature not available in PostgreSQL
result_cast_numeric_precision_radixcardinal_numberApplies to a feature not available in PostgreSQL
result_cast_numeric_scalecardinal_numberApplies to a feature not available in PostgreSQL
result_cast_datetime_precisioncharacter_dataApplies to a feature not available in PostgreSQL
result_cast_interval_typecharacter_dataApplies to a feature not available in PostgreSQL
result_cast_interval_precisioncardinal_numberApplies to a feature not available in PostgreSQL
result_cast_type_udt_catalogsql_identifierApplies to a feature not available in PostgreSQL
result_cast_type_udt_schemasql_identifierApplies to a feature not available in PostgreSQL
result_cast_type_udt_namesql_identifierApplies to a feature not available in PostgreSQL
result_cast_scope_catalogsql_identifierApplies to a feature not available in PostgreSQL
result_cast_scope_schemasql_identifierApplies to a feature not available in PostgreSQL
result_cast_scope_namesql_identifierApplies to a feature not available in PostgreSQL
result_cast_maximum_cardinalitycardinal_numberApplies to a feature not available in PostgreSQL
result_cast_dtd_identifiersql_identifierApplies to a feature not available in PostgreSQL