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 ] <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
orSCHEMA
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
orSCHEMA
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 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 |
---|---|
|
The timestamp at which the user-defined function (UDF) was created. |
|
The name of the UDF. |
|
The name of the schema in which the UDF exists. |
|
Whether the UDF is built-in (always false for UDFs). |
|
True if the UDF is an aggregate function; false otherwise. |
|
Not applicable currently. |
|
The minimum number of arguments to the UDF. |
|
The maximum number of arguments to the UDF. |
|
The data types of the arguments and of the return value. |
|
Description of the UDF. |
|
The name of the database in which the UDF exists. |
|
True if the UDF is a table function; false otherwise. |
|
True if the UDF can be used in a CLUSTER BY expression; false otherwise. |
|
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 | ---------------------------------+------------------------+-------------------+------------+--------------+---------+-------------------+-------------------+------------------------------------------------------------------------------------------+-----------------------+