Returning tabular data from Snowflake SQL procedure-CodePudding

I'm trying to write a stored procedure that will return the results of a complex query:

CREATE OR REPLACE PROCEDURE sp_partition_ohlc(symbol VARCHAR(10), 
                                              from_time NUMERIC(20, 0), 
                                              til_time NUMERIC(20, 0),  
                                              step_time NUMERIC(20, 0))
    RETURNS TABLE()
    LANGUAGE SQL
    AS
    $$
    BEGIN
        SELECT 
            :step_time * bucket AS t,
            SUM(T.volume) AS v,
            MAX(T.open) AS o,
            MAX(T.close) AS c,
            MAX(T.highlow) AS h,
            MIN(T.highlow) AS l,
            v * (h   l   c) / 3 AS wv,
            COUNT(*) AS n
        FROM (
            SELECT
                FLOOR(T.sip_timestamp / :step_time) AS bucket,
                cta_calc(T.size, T.conditions, 'VOLUME') AS volume,
                cta_calc(T.price, T.conditions, 'HIGHLOW') AS highlow,
                IFF(ROW_NUMBER() OVER (PARTITION BY bucket ORDER BY T.sip_timestamp) = 1, T.price, NULL) AS open,
                LAST_VALUE(cta_calc(T.price, T.conditions, 'LAST')) 
                    IGNORE NULLS OVER (PARTITION BY bucket ORDER BY T.sip_timestamp) AS close
            FROM trades AS T
            WHERE 
                T.symbol = :symbol AND 
                T.sip_timestamp >= :from_time AND
                T.sip_timestamp < :til_time) AS T
        GROUP BY bucket
        ORDER BY bucket;
    END
    $$

This compiles properly but, if I try to run it:

CALL sp_partition_ohlc('NTAP', 1640995200000000000, 1672531200000000000, 3600000000000)

I get the following error:

092222 (P0000): SQL compilation error: stored procedure is missing a return statement

From this, I understand that I should have a RETURN in front of my outer SELECT statement. However, when I modify the procedure thus, it fails to compile with this error:

Syntax error: unexpected 'SELECT'. (line 7) syntax error line 4 at position 23 unexpected '*'. (line 7)

I'm not sure what this error is telling me as, when I run the query by itself, it returns results. I assume I'm doing something wrong in setting up the procedure but I'm not sure what it is from the associated documentation, here. Any help would be appreciated.

CodePudding user response：

It is not a matter of just putting a return in there:

you more want to follow a pattern like:

create or replace procedure get_top_sales()
returns table (sales_date date, quantity number)
language sql
as
declare
  res resultset default (select sales_date, quantity from sales order by quantity desc limit 10);
begin
  return table(res);
end;

here the res is a declared as the result set, which is the SQL to run, so you can access those, and then it is returned as a table via table(res) and the type of the res matches the return type of the function returns table (sales_date date, quantity number), so for your function you would need to alter this also.

so something like (I have not run):

CREATE OR REPLACE PROCEDURE sp_partition_ohlc(symbol VARCHAR(10), 
                                              from_time NUMERIC(20, 0), 
                                              til_time NUMERIC(20, 0),  
                                              step_time NUMERIC(20, 0))
    RETURNS TABLE(t int, v float, o float, c float, h float, l float, wv float, n int) /* guessed types */
    LANGUAGE SQL
    AS
    $$
    DECLARE
        res resultset;
    BEGIN
    
        let res := (
            SELECT 
                :step_time * bucket AS t,
                SUM(T.volume) AS v,
                MAX(T.open) AS o,
                MAX(T.close) AS c,
                MAX(T.highlow) AS h,
                MIN(T.highlow) AS l,
                v * (h   l   c) / 3 AS wv,
                COUNT(*) AS n
            FROM (
                SELECT
                    FLOOR(T.sip_timestamp / :step_time) AS bucket,
                    cta_calc(T.size, T.conditions, 'VOLUME') AS volume,
                    cta_calc(T.price, T.conditions, 'HIGHLOW') AS highlow,
                    IFF(ROW_NUMBER() OVER (PARTITION BY bucket ORDER BY T.sip_timestamp) = 1, T.price, NULL) AS open,
                    LAST_VALUE(cta_calc(T.price, T.conditions, 'LAST')) 
                        IGNORE NULLS OVER (PARTITION BY bucket ORDER BY T.sip_timestamp) AS close
                FROM trades AS T
                WHERE 
                    T.symbol = :symbol AND 
                    T.sip_timestamp >= :from_time AND
                    T.sip_timestamp < :til_time) AS T
            GROUP BY bucket
            ORDER BY bucket
        );
        
        return table(res);
    END
    $$