Information schema
Routines
Use this reference to learn about the metadata available about SQL functions using the information schema.
You can use the information_schema.routines
view to return information about SQL functions including their database, schema, name, type, return data type, parameter data types, and whether they are deterministic.
Example
The following code example lists all aggregate functions in the database and displays their name, parameters, and return data type, sorted by function name:
Example
The following code example retrieves the names of all routines from the database that return a data type of GEOGRAPHY
:
Columns in information_schema.routines
Each row in information_schema.routines
contains the following information about the SQL function:
Column Name | Data Type | Description |
---|---|---|
routine_catalog | TEXT | The database containing the function. |
routine_schema | TEXT | The schema containing the function. |
routine_name | TEXT | The name of the function. |
routine_type | TEXT | The type of the function, which can be either FUNCTION , AGGREGATE FUNCTION , WINDOW FUNCTION or TABLE FUNCTION . |
data_type | TEXT | The return type name of the function. These type names are simplified, for example, returning ARRAY instead of ARRAY(INT) . If the function accepts and returns data any type, its data_type is ANY . |
is_deterministic | TEXT | A flag that indicates if the function is deterministic or not. A deterministic function returns the same output when called with the same inputs within the same query, even if it can produce a different output when called in a different query. An example of a deterministic function is CURRENT_TIMESTAMP . An example of a non-deterministic function is RANDOM . |
routine_parameters | ARRAY(TEXT) | An array of data types representing the function’s parameters. |