I have 2 functions that I want to apply across a whole table in Snowflake. However when I try this the behaviour is different that expected.

The 2 functions in question are:

CREATE OR REPLACE FUNCTION postgres_generate_series(x_start float, x_end float, stepsize float)
 RETURNS table (series float)
AS
-- Max series size is 10000
$$ 
select (x_start (-1   (row_number()) over(order by 0))*stepsize) i
from (select x_start, x_end)
join table(generator(rowcount => 10000)) x
qualify i <= x_end
$$
;

and

CREATE OR REPLACE FUNCTION myfunction(a float, b float, c float)
 RETURNS float
AS
$$ 
select sum(1/(1 exp(-(series - c)/4)))
from (select 1),
table(postgres_generate_series(a 1,b,1::float))
$$
;

When I execute these function as follows:

select myfunction(1,10,1); -- answer 6.827147862 as expected
select myfunction(1,100,1); -- answer 96.472200224 as expected

I get the expected results. However when I run the following query:

select
    a,
    b,
    c,
    myfunction(a, b, c)
from
    (
        select
            1 as a, 10 as b, 1 as c
        union
        select
            1 as a, 100 as b, 1 as c
    );

I get the following error.

How can I rewrite this query so that I can execute it across all rows of a table?

CodePudding user response：

It works when unnecessary JOINs ((select x_start, x_end) join and (select 1), are removed from functions:

CREATE OR REPLACE FUNCTION postgres_generate_series(x_start float, x_end float,
                                                  stepsize float)
 RETURNS table (series float)
AS
-- Max series size is 10000
$$ 
select (x_start (-1   (row_number()) over(order by 0))*stepsize) i
from table(generator(rowcount => 10000)) x
qualify i <= x_end
$$;

CREATE OR REPLACE FUNCTION myfunction(a float, b float, c float)
 RETURNS float
AS
$$ 
select sum(1/(1 exp(-(series - c)/4)))
from table(postgres_generate_series(a 1,b,1::float))
$$;

select    a,    b,    c,    myfunction(a, b, c)
from ( select 1 as a, 10 as b, 1 as c
       union
       select 1 as a, 100 as b, 1 as c
    );

Output: