Welcome To TechBrothersIT: Snowflake SQL Tutorial 141 - My Function Said RETURNS INT And Handed Back 3.5

Snowflake SQL Tutorial 141 - My Function Said RETURNS INT And Handed Back 3.5

Snowflake SQL · Episode 141

In this post we are going to cover scalar SQL user defined functions in Snowflake.

A scalar SQL UDF is a name, a signature, a return type and a body. The body is one SQL expression. You call it like any built-in function and you get one value back.

That part is easy. The part worth an episode is this: almost everything you declare about one is metadata, and the body is what actually runs.

We will start with a function that adds tax and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 141 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's write a function
Now let's see what the catalog keeps
Where can a function go?
Now back to that number: RETURNS is a promise, not a cast
So what does Snowflake check?
The null clause is metadata too
Can two functions share a name?
What can a body do?
What about a secure function?
What does a body refuse, and where?
One last thing: the dependency nobody tracks
And the teardown

Let's create some sample data

A small table to run the functions over. One row has a NULL amount, on purpose, because that row answers a question later on.

Sample data

-- Sample data for this post.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_T141 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

INSERT INTO SALES_T141 (SALE_ID, REGION, AMOUNT) VALUES
    (1, 'north', 100.00), (2, 'south', 250.50), (3, 'east', 75.25),
    (4, 'west',  310.00), (5, 'north', NULL);

Query

INSERT INTO SALES_T141 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
       (3, 'east',   75.25), (4, 'west',  310.00),
       (5, 'north',   NULL);

Result1 row · 1 column

number of rows inserted
5

Five rows, four regions, and one missing amount.

Let's write a function

A signature, a RETURNS clause, and a body between dollar signs.

Query

CREATE OR REPLACE FUNCTION WITH_TAX_T141(AMOUNT NUMBER(10,2))
RETURNS NUMBER(10,2)
AS
$$
    AMOUNT * 1.20
$$;

Result1 row · 1 column

status
Function WITH_TAX_T141 successfully created.

Created. Let's call it on a literal.

Query

SELECT WITH_TAX_T141(100.00) AS A_HUNDRED_WITH_TAX;

Result1 row · 1 column

A_HUNDRED_WITH_TAX
120.000

120.000. Hold that number, because we come back to it. The function declared NUMBER(10,2), which is two decimal places, and that result has three.

And on a few more values, including zero and a negative.

Query

SELECT WITH_TAX_T141(10.00)  AS TEN,
       WITH_TAX_T141(0.00)   AS NOTHING,
       WITH_TAX_T141(-50.00) AS A_NEGATIVE;

Result1 row · 3 columns

TEN	NOTHING	A_NEGATIVE
12.000	0.000	-60.000

Nothing clever happening. It is an expression with a name on it.

Now let's see what the catalog keeps

There are three places to look, and they are three different shapes. First, SHOW USER FUNCTIONS.

SHOW USER FUNCTIONS

SHOW USER FUNCTIONS LIKE '%\\_T141';

Result1 row · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function
2026-10-03 13:52:40.292000-07:00	WITH_TAX_T141	PUBLIC	N	N	N	1	1	WITH_TAX_T141(NUMBER) RETURN NUMBER	user-defined function	DEMO_DB	N	N	N			N	SQL	N	N	N

Twenty one columns, which is a lot to read at once. The useful one is arguments, and it holds the signature and the return type together as one string.

The secrets and external_access_integrations cells are empty strings, which the grid marks as empty. Nothing in this grid is a real NULL.

DESCRIBE FUNCTION is a completely different shape. It gives property and value rows.

DESCRIBE FUNCTION

DESCRIBE FUNCTION WITH_TAX_T141(NUMBER);

Result4 rows · 2 columns

property	value
signature	(AMOUNT NUMBER)
returns	NUMBER(10,2)
language	SQL
body	AMOUNT * 1.20

Four rows: the signature, the return type, the language and the body itself. So the body is not hidden anywhere. It is sitting in the catalog in plain text.

And there is a view as well.

INFORMATION_SCHEMA.FUNCTIONS

SELECT FUNCTION_NAME,
       ARGUMENT_SIGNATURE,
       DATA_TYPE,
       FUNCTION_LANGUAGE,
       IS_SECURE
FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_SCHEMA = 'PUBLIC'
  AND ENDSWITH(FUNCTION_NAME, '_T141')
ORDER BY FUNCTION_NAME;

Result1 row · 5 columns

FUNCTION_NAME	ARGUMENT_SIGNATURE	DATA_TYPE	FUNCTION_LANGUAGE	IS_SECURE
WITH_TAX_T141	(AMOUNT NUMBER)	NUMBER(10,2)	SQL	NO

The signature, the data type, the language and a secure flag. Three places, three shapes, one function.

Where can a function go?

In a select list, once per row.

Query

SELECT SALE_ID,
       AMOUNT,
       WITH_TAX_T141(AMOUNT) AS AMOUNT_WITH_TAX
FROM SALES_T141
ORDER BY SALE_ID;

Result5 rows · 3 columns

SALE_ID	AMOUNT	AMOUNT_WITH_TAX
1	100.00	120.000
2	250.50	300.600
3	75.25	90.300
4	310.00	372.000
5	NULL	NULL

Five rows, and the tax worked out for each of them. Now in a WHERE clause.

Query

SELECT SALE_ID, AMOUNT
FROM SALES_T141
WHERE WITH_TAX_T141(AMOUNT) > 200
ORDER BY SALE_ID;

Result2 rows · 2 columns

SALE_ID	AMOUNT
2	250.50
4	310.00

Two rows. Snowflake had to run the function to decide which rows to keep, and it did.

And in an ORDER BY and a GROUP BY at the same time.

Query

SELECT REGION,
       SUM(WITH_TAX_T141(AMOUNT)) AS TAXED_TOTAL
FROM SALES_T141
GROUP BY REGION
ORDER BY TAXED_TOTAL DESC NULLS LAST;

Result4 rows · 2 columns

REGION	TAXED_TOTAL
west	372.000
south	300.600
north	120.000
east	90.300

Four rows, one per region, grouped by region, totalled with tax and ordered by that total. All of the arithmetic came out of the function.

Now the row we left in deliberately. The one with no amount.

Query

SELECT SALE_ID,
       AMOUNT,
       WITH_TAX_T141(AMOUNT)              AS TAXED,
       WITH_TAX_T141(AMOUNT) IS NULL      AS IS_IT_NULL
FROM SALES_T141
WHERE SALE_ID = 5;

Result1 row · 4 columns

SALE_ID	AMOUNT	TAXED	IS_IT_NULL
5	NULL	NULL	TRUE

NULL in, NULL out, and IS NULL says TRUE.

Which is not the function being careful. NULL * 1.20 is NULL. No special handling, no error, no zero.

Now back to that number: RETURNS is a promise, not a cast

The first grid gave us three decimal places out of a function that declared two. So something is already not working the way the declaration reads. Let's make it unmistakable.

Here is a function that takes an integer, returns an integer, and divides by two.

Query

CREATE OR REPLACE FUNCTION HALF_T141(N INT)
RETURNS INT
AS
$$
    N / 2
$$;

Result1 row · 1 column

status
Function HALF_T141 successfully created.

Let's call it on an odd number.

Query

SELECT HALF_T141(7) AS SEVEN_HALVED,
       HALF_T141(5) AS FIVE_HALVED,
       HALF_T141(4) AS FOUR_HALVED;

Result1 row · 3 columns

SEVEN_HALVED	FIVE_HALVED	FOUR_HALVED
3.500000	2.500000	2.000000

3.500000. Not three, not four, and with six decimal places on it. A function that promised an integer handed back a fraction.

So let's compare that against the same expression with no function involved at all.

Query

SELECT 7 / 2 AS SEVEN_HALVED_PLAINLY;

Result1 row · 1 column

SEVEN_HALVED_PLAINLY
3.500000

3.500000 again, to the same six decimal places. Byte for byte identical. The RETURNS clause did nothing whatsoever.

Numbers are easy to argue about, so here is one more with no arithmetic in it. A function that promises a short string and whose body just hands back what it was given.

Query

CREATE OR REPLACE FUNCTION SHORTEN_T141(S VARCHAR)
RETURNS VARCHAR(5)
AS
$$
    S
$$;

Result1 row · 1 column

status
Function SHORTEN_T141 successfully created.

And give it ten characters

SELECT SHORTEN_T141('abcdefghij') AS SHORTENED;

Result1 row · 1 column

SHORTENED
abcdefghij

All ten characters came back, out of a function that said five.

The finding this episode is built around

So the RETURNS clause does not cast, does not round and does not truncate.
If you need the result rounded or shortened, do it in the body. Writing it in the RETURNS clause looks like it works and does not.

So what does Snowflake check?

Compatibility, and it checks it at create time. Let's declare a number and write a string body.

Query

CREATE OR REPLACE FUNCTION NOTANUMBER_T141()
RETURNS INT
AS
$$
    'not a number'
$$;

Query failed — Snowflake returned an error

090207 (42601): Declared return type 'NUMBER(38,0)' is incompatible with actual return type 'VARCHAR(12)'

090207. The declared return type is incompatible with the actual return type, and the function is not created.

We can prove it was not created by calling it.

Query

SELECT NOTANUMBER_T141() AS WHAT_CAME_BACK;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function NOTANUMBER_T141.

Unknown function. There is nothing there.

So that is the whole rule, in one line. Compatible is checked. Precision is not.

The null clause is metadata too

The same flavour of surprise lives in the strictness clause. Let's write two functions that are identical apart from that clause, with bodies that report what they saw.

CALLED ON NULL INPUT

CREATE OR REPLACE FUNCTION CALLED_T141(N INT)
RETURNS VARCHAR
CALLED ON NULL INPUT
AS
$$
    CASE WHEN N IS NULL THEN 'the body ran and saw a null'
         ELSE 'the body ran and saw ' || N::VARCHAR END
$$;

Result1 row · 1 column

status
Function CALLED_T141 successfully created.

RETURNS NULL ON NULL INPUT

CREATE OR REPLACE FUNCTION STRICT_T141(N INT)
RETURNS VARCHAR
RETURNS NULL ON NULL INPUT
AS
$$
    CASE WHEN N IS NULL THEN 'the body ran and saw a null'
         ELSE 'the body ran and saw ' || N::VARCHAR END
$$;

Result1 row · 1 column

status
Function STRICT_T141 successfully created.

Let's call them both with a three first.

Query

SELECT CALLED_T141(3) AS THE_CALLED_ONE,
       STRICT_T141(3) AS THE_STRICT_ONE;

Result1 row · 2 columns

THE_CALLED_ONE	THE_STRICT_ONE
the body ran and saw 3	the body ran and saw 3

Both ran and both saw a three. No surprise there. Now let's call them both with a NULL.

Query

SELECT CALLED_T141(NULL) AS THE_CALLED_ONE,
       STRICT_T141(NULL) AS THE_STRICT_ONE;

Result1 row · 2 columns

THE_CALLED_ONE	THE_STRICT_ONE
the body ran and saw a null	the body ran and saw a null

Both ran. Both saw a null. Including the one that said it would return null instead of running.

So let's ask the catalog whether it even noticed the difference.

Query

SELECT FUNCTION_NAME, IS_NULL_CALL, ARGUMENT_SIGNATURE
FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_SCHEMA = 'PUBLIC'
  AND FUNCTION_NAME IN ('CALLED_T141', 'STRICT_T141')
ORDER BY FUNCTION_NAME;

Result2 rows · 3 columns

FUNCTION_NAME	IS_NULL_CALL	ARGUMENT_SIGNATURE
CALLED_T141	YES	(N NUMBER)
STRICT_T141	NO	(N NUMBER)

It did. IS_NULL_CALL reads YES for one and NO for the other. Recorded, and not honoured.

Not a universal rule

Keep that one in mind, because it is not the same in every language. Episode 143 runs the identical experiment on a JavaScript UDF and gets a different answer.

They can, as long as they take different argument types.

One for a number

CREATE OR REPLACE FUNCTION LABEL_T141(N INT)
RETURNS VARCHAR
AS
$$
    'a number: ' || N::VARCHAR
$$;

Result1 row · 1 column

status
Function LABEL_T141 successfully created.

One for a string

CREATE OR REPLACE FUNCTION LABEL_T141(S VARCHAR)
RETURNS VARCHAR
AS
$$
    'a string: ' || S
$$;

Result1 row · 1 column

status
Function LABEL_T141 successfully created.

Both created. Let's see which one gets picked.

Query

SELECT LABEL_T141(7)     AS GIVEN_A_NUMBER,
       LABEL_T141('xyz') AS GIVEN_A_STRING;

Result1 row · 2 columns

GIVEN_A_NUMBER	GIVEN_A_STRING
a number: 7	a string: xyz

The right one each time, chosen by what we handed it.

SHOW USER FUNCTIONS

SHOW USER FUNCTIONS LIKE 'LABEL\\_T141';

Result2 rows · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function
2026-10-03 13:52:47.231000-07:00	LABEL_T141	PUBLIC	N	N	N	1	1	LABEL_T141(NUMBER) RETURN VARCHAR	user-defined function	DEMO_DB	N	N	N			N	SQL	N	N	N
2026-10-03 13:52:47.357000-07:00	LABEL_T141	PUBLIC	N	N	N	1	1	LABEL_T141(VARCHAR) RETURN VARCHAR	user-defined function	DEMO_DB	N	N	N			N	SQL	N	N	N

Two entries, the same name, different argument strings. Now something less obvious. What does a date get?

Query

SELECT LABEL_T141('2026-01-15'::DATE) AS GIVEN_A_DATE;

Result1 row · 1 column

GIVEN_A_DATE
a string: 2026-01-15

The string one. A date converts to a varchar more readily than it converts to a number, so that is the overload it lands on.

And once there are two, the name on its own is no longer enough to point at either.

Query

DESCRIBE FUNCTION LABEL_T141();

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Function 'DEMO_DB.PUBLIC.LABEL_T141' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on FUNCTION DEMO_DB.PUBLIC.LABEL_T141.

002003, does not exist. With overloads in place, DESCRIBE FUNCTION needs the signature.

What can a body do?

More than you might expect. It can call another function.

Query

CREATE OR REPLACE FUNCTION TWICE_TAXED_T141(AMOUNT NUMBER(10,2))
RETURNS NUMBER(12,4)
AS
$$
    WITH_TAX_T141(WITH_TAX_T141(AMOUNT))
$$;

Result1 row · 1 column

status
Function TWICE_TAXED_T141 successfully created.

And what it gives

SELECT TWICE_TAXED_T141(100.00) AS TWICE_TAXED;

Result1 row · 1 column

TWICE_TAXED
144.0000

144.0000. Tax applied twice. So can it call itself?

Query

CREATE OR REPLACE FUNCTION COUNTDOWN_T141(N INT)
RETURNS INT
AS
$$
    CASE WHEN N <= 0 THEN 0 ELSE COUNTDOWN_T141(N - 1) END
$$;

Query failed — Snowflake returned an error

090206 (42601): Detected a cycle in SQL UDF: COUNTDOWN_T141

090206, a cycle detected in a SQL UDF. Caught at create time, which is the right place for it.

And calling it anyway

SELECT COUNTDOWN_T141(3) AS COUNTED_DOWN;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function COUNTDOWN_T141.

Unknown function, so nothing was created. There is no recursion in a SQL UDF.

A body can read a table

Query

CREATE OR REPLACE FUNCTION REGION_TOTAL_T141(R VARCHAR)
RETURNS NUMBER(12,2)
AS
$$
    SELECT SUM(AMOUNT) FROM SALES_T141 WHERE REGION = R
$$;

Result1 row · 1 column

status
Function REGION_TOTAL_T141 successfully created.

And what it gives

SELECT REGION_TOTAL_T141('north') AS NORTH,
       REGION_TOTAL_T141('south') AS SOUTH,
       REGION_TOTAL_T141('nowhere') AS NOWHERE;

Result1 row · 3 columns

NORTH	SOUTH	NOWHERE
100.00	250.50	NULL

North and south give their totals, and a region with no rows gives a NULL rather than a zero, because that is what SUM over no rows returns.

And it can run once per row of another query, which is the shape you would actually use.

Query

SELECT DISTINCT REGION, REGION_TOTAL_T141(REGION) AS THE_TOTAL
FROM SALES_T141
ORDER BY REGION;

Result4 rows · 2 columns

REGION	THE_TOTAL
east	75.25
north	100.00
south	250.50
west	310.00

Four rows, one per region, each one a separate run of the function.

A body can be a column default

Query

CREATE OR REPLACE TABLE DEFAULTED_T141 (
    SALE_ID INT,
    AMOUNT  NUMBER(10,2),
    TAXED   NUMBER(10,2) DEFAULT WITH_TAX_T141(1.00)
);

Result1 row · 1 column

status
Table DEFAULTED_T141 successfully created.

A row

INSERT INTO DEFAULTED_T141 (SALE_ID, AMOUNT) VALUES (1, 10.00);

Result1 row · 1 column

number of rows inserted
1

And what the default put there

SELECT SALE_ID, AMOUNT, TAXED FROM DEFAULTED_T141;

Result1 row · 3 columns

SALE_ID	AMOUNT	TAXED
1	10.00	1.20

Read that one carefully. The amount is ten and the taxed column says 1.20. The default was written as the function of a constant, so a constant is what it stored.

A DEFAULT cannot see the other columns of its own row. If you want the tax of this row's amount, a default is not where it goes.

A body can back a view

Query

CREATE OR REPLACE VIEW TAXED_VIEW_T141 AS
SELECT SALE_ID, AMOUNT, WITH_TAX_T141(AMOUNT) AS AMOUNT_WITH_TAX
FROM SALES_T141;

Result1 row · 1 column

status
View TAXED_VIEW_T141 successfully created.

And what it shows

SELECT SALE_ID, AMOUNT, AMOUNT_WITH_TAX
FROM TAXED_VIEW_T141
ORDER BY SALE_ID;

Result5 rows · 3 columns

SALE_ID	AMOUNT	AMOUNT_WITH_TAX
1	100.00	120.000
2	250.50	300.600
3	75.25	90.300
4	310.00	372.000
5	NULL	NULL

Five rows, exactly as the query gave us earlier.

But not a virtual column

Query

CREATE OR REPLACE TABLE COMPUTED_T141 (
    AMOUNT NUMBER(10,2),
    TAXED  NUMBER(12,4) AS (WITH_TAX_T141(AMOUNT))
);

Query failed — Snowflake returned an error

001482 (42601): SQL compilation error: error line 3 at position 4
Data type of virtual column does not match the data type of its expression for column 'TAXED'. Expected data type 'NUMBER(12,4)', found 'NUMBER(12,3)'

001482, and read the pane rather than the summary. The declared type and the found type differ by one decimal place. That is the same three-decimals-from-a-two-decimal-declaration we saw in the very first grid, turning up somewhere that does check.

What about a secure function?

Query

CREATE OR REPLACE SECURE FUNCTION HIDDEN_T141(N INT)
RETURNS INT
AS
$$
    N * 3
$$;

Result1 row · 1 column

status
Function HIDDEN_T141 successfully created.

And it works

SELECT HIDDEN_T141(5) AS TRIPLED;

Result1 row · 1 column

TRIPLED
15

Created, and it runs. Now let's ask the catalog about its body.

Query

SELECT FUNCTION_NAME, IS_SECURE, FUNCTION_DEFINITION
FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_SCHEMA = 'PUBLIC'
  AND FUNCTION_NAME IN ('HIDDEN_T141', 'WITH_TAX_T141')
ORDER BY FUNCTION_NAME;

Result2 rows · 3 columns

FUNCTION_NAME	IS_SECURE	FUNCTION_DEFINITION
HIDDEN_T141	YES	N * 3
WITH_TAX_T141	NO	AMOUNT * 1.20

IS_SECURE says YES and the body is right there in the grid. And GET_DDL gives it too.

Query

SELECT GET_DDL('FUNCTION', 'HIDDEN_T141(INT)') AS THE_SECURE_DDL;

Result1 row · 1 column

THE_SECURE_DDL
CREATE OR REPLACE SECURE FUNCTION "HIDDEN_T141"("N" NUMBER(38,0)) RETURNS NUMBER(38,0) LANGUAGE SQL AS ' N * 3 ';

That is not SECURE failing. It is SECURE working as documented, because this session owns the function and an owner can always read its own definition. SECURE is about what other roles can see.

This account has one role to test with, so that is all this demo can honestly claim about it.

What does a body refuse, and where?

Six refusals, and the interesting part is which ones happen at create time and which wait for the call. First, two statements in one body.

Query

CREATE OR REPLACE FUNCTION TWOBODY_T141()
RETURNS INT
AS
$$
    SELECT 1;
    SELECT 2
$$;

Query failed — Snowflake returned an error

000008 (0A000): Actual statement count 2 did not match the desired statement count 1.

000008. Two statements did not match the desired statement count of one.

A body that inserts

CREATE OR REPLACE FUNCTION ANINSERT_T141()
RETURNS INT
AS
$$
    INSERT INTO SALES_T141 (SALE_ID) VALUES (99)
$$;

Query failed — Snowflake returned an error

090203 (42601): Compilation of SQL UDF failed: SQL compilation error:
syntax error line 2 at position 11 unexpected 'INTO'.
syntax error line 2 at position 28 unexpected 'SALE_ID'.
syntax error line 2 at position 37 unexpected 'VALUES'.

090203, a syntax error at INTO. A body is an expression, not a statement, so the parser never expected an INSERT there at all.

Now a body that returns two columns.

Query

CREATE OR REPLACE FUNCTION TWOCOLS_T141()
RETURNS INT
AS
$$
    SELECT SALE_ID, AMOUNT FROM SALES_T141 LIMIT 1
$$;

Query failed — Snowflake returned an error

090207 (42601): Declared return type 'NUMBER(38,0)' is incompatible with actual return type 'ROW(NUMBER(38,0), NUMBER(10,2))'

090207 again, and look at the actual type it reports. A row of two numbers.

And a body that returns two rows?

Query

CREATE OR REPLACE FUNCTION TWOROWS_T141()
RETURNS INT
AS
$$
    SELECT SALE_ID FROM SALES_T141
$$;

Result1 row · 1 column

status
Function TWOROWS_T141 successfully created.

Created. Successfully. No complaint at all. So let's call it.

Query

SELECT TWOROWS_T141() AS WHAT_CAME_BACK;

Query failed — Snowflake returned an error

090150 (22000): Single-row subquery returns more than one row.

090150, single-row subquery returns more than one row.

Where the error happens

Two columns is a create time error. Two rows is a call time one.
Worth knowing which is which, because the second kind sits in your catalog looking fine.

Then two more. No RETURNS clause at all.

Query

CREATE OR REPLACE FUNCTION NORETURN_T141(N INT)
AS
$$
    N + 1
$$;

Query failed — Snowflake returned an error

090202 (42601): Missing result type in function definition.

And a body naming a table that is not there.

Query

CREATE OR REPLACE FUNCTION NOTABLE_T141()
RETURNS INT
AS
$$
    SELECT COUNT(*) FROM NO_SUCH_TABLE_T141
$$;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.PUBLIC.NO_SUCH_TABLE_T141' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.PUBLIC.NO_SUCH_TABLE_T141.

Refused at create time. So tables in a body are resolved when you create the function. Which makes the last section of this post the interesting one.

And two mistakes at the calling end, for completeness.

Too many arguments

SELECT WITH_TAX_T141(1.00, 2.00) AS WRONG_ARITY;

Query failed — Snowflake returned an error

000939 (22023): SQL compilation error: error line 1 at position 7
too many arguments for function [WITH_TAX_T141(1, 2)] expected 1, got 2

A name that is not there

SELECT NO_SUCH_FUNCTION_T141(1) AS NOT_A_FUNCTION;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function NO_SUCH_FUNCTION_T141.

One last thing: the dependency nobody tracks

We have a working function that reads SALES_T141. Let's drop the table out from under it.

Query

DROP TABLE SALES_T141;

Result1 row · 1 column

status
SALES_T141 successfully dropped.

Now, does the function still exist?

SHOW USER FUNCTIONS

SHOW USER FUNCTIONS LIKE 'REGION\\_TOTAL\\_T141';

Result1 row · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function
2026-10-03 13:52:49.180000-07:00	REGION_TOTAL_T141	PUBLIC	N	N	N	1	1	REGION_TOTAL_T141(VARCHAR) RETURN NUMBER	user-defined function	DEMO_DB	N	N	N			N	SQL	N	N	N

It does. Listed happily, with its signature and its return type, exactly as before. Nothing in that grid hints that anything is wrong.

So let's call it.

Query

SELECT REGION_TOTAL_T141('north') AS NORTH;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.PUBLIC.SALES_T141' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.PUBLIC.SALES_T141.

002003. The object the body names does not exist.

And the view we built on the other function?

Query

SELECT COUNT(*) AS ROWS_THE_VIEW_SHOWS FROM TAXED_VIEW_T141;

Query failed — Snowflake returned an error

002037 (42601): SQL compilation error:
Failure during expansion of view 'TAXED_VIEW_T141': SQL compilation error:
Object 'DEMO_DB.PUBLIC.SALES_T141' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on TABLE DEMO_DB.PUBLIC.SALES_T141.

002037, a failure during expansion of the view, with the missing table named inside it.

Let's bring the table back.

Query

UNDROP TABLE SALES_T141;

Result1 row · 1 column

status
Table SALES_T141 successfully restored.

And the function

SELECT REGION_TOTAL_T141('north') AS NORTH;

Result1 row · 1 column

NORTH
100.00

100.00, which is exactly what it gave before the drop. Everything works again.

The thing to take away

Nothing recorded that dependency, and nothing had to. DROP TABLE did not warn us, and SHOW USER FUNCTIONS did not either.
A SQL UDF resolves its tables when you create it and again when you call it, and in between it is just text.

And the teardown

Functions are dropped by signature, not by name, which is the same reason DESCRIBE needed one earlier.

Teardown

DROP VIEW IF EXISTS TAXED_VIEW_T141;

Result1 row · 1 column

status
TAXED_VIEW_T141 successfully dropped.

Teardown

DROP FUNCTION IF EXISTS WITH_TAX_T141(NUMBER(10,2));

Result1 row · 1 column

status
WITH_TAX_T141 successfully dropped.

Teardown, one overload

DROP FUNCTION IF EXISTS LABEL_T141(INT);

Result1 row · 1 column

status
LABEL_T141 successfully dropped.

Teardown, the other overload

DROP FUNCTION IF EXISTS LABEL_T141(VARCHAR);

Result1 row · 1 column

status
LABEL_T141 successfully dropped.

Two drops for one name, because there are two functions.

Teardown, for a table that was never created

DROP TABLE IF EXISTS COMPUTED_T141;

Result1 row · 1 column

status
Drop statement executed successfully (COMPUTED_T141 already dropped).

That message is different from the others, because the computed-column table was refused at create time. There was nothing to drop and IF EXISTS said so.

And the checks.

Where we are

SELECT CURRENT_DATABASE() AS THE_DATABASE,
       CURRENT_SCHEMA()   AS THE_SCHEMA;

Result1 row · 2 columns

THE_DATABASE	THE_SCHEMA
DEMO_DB	PUBLIC

And what is left

SHOW USER FUNCTIONS LIKE '%\\_T141';

Result0 rows · 21 columns · scroll →

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	secrets	external_access_integrations	is_external_function	language	is_memoizable	is_data_metric	is_ai_function

And the tables

SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND ENDSWITH(TABLE_NAME, '_T141');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

Still in PUBLIC, no functions of ours left and no tables of ours left. Back where we started.

Key points

A scalar SQL UDF is a signature, a RETURNS clause and a body that is one SQL expression.
DESCRIBE FUNCTION gives you the signature, the return type, the language and the body itself, in four property rows.
It works in a select list, a WHERE, an ORDER BY and a GROUP BY.
The RETURNS clause does not cast anything. A function declaring NUMBER(10,2) gave three decimal places, and one declaring INT gave 3.500000.
That is the identical result the bare expression gives on its own, to the same six decimal places.
A function declaring VARCHAR(5) handed back all ten characters it was given.
What Snowflake does check is type compatibility, at create time. A number declared over a string body is 090207.
RETURNS NULL ON NULL INPUT is recorded in IS_NULL_CALL and not honoured. The body ran anyway.
Overloading resolves by argument type, and a date takes the string version. With overloads, DESCRIBE needs the signature.
A body may call another UDF but never itself. A cycle is 090206, at create time.
A UDF can be a column DEFAULT, but a default is a constant and cannot see its own row's other columns.
Two columns in a body fails when you create it. Two rows fails only when you call it.
A body is an expression, not a statement. One of them, and no INSERT.
Drop the table a UDF reads and the UDF stays listed and stops working. UNDROP the table and it works again.

Next in this series

Episode 142 — SQL table functions. A function that gives back a whole table instead of one value, the word TABLE you cannot leave out, and a comma join that quietly drops a row.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The SECURE section is one role looking at its own function, and the post says so where it says it. The last three grids show the session back in PUBLIC with nothing of its own left behind. Every count in the text is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake CREATE FUNCTION documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 141 - My Function Said RETURNS INT And Handed Back 3.5

Let's create some sample data

Let's write a function

Now let's see what the catalog keeps

Where can a function go?

Now back to that number: RETURNS is a promise, not a cast

So what does Snowflake check?

The null clause is metadata too

What can a body do?

A body can read a table

A body can be a column default

A body can back a view

But not a virtual column

What about a secure function?

What does a body refuse, and where?

One last thing: the dependency nobody tracks

And the teardown

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 141 - My Function Said RETURNS INT And Handed Back 3.5

Let's create some sample data

Let's write a function

Now let's see what the catalog keeps

Where can a function go?

Now back to that number: RETURNS is a promise, not a cast

So what does Snowflake check?

The null clause is metadata too

Can two functions share a name?

What can a body do?

A body can read a table

A body can be a column default

A body can back a view

But not a virtual column

What about a secure function?

What does a body refuse, and where?

One last thing: the dependency nobody tracks

And the teardown

Key points

No comments:

Post a Comment