Skip to content
Developer Edition runs on one host and does not require a product license file. Need multi-host deployment or performance testing at scale? Explore Enterprise Edition

SHOW USER DEFINED FUNCTION / FUNCTIONS command

The SHOW USER DEFINED FUNCTIONS command displays all user-defined functions (UDFs) in the current schema. The SHOW USER DEFINED FUNCTION <function_name> command displays the UDFs that match the specified function name.

If the current user does not have USAGE on the current schema, no UDFs in that schema are returned. For more information, see Schema access control.

The following table describes the returned information:

Column Description
name The name of the UDF.
signature The signature of the UDF.
comment The description of the UDF.

To view additional columns including the UDF category and null_behavior, use the VERBOSE option. See Syntax.

When the VERBOSE option is used, the following additional columns are returned:

Column Description
category The category of the UDF.
null_behavior The NULL handling behavior of the UDF.

Syntax

show_udf_statement
SHOW GQL USER DEFINED FUNCTIONS VERBOSE
show_specific_udf_statement
SHOW GQL USER DEFINED FUNCTION function_name VERBOSE 

show_udf_statement ::= 
    'SHOW' 'GQL'? 'USER DEFINED' 'FUNCTIONS' 'VERBOSE'?

show_specific_udf_statement ::= 
    'SHOW' 'GQL'? 'USER DEFINED' 'FUNCTION' function_name 'VERBOSE'?
  • VERBOSE: If specified, the output includes the additional category and null_behavior columns.

Parameters

function_name

The name of the UDF for which to display information.

You can specify a regular expression to match multiple UDFs. The regular expression must be enclosed in double quotes (") or backticks (`). For example, SHOW USER DEFINED FUNCTION "add_.*" matches all UDFs whose names start with add_.

To view all UDFs, use the SHOW USER DEFINED FUNCTIONS command.

Examples

The following examples are based on the UDFs created in User-defined functions.

Show all UDFs

SHOW USER DEFINED FUNCTIONS

Return:

+------------+----------------------------------------------------+------------------------------------+
| name       | signature                                          | comment                            |
+------------+----------------------------------------------------+------------------------------------+
| _pi        | _pi() -> FLOAT { 3.1415926 }                       |                                    |
| add_offset | add_offset(x FLOAT) -> FLOAT { x + $offset }       |                                    |
| add_tax    | add_tax(price FLOAT) -> FLOAT { price * 1.06 }     | Calculates the price including tax |
| avg_sum    | avg_sum(b FLOAT) -> FLOAT { avg(b) + sum(abs(b)) } |                                    |
| foo        | foo(a INT64, b FLOAT) -> FLOAT { a + b }           |                                    |
+------------+----------------------------------------------------+------------------------------------+

Show UDFs matching the specified function name

SHOW USER DEFINED FUNCTION foo

Return:

+------+------------------------------------------+---------+
| name | signature                                | comment |
+------+------------------------------------------+---------+
| foo  | foo(a INT64, b FLOAT) -> FLOAT { a + b } |         |
+------+------------------------------------------+---------+

Show UDFs matching the specified regular expression

SHOW USER DEFINED FUNCTION "add_.*"

Return:

+------------+----------------------------------------------+---------+
| name       | signature                                    | comment |
+------------+----------------------------------------------+---------+
| add_offset | add_offset(x FLOAT) -> FLOAT { x + $offset } |         |
+------------+----------------------------------------------+---------+