Cannot create UDFs returning "Table" in Snowflake - compilation error

Question

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?

Answer 1

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';

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

gives:

LATITUDE	LONGITUDE
1	1

note: the SELECT 1 LATITUDE, 1 LONGITUDE can be changed to 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