user_defined_functions

Creates, updates, deletes, gets or lists a user_defined_functions resource.

Overview

Name	user_defined_functions
Type	Resource
Id	snowflake.user_defined_function.user_defined_functions

Fields

Name	Datatype	Description
name	string	The name of the UDF
arguments	array	List of arguments for the function/procedure
body	string	Function/procedure definition
comment	string	Specifies a comment for the function/procedure
created_on	string	The date and time when the function/procedure was created
database_name	string	The name of the database in which the function/procedure exists.
is_aggregate	boolean	Specifies whether the UDF is an aggregate function. Applicable only for Python language type
is_builtin	boolean	If the function/procedure is built-in or not (user-defined)
is_memoizable	boolean	Indicates whether the function is memoizable. Applicable only for Python language type.
is_secure	boolean	Specifies whether the function/procedure is secure or not
is_table_function	boolean	True if the UDF is a table function; false otherwise.
is_temporary	boolean	Specifies whether the UDF is temporary or not
language_config	object
max_num_arguments	integer	The maximum number of arguments
min_num_arguments	integer	The minimum number of arguments
owner	string	Role that owns the function/procedure
owner_role_type	string	The type of role that owns the function/procedure
return_type	object
schema_name	string	The name of the schema in which the function/procedure exists.
valid_for_clustering	boolean	True if the UDF is valid for clustering; false otherwise.

Methods

Name	Accessible by	Required Params	Description
fetch_user_defined_function	SELECT	database_name, nameWithArgs, schema_name, endpoint	Fetch a UDF
list_user_defined_functions	SELECT	database_name, schema_name, endpoint	List UDFs
create_user_defined_function	INSERT	database_name, schema_name, data__arguments, data__language_config, data__name, data__return_type, endpoint	Create a UDF
delete_user_defined_function	DELETE	database_name, nameWithArgs, schema_name, endpoint	Delete a UDF
rename_user_defined_function	EXEC	database_name, nameWithArgs, schema_name, targetDatabase, targetName, targetSchema, endpoint	Rename a UDF

SELECT examples

List UDFs

SELECT
name,
arguments,
body,
comment,
created_on,
database_name,
is_aggregate,
is_builtin,
is_memoizable,
is_secure,
is_table_function,
is_temporary,
language_config,
max_num_arguments,
min_num_arguments,
owner,
owner_role_type,
return_type,
schema_name,
valid_for_clustering
FROM snowflake.user_defined_function.user_defined_functions
WHERE database_name = '{{ database_name }}'
AND schema_name = '{{ schema_name }}'
AND endpoint = '{{ endpoint }}';

INSERT example

Use the following StackQL query and manifest file to create a new user_defined_functions resource.

Required Properties

/*+ create */
INSERT INTO snowflake.user_defined_function.user_defined_functions (
data__name,
data__is_temporary,
data__is_aggregate,
data__is_memoizable,
data__is_secure,
data__arguments,
data__return_type,
data__language_config,
data__comment,
data__body,
database_name,
schema_name,
endpoint
)
SELECT 
'{{ name }}',
'{{ is_temporary }}',
'{{ is_aggregate }}',
'{{ is_memoizable }}',
'{{ is_secure }}',
'{{ arguments }}',
'{{ return_type }}',
'{{ language_config }}',
'{{ comment }}',
'{{ body }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}'
;

All Properties

/*+ create */
INSERT INTO snowflake.user_defined_function.user_defined_functions (
data__name,
data__arguments,
data__return_type,
data__language_config,
database_name,
schema_name,
endpoint
)
SELECT 
'{{ name }}',
'{{ arguments }}',
'{{ return_type }}',
'{{ language_config }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}'
;

Manifest

- name: user_defined_functions
  props:
    - name: database_name
      value: string
    - name: schema_name
      value: string
    - name: data__arguments
      value: string
    - name: data__language_config
      value: string
    - name: data__name
      value: string
    - name: data__return_type
      value: string
    - name: endpoint
      value: string
    - name: name
      value: string
    - name: is_temporary
      value: boolean
    - name: is_aggregate
      value: boolean
    - name: is_memoizable
      value: boolean
    - name: is_secure
      value: boolean
    - name: arguments
      value: array
      props:
        - name: name
          value: string
        - name: datatype
          value: string
        - name: default_value
          value: string
    - name: return_type
      props:
        - name: type
          value: string
    - name: language_config
      props:
        - name: language
          value: string
        - name: called_on_null_input
          value: boolean
        - name: is_volatile
          value: boolean
    - name: comment
      value: string
    - name: body
      value: string

DELETE example

Deletes the specified user_defined_functions resource.

/*+ delete */
DELETE FROM snowflake.user_defined_function.user_defined_functions
WHERE database_name = '{{ database_name }}'
AND nameWithArgs = '{{ nameWithArgs }}'
AND schema_name = '{{ schema_name }}'
AND endpoint = '{{ endpoint }}';