Naming Conventions for Procedures and UDFs

When you create or call stored procedures or user-defined functions (UDF), you’ll need to be aware of the naming conventions that Snowflake allows and enforces for them.

Names for procedures and UDFs must conform to the rules for Object Identifiers.

Name Form

When you create a stored procedures or UDF, you create it in a specified database and schema. Procedures and UDFs have a fully-qualified name defined by their namespace in the form of db.schema.procedure_or_function_name.

The following illustrates the form of a stored procedure’s name:

CALL temporary_db_qualified_names_test.temporary_schema_1.stproc_pi();
Copy

When called without their fully-qualified name, procedures and UDFs are resolved according to the database and schema in use for the session.

In contrast, built-in, system-defined functions provided by Snowflake have no namespace. As a result, you can call them from anywhere.

Overloading

Snowflake supports overloading of procedure and function names. Multiple procedures or functions in the same schema can have the same name as long as their signatures differ, either by the number of arguments or the argument types. When you call an overloaded procedure or function, Snowflake checks the arguments and calls the correct one.

Note

Snowflake does not allow creating functions with the same name as any of the system-defined functions.

Be careful when using overloading. The combination of automatic type conversion and overloading makes it easy for minor user errors to cause unexpected results. For an example, see Stored Procedure Name Overloading Examples.

Consider the following examples, which create two SQL UDFs named add5:

CREATE OR REPLACE FUNCTION add5 (n number)
  RETURNS number
  AS 'n + 5';

CREATE OR REPLACE FUNCTION add5 (s string)
  RETURNS string
  AS 's || ''5''';
Copy

Important

In the second ADD5 function, single quotes are used to escape the string literal '5'. Any single quotes used in a UDF definition must be escaped by single quotes.

If add5 is called with a numeric argument, then the first implementation is chosen, while a string-typed argument uses the second implementation. If the argument is neither a number nor a string, then the implementation depends on Snowflake’s implicit type conversion rules. For instance, a date-typed argument would be converted to a string, and the string implementation would be selected, because conversion from DATE to NUMBER is not supported.

For example:

select add5(1);

+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

select add5('1');

+-----------+
| ADD5('1') |
|-----------|
| 15        |
+-----------+

select add5('hello');

+---------------+
| ADD5('HELLO') |
|---------------|
| hello5        |
+---------------+

select add5(to_date('2014-01-01'));

+-----------------------------+
| ADD5(TO_DATE('2014-01-01')) |
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+
Copy

Caution

For UDFs written in languages other than SQL, the rules might differ.

UDF Name Overloading Examples

The following examples illustrate how combining overloading and automatic type conversion can make it easy to get unexpected results.

  1. Create a function that takes a FLOAT parameter:

    CREATE FUNCTION add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE SQL
    AS $$
        PARAM_1 + 3.1415926::FLOAT
    $$;
    Copy

  2. Call the function twice. The first time, pass a FLOAT. The second time, pass a VARCHAR. The VARCHAR is converted to a FLOAT, and the output from each call is identical:

    SELECT add_pi(1.0), add_pi('1.0');
+-------------+---------------+
| ADD_PI(1.0) | ADD_PI('1.0') |
|-------------+---------------|
|   4.1415926 |     4.1415926 |
+-------------+---------------+
    Copy

  3. Create an overloaded function that takes a VARCHAR parameter:

    CREATE FUNCTION add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
        PARAM_1 || ' + 3.1415926'
    $$;
    Copy

  4. Use the same CALLs as before. Note the difference in output between these two CALLs and the previous two CALLs.

    SELECT add_pi(1.0), add_pi('1.0');
+-------------+-----------------+
| ADD_PI(1.0) | ADD_PI('1.0')   |
|-------------+-----------------|
|   4.1415926 | 1.0 + 3.1415926 |
+-------------+-----------------+
    Copy

Stored Procedure Name Overloading Examples

The following examples illustrate how combining overloading and automatic type conversion can make it easy to get unexpected results.

  1. Create two stored procedures that have the same names, but different numbers of arguments.

    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)
    returns string
    language javascript
    strict
    as
    $$
    return FLOAT_PARAM1;
    $$
    ;
    Copy
    create or replace procedure stproc1(FLOAT_PARAM1 FLOAT, FLOAT_PARAM2 FLOAT)
    returns string
    language javascript
    strict
    as
    $$
    return FLOAT_PARAM1 * FLOAT_PARAM2;
    $$
    ;
    Copy

  2. Call the two procedures:

    call stproc1(5.14::FLOAT);
+---------+
| STPROC1 |
|---------|
| 5.14    |
+---------+
    Copy
    call stproc1(5.14::FLOAT, 2.00::FLOAT);
+---------+
| STPROC1 |
|---------|
| 10.28   |
+---------+
    Copy

Note

You must also specify the data types of the arguments for some other operations on stored procedures. For example, GRANT and REVOKE require the argument types, as well as the stored procedure name.

The next example of overloading shows how combining overloading and automatic type conversion can make it easy to get unexpected results:

  1. Create a stored procedure that takes a FLOAT parameter:

    CREATE PROCEDURE add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + 3.1415926;
    $$;
    Copy

  2. Call the stored procedure twice. The first time, pass a FLOAT. The second time, pass a VARCHAR. The VARCHAR is converted to a FLOAT, and the output from each call is identical:

    CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
    Copy

  3. Create an overloaded stored procedure that takes a VARCHAR parameter:

    CREATE PROCEDURE add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS $$
        return PARAM_1 + '3.1415926';
    $$;
    Copy

  4. Call the stored procedure exactly as before. Note the difference in output between these two calls and the previous two calls.

    CALL add_pi(1.0);
+-----------+
|    ADD_PI |
|-----------|
| 4.1415926 |
+-----------+
CALL add_pi('1.0');
+--------------+
| ADD_PI       |
|--------------|
| 1.03.1415926 |
+--------------+
    Copy
Language: English