Using Input of Snowflake Function as the From clause

Question

I have a function that takes a list of inputs to draft a query, it is dynamic SQL but not complex in any way the inputs directly pass through to the from and select clauses. In Snowflake, I cannot get it to allow me to use the name of the input as the table name. I tried using the identifier function, but still no luck. Here is the function if anyone has any ideas.

CREATE OR REPLACE FUNCTION aggregate_string
(p_table_name VARCHAR
,p_column_name VARCHAR
,p_where_column_name VARCHAR
,p_where_value VARCHAR
,p_order_by_YN VARCHAR
,p_order_by_column VARCHAR
)

RETURNS VARCHAR

AS

$$

SELECT
    CASE 
        WHEN TRIM(upper(p_order_by_YN)) = 'Y' AND trim(p_order_by_column)> ' ' 
    THEN (SELECT as_array(p_column_name) from p_table_name where p_where_column_name = p_where_value order by p_order_by_column)
    ELSE (SELECT as_array(p_column_name) from p_table_name where p_where_column_name = p_where_value)
END AS V_CUR


$$
;

score 0 · Answer 1 · answered Mar 03 '20 at 22:57

Although the documentation doesn't explicitly call this out, I don't think the dynamic table name is possible with a UDF. However, it should be possible with a stored procedure, which allows for "Dynamically creating a SQL statement and execute it."

See https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#dynamically-creating-a-sql-statement for a good example of this (reproduced below):

Create the stored procedure. This procedure allows you to pass the name of a table and get the number of rows in that table (equivalent to select count(*) from table_name):

create or replace procedure get_row_count(table_name VARCHAR)
    returns float 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    // Note that we uppercased the input parameter name.
    var sql_command = "select count(*) from " + TABLE_NAME;
    // Run the statement.
    var stmt = snowflake.createStatement(
           {
           sqlText: sql_command
           }
        );
    var res = stmt.execute();
    // Get back the row count. Specifically, ...
    // ... first, get the first (and in this case only) row from the
    //  result set ...
    res.next();
    // ... then extract the returned value (which in this case is the
    // number of rows in the table).
    row_count = res.getColumnValue(1);
    return row_count;
    $$
    ;

Call the stored procedure:

call get_row_count('stproc_test_table1');
+---------------+
| GET_ROW_COUNT |
|---------------|
|             3 |
+---------------+

Show the results from select count(*) for the same table:

SELECT COUNT(*) FROM stproc_test_table1;
+----------+
| COUNT(*) |
|----------|
|        3 |
+----------+

Do understand that using the results of the procedure will be a little different from the function, see Values Returned by Stored Procedures Are Not Directly Usable in SQL

score 0 · Answer 2 · answered Mar 08 '20 at 01:54

If I understand correctly, the function is generating a SQL statement from the provided inputs. If that's the case, you can do this using a JavaScript UDF:

CREATE OR REPLACE FUNCTION aggregate_string
    (p_table_name VARCHAR
    ,p_column_name VARCHAR
    ,p_where_column_name VARCHAR
    ,p_where_value VARCHAR
    ,p_order_by_YN VARCHAR
    ,p_order_by_column VARCHAR
    )
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
        var sql = "select " + P_COLUMN_NAME + " from " + P_TABLE_NAME + " where " + P_WHERE_COLUMN_NAME + " = ";

        if (isNaN(P_WHERE_VALUE)) {
            sql += "'" + P_WHERE_VALUE.replace(/'/g, "''") + "'";
        } else {
            sql += P_WHERE_VALUE;
        }

        if (P_ORDER_BY_YN.toUpperCase() == 'Y') {
            sql += " order by " + P_ORDER_BY_COLUMN;
        }

        return sql;

    $$
    ;

    -- Where is not a number, so the where condition is single quoted
    select aggregate_string('TABLE_NAME1', 'COL1, COL2, COL3', 'WHERE_COLUMN1', 'WHERE_VALUE', 'Y', 'ORDER_BY_COL1');

    -- Where is a number (whether or not passed in single quotes), so the where condition is not quoted.
    select aggregate_string('TABLE_NAME1', 'COL1, COL2, COL3', 'WHERE_COLUMN1', 5, 'Y', 'ORDER_BY_COL1');

    -- Order by is NO, so there is no order by clause
    select aggregate_string('TABLE_NAME1', 'COL1, COL2, COL3', 'WHERE_COLUMN1', 'WHERE_VALUE', 'N', '');

Thank you very much for the thoughtful response - this does just return the SQL statement though correct? This does not actually execute the SQL statement? If I'm understanding the crux of the issue to execute the generated statement I would need to use a sproc, because the UDF does not support dynamic sql? — Holt, Mar 09 '20 at 14:42
Yes, the UDF sample would only generate the statement. I wasn't sure if you wanted just the generation or to execute it. Currently to run SQL in a UDF it needs to be a SQL UDF. The "snowflake" object that's in context in a JavaScript SP is not in context in a JavaScript UDF. I'll see if there's a way to generate and execute using SQL and get back to you. — Greg Pavlik, Mar 09 '20 at 16:30

Using Input of Snowflake Function as the From clause

2 Answers2