CODEWITHSUNDEEP
user-defined-functionssnowflake-cloud-data-platform
patrick harris
patrick harris

Reputation: 1

what is the proper syntax to display SNowflake DDL for UDF

I am logged in as an admin and created the following function

Create or replace function "CONSUMPTION"."UDF_GetCountArea" (
    LOCN_CLASS varchar, 
    LOCN_SIZE_TYPE varchar, 
    ZONE varchar,
    LOCN_AISLE varchar)

    Returns varchar(40) as

$$

    select CASE 
        WHEN  ZONE in('81','84') THEN                                                                       'Pallet Reserve'
        WHEN  ZONE = '88' AND LOCN_AISLE = '84' THEN                                                        'Pallet Reserve'
        WHEN  ZONE = '85' THEN                                                                              'Case Reserve'
        
        WHEN  ZONE in('80','83') THEN                                                                       'Pallet Reserve'
        WHEN  ZONE = '88' AND LOCN_AISLE = '83' THEN                                                        'Pallet Reserve'
        WHEN  ZONE = '86' THEN                                                                              'Case Reserve'
        
        WHEN LOCN_CLASS ='R' AND LOCN_SIZE_TYPE IN ('BFL','AFL','HLF','FUL') THEN                           'Pallet Reserve'
        WHEN LOCN_CLASS ='R' AND LOCN_SIZE_TYPE IN ('CSR','ACS','X1','BCS') THEN                            'Case Reserve'
        WHEN LOCN_CLASS ='C' AND ZONE IN ('41', '42', '43') THEN                                            'Cart Picking'
        WHEN LOCN_CLASS ='C' AND ZONE IN ('44', '45', '46') THEN                                            'Unit Picking'
        WHEN LOCN_CLASS ='C' AND ZONE IN ('47', '48', '49') THEN                                            'Mod Picking' 
        Else '99-UNK'
    End Phys_COUNT_AREA
$$

grant usage on function "CONSUMPTION"."UDF_GetCountArea"(VARCHAR,VARCHAR,VARCHAR,VARCHAR) to APPLICATION_SNOWFLAKE_QA_SC_WMS_NALC_READWRITE;

grant usage on function "CONSUMPTION"."UDF_GetCountArea"(VARCHAR,VARCHAR,VARCHAR,VARCHAR) to APPLICATION_SNOWFLAKE_QA_SC_WMS_NALC_READ;

SHOW USER FUNCTIONS return and shows the function.

I can test the function and it does work properly. but when I try to list out the code with the following command it fails to list

select GET_DDL('FUNCTION', 'UDF_GetCountArea(VARCHAR, VARCHAR, VARCHAR, VARCHAR)');

SQL compilation error: Object 'UDF_GetCountScope(VARCHAR, VARCHAR, VARCHAR)'

does not exist or not authorized.

Also, is there a setting somewhere that allows UDFs and SPs to be listed in the SF objects window?

Upvotes: 0

Views: 347

Answers (1)

Darren Gardner
Darren Gardner

Reputation: 1222

When you created your function, you wrapped your function name inside double quotes, and therefore the function is named UDF_GetCountArea. However, in your call to the GET_DDL() function, you did NOT wrap the name in double quotes, and in Snowflake, all identifiers are BY DEFAULT upper-cased, so you are asking for a function named UDF_GETCOUNTAREA, but no such function exists. In order to retrieve the DDL for the function that you created, you can use:

select GET_DDL('FUNCTION', '"UDF_GetCountArea"(VARCHAR, VARCHAR, VARCHAR, VARCHAR)');
;

That said, I would strongly urge you to avoid the use of quoted identifiers unless you absolutely need to use them. To do this, you would need to drop the existing function:

DROP FUNCTION "UDF_GetCountArea"(VARCHAR, VARCHAR, VARCHAR, VARCHAR)

and then re-create your function under the new name:

CREATE OR REPLACE FUNCTION CONSUMPTION.UDF_GETCOUNTAREA (
  LOCN_CLASS      VARCHAR
 ,LOCN_SIZE_TYPE  VARCHAR
 ,ZONE            VARCHAR
 ,LOCN_AISLE      VARCHAR
)
RETURNS VARCHAR AS
$$
...

Upvotes: 3

Related Questions