[SOLVED] Cannot create UDFs returning "Table" in Snowflake – compilation error

Issue

USE AGDWHDEV.EDW_WEATHER; -- My database and schema

CREATE OR REPLACE function EDW_WEATHER.find_nearest_radar()
  returns table (LATITUDE number, LONGITUDE number)
  as 'SELECT 1 LATITUDE, 1 LONGITUDE';

-- The function compiled successfully but when I ran it:

select EDW_WEATHER.find_nearest_radar();

-- I get this:

002141 (42601): SQL compilation error:

Unknown user-defined function EDW_WEATHER.FIND_NEAREST_RADAR

If I change the return type to a FLOAT (non-Table) or any simple return type, it works. Determined, I search the web and followed this example, at:

https://www.bmc.com/blogs/snowflake-user-defined-functions/

in the section "Snowflake table function"

but I got the same error! What am I doing wrong?

Solution

Because it returns a table, you cannot directly select it. It needs to be a source, thus wrapped in a TABLE and called like:

CREATE OR REPLACE function edw_weather.find_nearest_radar()
  returns table (LATITUDE number, LONGITUDE number)
  as 'SELECT 1, 1';

SEECT * 
FROM TABLE(edw_weather.find_nearest_radar());

gives:

note: the SELECT 1 LATITUDE, 1 LONGITUDE can be just SELECT 1,1 because the names of the columns are declared in the output. But often with aliases are used it’s less error prone to use an AS to help show you are meaning for a rename/etc verse having missed a comma.
SELECT 1 AS latitude, 1 AS longitude

Answered By – Simeon Pilgrim

Answer Checked By – Jay B. (BugsFixing Admin)