Routines

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:

SELECT
  routine_name || '(' || array_to_string(routine_parameters) || ') => ' || data_type
FROM
  information_schema.routines
WHERE
  routine_type = 'AGGREGATE FUNCTION'
ORDER BY routine_name

Example The following code example retrieves the names of all routines from the database that return a data type of GEOGRAPHY:

SELECT
  routine_name
FROM
  information_schema.routines
WHERE
  data_type = '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.

Quotas

CDC ingests

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Columns in `information_schema.routines`

​Columns in information_schema.routines

Columns in `information_schema.routines`