Categories:

DDL for User-Defined Functions, External Functions, and Stored Procedures

SHOW USER FUNCTIONS

Lists all user-defined functions (UDFs) for which you have access privileges. This command can be used to list the UDFs for a specified database or schema (or the current database/schema for the session), or across your entire account.

See also:

SHOW FUNCTIONS , SHOW EXTERNAL FUNCTIONS

FUNCTIONS View (Information Schema)

Syntax

SHOW USER FUNCTIONS [ LIKE '<pattern>' ]
                    [ IN
                         {
                           ACCOUNT                  |

                           DATABASE                 |
                           DATABASE <database_name> |

                           SCHEMA                   |
                           SCHEMA <schema_name>     |
                           <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 ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

SCHEMA, . SCHEMA schema_name, . schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully-qualified schema_name (e.g. db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

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 Snowflake 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:

| created_on | name | schema_name | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments | arguments | description | catalog_name | is_table_function | valid_for_clustering | is_secure |

Column

Description

created_on

The timestamp at which the user-defined function (UDF) was created.

name

The name of the UDF.

schema_name

The name of the schema in which the UDF exists.

is_builtin

Whether the UDF is built-in (always false for UDFs).

is_aggregate

True if the UDF is an aggregate function; false otherwise.

is_ansi

Not applicable currently.

min_num_arguments

The minimum number of arguments to the UDF.

max_num_arguments

The maximum number of arguments to the UDF.

arguments

The data types of the arguments and of the return value.

description

Description of the UDF.

catalog_name

The name of the database in which the UDF exists.

is_table_function

True if the UDF is a table function; false otherwise.

valid_for_clustering

True if the UDF can be used in a CLUSTER BY expression; false otherwise.

is_secure

True if the UDF is a secure UDF; false otherwise.

Examples

Show all the UDFs that you have privileges to view in the current database:

SHOW USER FUNCTIONS;

---------------------------------+------------------------+-------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------------------------------------------------------------+-----------------------+
           created_on            |          name          |    schema_name    | is_builtin | is_aggregate | is_ansi | min_num_arguments | max_num_arguments |                                        arguments                                         |      description      |
---------------------------------+------------------------+-------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------------------------------------------------------------+-----------------------+
 Wed, 01 Aug 2012 00:00:00 -0700 | GET_COUNTRIES_FOR_USER | TESTDB.TESTSCHEMA | N          | N            | N       | 1                 | 1                 | GET_COUNTRIES_FOR_USER(NUMBER) RETURN TABLE (COUNTRY_CODE VARCHAR, COUNTRY_NAME VARCHAR) | user-defined function |
 Wed, 01 Aug 2012 00:00:00 -0700 | MULTIPLY               | TESTDB.TESTSCHEMA | N          | N            | N       | 2                 | 2                 | MULTIPLY(NUMBER, NUMBER) RETURN NUMBER                                                   | multiply two numbers  |
---------------------------------+------------------------+-------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------------------------------------------------------------+-----------------------+
⇧
Back to top