How To Get Snowflake Stored Procedure DDL with $$

Question

We prefer coding Snowflake stored procedures and javascript UDF's using the $$ notation. It is easier as we do not have to worry about escaping every single quote in our code. However, when we retrieve the DDL using GET_DDL, Snowflake removes the $$ and places the body of the SP in single quotes and also escapes every single quote.

Is there a way to get the SP DDL from Snowflake in the $$ format?

Example, below is the SP we create. Notice the $$ sign and that we do not hae

CREATE OR REPLACE PROCEDURE "SP_TEST"()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$
    var result = snowflake.execute({sqlText: "select 'Hello World!' as test;"})
    result.next();
    return String(result.getColumnValue(1));
$$;

Then, when we retrieve the DDL from Snowflake using SELECT GET_DDL('PROCEDURE','SP_TEST()'), we get the following. Notice the $$ has been replaced by single quotes and that all other single quotes are now escaped.

CREATE OR REPLACE PROCEDURE "SP_TEST"()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS '


    var result = snowflake.execute({sqlText: "select ''Hello World!'' as test;"})
    result.next();
    return String(result.getColumnValue(1));
';

Thanks

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-06-09T02:45:51

I've not found a built-in way to do it, but you can use a UDF to do this. This should work provided that the $$ start and end of the code section are always on lines by themselves:

create or replace function CODE_DDL_TO_TEXT(CODE_TEXT string)
returns string
language javascript
as
$$
    var lines = CODE_TEXT.split("\n");
    var out = "";
    var startCode = new RegExp("^AS '$", "ig");
    var endCode = new RegExp("^'\;$", "ig");
    var inCode = false;
    var isChange = false;
    var s;
    for (i = 0; i < lines.length; i++){
        isChange = false;
        if(!inCode) {
            inCode = startCode.test(lines[i]);
            if(inCode) {
                isChange = true;
                out += "AS $" + "$\n";
            }
        }
        if (endCode.test(lines[i])){
            out += "$" + "$;";
            isChange = true;
            inCode = false;
        }
        if(!isChange){
            if(inCode){
                s = lines[i].replace(/''/g, "'") + "\n";
                s = s.replace(/\\\\/g, "\\");
                out += s;
            } else {
                out += lines[i] + "\n";
            }
        }
    }
    return out;
$$;

select CODE_DDL_TO_TEXT(get_ddl('function', 'CODE_DDL_TO_TEXT(string)'));

How To Get Snowflake Stored Procedure DDL with $$

1 Answers