Using Input of Snowflake Function as the From clause

user_9843525 0
Viewed 789

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


$$
;
snowflake-cloud-data-platform sql-function function sql
2 Answers

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

user_19691520

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', '');
user_127563810
Related