Procedure
You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with procedure:
- Manage the lifecycle of new and existing procedures
- Manage grants of a procedure
Usage
We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.
- Procedure in Current Configuration
- Procedure in SOLE for Data Products
- Overloading
databases:
<database-name>:
schemas:
<schema-name>:
procedures:
<procedure-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>
databases:
<database-name>:
schemas:
<schema-name>:
procedures:
<procedure-name>| unique identifier:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>
Overloading means you can add multiple functions with the same name but with different arguments. Use a pipe | after the function name. If you add more than two functions, each function's text after the pipe must be unique. See Examples.
Each function must have a unique signature. A signature is generated based on the following parameters:
- Functions FQN (fully qualified name), which is made up of:
- Database name
- Schema name
- External Function name
- Number of arguments
- Data types of arguments
- The sequence of arguments (mainly data type)
The function is treated as duplicated if you have identical values for the above parameters.
Due to a Snowflake limitation, you can't create a UDF and procedure with the same signature in the same schema.
- procedure:
name: <procedure-name>
<configuration-key>: <value>
database: rel(database.<database-name>)
schema: rel(schema.<schema-name>)
grants:
<privilege>:
- rel(role.<role-name>)
- rel(role.<role-name>)
Supported parameters
The engine supports the parameters listed below.
Configuration Key | Required/Optional | Data Types and Values | Description |
---|---|---|---|
return_type | Required | String | The return type of the procedure |
statement | Required | String | Specifies the JavaScript / SQL code used to create the procedure |
arguments | Optional | Object. See here for a definition of argument | List of the arguments for the procedure |
comment | Optional | String | Specifies a comment for the procedure |
deleted | Optional | Boolean: True enables deletion prevention, False does nothing | Specifies what objects are allowed to be deleted |
execute_as | Optional | String | Sets execute context - see caller's rights and owner's rights |
grants | Optional | Map: See Supported Procedure Grants to Roles | List of Privileges and Roles to which privileges are granted on the current procedure. |
language | Optional | String: javascript (default), sql | Specifies the language of the stored procedure code |
manage_mode | Optional | String: all (default), none , grants | Configures what properties to manage for the procedure. See Changing Manage Mode before changing the value. |
null_input_behavior | Optional | String | Specifies the behavior of the procedure when called with null inputs |
return_behavior | Optional | String | Specifies the behavior of the procedure when returning results |
During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phase for few parameters like return_behavior
, null_input_behavior
, statement
, arguments
, `return_type
, etc.
arguments
parameters
You can specify the arguments for the procedure.
Multiples arguments in an object format can be listed in the arguments
parameter, with each having the following supported parameters:
Configuration Key | Required / Optional | Data Types and Values | Description |
---|---|---|---|
type | Required | String | Data type of the Column |
Example
arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE
Supported procedure grants to roles
Following are the privileges you can grant to roles in the procedure definition:
- USAGE
- OWNERSHIP
Examples
JavaScript Procedure
- Procedure in Current Configuration
- Procedure in SOLE for Data Products
- Overloading
databases:
DATABASE_1:
schemas:
SCHEMA_1:
procedures:
JAVASCRIPT_PROCEDURE:
comment: "Procedure using JavaScript language"
return_type: VARCHAR
language: javascript
execute_as: CALLER
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE
- procedure:
name: JAVASCRIPT_PROCEDURE
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
comment: "Procedure using JavaScript language"
return_type: VARCHAR
language: javascript
execute_as: CALLER
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE
databases:
DATABASE_1:
schemas:
SCHEMA_1:
procedures:
JAVASCRIPT_PROCEDURE|11:
comment: "Procedure using JavaScript language"
return_type: VARCHAR
language: javascript
execute_as: CALLER
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
arguments:
ARG_1:
type: VARCHAR(16777216)
JAVASCRIPT_PROCEDURE|VARCHAR(16777216)|VARCHAR(16777216):
comment: "Procedure using JavaScript language"
return_type: VARCHAR
language: javascript
execute_as: CALLER
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
arguments:
ARG_1:
type: VARCHAR(16777216)
ARG_2:
type: VARCHAR(16777216)
JAVASCRIPT_PROCEDURE|VARCHAR(16777216)|VARCHAR(16777216)|DATE:
comment: "Procedure using JavaScript language"
return_type: VARCHAR
language: javascript
execute_as: CALLER
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
arguments:
ARG_1:
type: VARCHAR(16777216)
ARG_2:
type: VARCHAR(16777216)
ARG_3:
type: DATE
SQL Procedure
- Procedure in Current Configuration
- Procedure in SOLE for Data Products
databases:
DATABASE_1:
schemas:
SCHEMA_1:
procedures:
SQL_PROCEDURE:
comment: "Procedure using SQL language"
language: sql
return_type: VARCHAR
execute_as: CALLER
statement: >-
DECLARE
radius_of_circle FLOAT;
area_of_circle FLOAT;
BEGIN
radius_of_circle := 3;
area_of_circle := pi() * radius_of_circle * radius_of_circle;
RETURN area_of_circle;
END;
- procedure:
name: JAVASCRIPT_PROCEDURE
database: rel(database.DATABASE_1)
schema: rel(schema.SCHEMA_1)
comment: "Procedure using SQL language"
language: sql
return_type: VARCHAR
execute_as: CALLER
statement: >-
DECLARE
radius_of_circle FLOAT;
area_of_circle FLOAT;
BEGIN
radius_of_circle := 3;
area_of_circle := pi() * radius_of_circle * radius_of_circle;
RETURN area_of_circle;
END;