procedures

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

Overview

Name	procedures
Type	Resource
Id	snowflake.procedure.procedures

Fields

Name	Datatype	Description
name	string	Name of the procedure
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.
execute_as	string	What permissions should the procedure execution be called with
is_builtin	boolean	If the function/procedure is built-in or not (user-defined)
is_secure	boolean	Specifies whether the function/procedure is secure 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.

Methods

Name	Accessible by	Required Params	Description
fetch_procedure	SELECT	database_name, nameWithArgs, schema_name, endpoint	Fetch a procedure
list_procedures	SELECT	database_name, schema_name, endpoint	List procedures
create_procedure	INSERT	database_name, schema_name, data__arguments, data__body, data__language_config, data__name, data__return_type, endpoint	Create a procedure
delete_procedure	DELETE	database_name, nameWithArgs, schema_name, endpoint	Delete a procedure
call_procedure	EXEC	database_name, nameWithArgs, schema_name, data__call_arguments, endpoint	Call a procedure

SELECT examples

List procedures

SELECT
name,
arguments,
body,
comment,
created_on,
database_name,
execute_as,
is_builtin,
is_secure,
language_config,
max_num_arguments,
min_num_arguments,
owner,
owner_role_type,
return_type,
schema_name
FROM snowflake.procedure.procedures
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 procedures resource.

Required Properties

/*+ create */
INSERT INTO snowflake.procedure.procedures (
data__name,
data__execute_as,
data__is_secure,
data__arguments,
data__return_type,
data__language_config,
data__comment,
data__body,
database_name,
schema_name,
endpoint
)
SELECT 
'{{ name }}',
'{{ execute_as }}',
'{{ is_secure }}',
'{{ arguments }}',
'{{ return_type }}',
'{{ language_config }}',
'{{ comment }}',
'{{ body }}',
'{{ database_name }}',
'{{ schema_name }}',
'{{ endpoint }}'
;

All Properties

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

Manifest

- name: procedures
  props:
    - name: database_name
      value: string
    - name: schema_name
      value: string
    - name: data__arguments
      value: string
    - name: data__body
      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: execute_as
      value: string
    - 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: comment
      value: string
    - name: body
      value: string

DELETE example

Deletes the specified procedures resource.

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