Snowflake function unable to return multiple rows

June 3, 2024

I am trying to create a function in Snowflake that returns to me the rows matching a condition, but it only works when there is only one row matching the condition.

I am tried to create a function in Snowflake such as:

CREATE OR REPLACE FUNCTION test (field_value STRING)
RETURNS STRING
AS
$$
  SELECT 
        value_x
  FROM mapping_table_test 
  WHERE field_value = num1
$$;

We are assuming a very simple table, mapping_table_test, with a field called num1 and a field named value_x. The problem is that the function works if there is only a unique value in the num1 column. If there are more, I get this message: Single-row subquery returns more than one row.

Example: If there is only a value "10", the following calling to function works, but not if the value appears more than once in num1.

SELECT test(10);

I tried to change part of it so it returns a table instead of a string, but it does not work.

RETURNS TABLE(mapped_value STRING)

When trying to call the function this way, it simply tells me that it does not exists.

>Solution :

When using a tabular User Defined Function you must call the function within the FROM clause:

CREATE FUNCTION t()
    RETURNS TABLE(msg VARCHAR)
    AS
    $$
        SELECT 'Hello'
        UNION
        SELECT 'World'
    $$;


SELECT msg 
    FROM TABLE(t())
    ORDER BY msg;