Does Snowflake variables in UDF need to be in uppercase?

Question

I created a UDF to test this point:

CREATE OR REPLACE function EDW_WEATHER.CHK_READING(p_reading VARCHAR2, P_SENSOR VARCHAR2 )
  RETURNS VARCHAR2
  LANGUAGE JAVASCRIPT
AS
$$
    if (P_SENSOR == 'A') return p_reading;
$$
;

It runs correctly:

select EDW_WEATHER.CHK_READING('A', 'B');

By simply lowercasing the variable P_SENSOR as:

CREATE OR REPLACE function EDW_WEATHER.CHK_READING(p_reading VARCHAR2, p_sensor VARCHAR2 )
  RETURNS VARCHAR2
  LANGUAGE JAVASCRIPT
AS
$$
    if (p_sensor == 'A') return p_reading;
$$
;

I get this when I run the UDF:

100132 (P0000): JavaScript execution error: Uncaught ReferenceError: p_sensor is not defined in CHK_READING at 'if (p_sensor == 'A') return p_reading;' position 0

My question is whether Snowflake really require variables (used in "if" or "case" statements) to be in uppercase, or am I doing something wrong.

Answer 1

So there are two things. One is your session's handling of database object names. Which defaults to all objects are upper case by default (aka case does not matter), and in that context using double quotes, will mean "how I have the case is the intended case to use, also white space and other stuff is ok in here". This can be changed by session variables, but that leads to all sorts of troubles with views that using quotes and the case being respected an not for different sessions.

Then there is where that name convention behavior interacts/intersects with UDF code, where by case matters, and Snowflake have gone with "the objects true name is what it needs to be called in the JavaScript.

So if you are accessing passed in parameter and you do not double quote it's name (when you declare it), you will always have to refer to it SHOUTING style. But if you use the double quotes on the variable names when declaring the function (in a session where "quotes are respected") then you can have lower case variables in your javascript, which I show in the second example.

thus normally this function

CREATE OR REPLACE function EDW_WEATHER.CHK_READING(p_reading VARCHAR2, p_sensor VARCHAR2 )

the inputs are only P_READING & P_SENSOR

and here in this case

CREATE OR REPLACE function EDW_WEATHER.CHK_READING("p_reading" VARCHAR2, "p_SeNsOr" VARCHAR2 )

the input are only p_reading & p_SeNsOr

so you could change your function like this

CREATE OR REPLACE function CHK_READING("p_reading" VARCHAR2, "p_sensor" VARCHAR2 )
  RETURNS VARCHAR2
  LANGUAGE JAVASCRIPT
AS
$$
    if (p_sensor == 'A') return p_reading;
$$
;

and then happiness is true!

select CHK_READING('A', 'B');

see above how you named the function an object CHK_READING you can also call it via chk_reading because in SQL case (by default) does not matter.

So the last part: you question:

My question is whether Snowflake really require variables (used in "if" or "case" statements) to be in uppercase, or am I doing something wrong.

it is not a matter of things in IFs or CASEs, but when you use the input varaible.