Categories:: Account & Session DDL

SHOW FUNCTIONS¶

Lists all the native (i.e. system-defined/built-in) scalar functions provided by Snowflake, as well as any user-defined functions (UDFs) or external functions that have been created for your account.

For more information, see SQL Function Reference, UDFs (User-Defined Functions), and External Functions.

See also:: SHOW USER FUNCTIONS , SHOW EXTERNAL FUNCTIONS

Syntax¶

SHOW FUNCTIONS [ LIKE '<pattern>' ]
               [ IN { ACCOUNT | [ DATABASE ] <db_name> | [ SCHEMA ] <schema_name> } ]

Parameters¶

LIKE 'pattern'

Filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...

... LIKE '%TESTING%' ...

IN ACCOUNT | [ DATABASE ] db_name | [ SCHEMA ] schema_name

Specifies the scope of the command, which determines whether the command lists records only for the current/specified database or schema, or across your entire account:

The DATABASE or SCHEMA keyword is not required; you can set the scope by specifying only the database or schema name. Likewise, the database or schema name is not required if the session currently has a database in use:

If DATABASE or SCHEMA is specified without a name and the session does not currently have a database in use, the parameter has no effect on the output.
If SCHEMA is specified with a name and the session does not currently have a database in use, the schema name must be fully qualified with the database name (e.g. testdb.testschema).

Default: Depends on whether the session currently has a database in use:

Database: DATABASE is the default (i.e. the command returns the objects you have privileges to view in the database).
No database: ACCOUNT is the default (i.e. the command returns the objects you have privileges to view in your account).

Usage Notes¶

The command does not require a running warehouse to execute.
The command returns a maximum of 10K records for the specified object type, as dictated by the access privileges for the role used to execute the command; any records above the 10K limit are not returned, even with a filter applied.

To view results for which more than 10K records exist, query the corresponding view (if one exists) in the Information Schema.

To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

Output¶

The command’s output provides column properties and metadata in the following columns:

Column	Description
`created_on`	The timestamp at which the function was created.
`name`	The function’s name.
`schema_name`	The name of the schema that the function exists in. (NULL for built-in functions.)
`is_builtin`	True if the function is a built-in function; false otherwise.
`is_aggregate`	True if the function is an aggregate function; false otherwise.
`is_ansi`	True if the function is defined as part of the ANSI SQL standard; false otherwise.
`min_num_arguments`	Minimum number of arguments.
`max_num_arguments`	Maximum number of arguments.
`arguments`	Shows the data types of the arguments and of the return value.
`description`	Description of the function.
`catalog_name`	The name of the database that the function exists in. (NULL for built-in functions.)
`is_table_function`	True if the function is a table function; false otherwise.
`valid_for_clustering`	True if the function can be used in a `CLUSTER BY` expression; false otherwise.
`is_secure`	True if the function is a secure function; false otherwise.
`is_external_function`	True if the function is an external function; false otherwise.
`language`	For built-in functions, this column shows “SQL”. . For user-defined functions, this column shows the language in which the function was written (e.g. “JAVASCRIPT” or “SQL”). . For external functions, this column shows “EXTERNAL”.

Examples¶

Show all functions:

SHOW FUNCTIONS;

------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+
 created_on |       name        | schema_name | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments |                                      arguments                                      |                      description                           | language |
------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+
            | !=                |             | Y          | N            | Y       | 2                 | 2                 | !=(ANY, ANY) RETURN BOOLEAN, !=(ROW(ANY), ROW(ANY)) RETURN BOOLEAN                  | Checks if the first value is not equal to the second value | SQL      |
            | %                 |             | Y          | N            | N       | 2                 | 2                 | %(NUMBER, NUMBER) RETURN NUMBER, %(FLOAT, FLOAT) RETURN FLOAT                       | Computes modulo of two numbers                             | SQL      |
...
...
...
            | AND               |             | Y          | N            | Y       | 2                 | 2                 | AND(BOOLEAN, BOOLEAN) RETURN BOOLEAN                                                | returns TRUE if both inputs are TRUE, FALSE otherwise      | SQL      |
...
...
...
            | NOT               |             | Y          | N            | Y       | 1                 | 1                 | NOT(BOOLEAN) RETURN BOOLEAN                                                         | returns TRUE if input is TRUE, FALSE otherwise             | SQL      |
...
...
...
            | OR                |             | Y          | N            | Y       | 2                 | 2                 | OR(BOOLEAN, BOOLEAN) RETURN BOOLEAN                                                 | returns TRUE if either input is TRUE, FALSE otherwise      | SQL      |
...
...
...
            | SQUARE            |             | Y          | N            | Y       | 1                 | 1                 | SQUARE(NUMBER(38,0)) RETURN NUMBER(38,0), SQUARE(FLOAT) RETURN FLOAT                | Compute the square of the input expression.                | SQL      |
...
...
...
            | TYPEOF            |             | Y          | N            | N       | 1                 | 1                 | TYPEOF(ANY) RETURN VARCHAR(268435456)                                               | returns the SQL type of its input argument as a string     | SQL      |
...
...
...
------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+

Show only functions matching the specified regular expression:

SHOW FUNCTIONS LIKE 'SQUARE';

------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+
 created_on |       name        | schema_name | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments |                                      arguments                                      |                      description                           | language |
------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+
            | SQUARE            |             | Y          | N            | Y       | 1                 | 1                 | SQUARE(NUMBER(38,0)) RETURN NUMBER(38,0), SQUARE(FLOAT) RETURN FLOAT                | Compute the square of the input expression.                | SQL      |
------------+-------------------+-------------+------------+--------------+---------+-------------------+-------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------+----------+