Creating a new function in Snowflake

Sometimes, you’ll need to create functions that do a particular thing. They’re not always pretty. Some of them look like they should have been thrown out with the bathwater.

Unlike SQL Server, user-defined functions in Snowflake can be done in several different languages. They can be done in Java, JavaScript, Python, Scala, or plain-old SQL.

Today, I’m going to share with you one of my favorite UDFs to get the primary keys from a table, if there is a primary key on it. Thank you goes out to Greg Pavlik many years ago for creating this small function many years ago on his page here. (Yes, go out and his read his blog – because it’s great!)

create or replace function GET_PK_COLUMNS(TABLE_DDL string)
returns table (PK_COLUMN string)
language javascript
as
$$
{
    processRow: function get_params(row, rowWriter, context){
        var pkCols = getPKs(row.TABLE_DDL);
        for (i = 0; i < pkCols.length; i++) {
            rowWriter.writeRow({PK_COLUMN: pkCols[i]}); 
        }
          
        function getPKs(tableDDL) {
            var c;
            var keyword = "primary key";
            var ins = -1;
            var s = tableDDL.split("\n");
            for (var i = 0; i < s.length; i++) {  
                ins = s[i].indexOf(keyword);
                if (ins != -1) {
                    var colList = s[i].substring(ins + keyword.length);
                    colList = colList.replace("(", "");
                    colList = colList.replace(")", "");
                    var colArray = colList.split(",");
                    for (pkc = 0; c < colArray.length; pkc++) {
                        colArray[pkc] = colArray[pkc].trim();
                    }
                    return colArray;
                }
            }
            return [];  // No PK
        }
    }
}
$$;

First, please note that this UDF actually is a UDTF – since it returns a table. (Yes, UDTF stands for User Defined Table Function.) It is also written in JavaScript – maybe not the coolest of all languages but it can definitely get the job done.

Also, notice the $$ around the definition of the function. You should be using those to surround your function definition or your function may or may not work. The reason is because the $$ is used for string escapes – if you don’t use it, you have to escape every single quote in your code – which is probably going to be a lot…

Now, let’s practice with our new function:

select * from table(get_pk_columns(get_ddl('table', 'NewEmployee'))) PKS;

Sigh. Looks like someone forgot to define the primary key on the new table. Shame on them!

Let’s help them out by adding the primary key to the table correctly.

ALTER TABLE NEWEMPLOYEE
ADD CONSTRAINT PK_NewEmp PRIMARY KEY (EEID);

Now when we run our UDTF, we get the following:

Happiness is a table with a good primary key!

Over the next few weeks, we’ll go over a few other languages you can create a UDF in Snowflake in, what is different about each of those structures, and just some of what you have to look out for.

And with that my friends, we have come to the end of yet another blog post. Hopefully, you’ll start digging around for some goodness with JavaScript after this post and can create some functions that are at least good for you!