Snowflake create views dynamically via stored procedure

Question

I am having a heck of a time getting this to deploy, I am needing to offload snowflake data into a dataframe. The process we have to read snowflake cannot execute a procedure, but can only call a table or execute a simple query. The data types differ between snowflake and some parquet files that are in essence the same data, but another system wrote them differently.

My solution is to read the information schema right now for one schema, and table. I then need to create a string that casts the data to be compatible with the other parquet files.

I have written similar procedures in T-SQL, postgresql, plsql, and nzsql. The logic should be pretty solid when I try to deploy the DDL I get an error on the loop, what am I doing wrong, can snowflake loop over a table I think the FOR i IN (SELECT * FROM SYS_INFO_OUT) statement is the issue.

I have tried changing the syntax of the loop structure to no avail, any insight would be hugely appreciated.

CREATE OR REPLACE PROCEDURE SCHEMA.VIEW_BUILDER()
returns varchar()
language sql
AS 
$$
DECLARE 
    qry_head varchar;
    qry_stmt varchar;
BEGIN
    DROP TABLE SYS_INFO_OUT;
    CREATE TEMP TABLE SYS_INFO_OUT AS
        SELECT tbl.TABLE_NAME,
            col.COLUMN_NAME,
            col.DATA_TYPE,
            COALESCE(col.CHARACTER_MAXIMUM_LENGTH,col.CHARACTER_OCTET_LENGTH,col.NUMERIC_PRECISION ,col.DATETIME_PRECISION) AS COL_LENGTH,
            col.ORDINAL_POSITION,
            col.IS_NULLABLE,
            CASE WHEN nvl(col.NUMERIC_SCALE,0) = 0 THEN 0 ELSE 1 END AS is_decimal
        FROM INFORMATION_SCHEMA.TABLES AS tbl
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON tbl.TABLE_NAME = col.TABLE_NAME AND tbl.TABLE_SCHEMA = col.TABLE_SCHEMA
        WHERE tbl.TABLE_SCHEMA ='SOME_SCHMEA' AND tbl.TABLE_NAME ='SOME_TABLE'
        ORDER BY tbl.TABLE_NAME,
            col.ORDINAL_POSITION ASC;           
    FOR i IN (SELECT * FROM SYS_INFO_OUT )
    --Case statement to handle types
        SELECT 
            CASE DATA_TYPE
                WHEN 'NUMBER' THEN 
                    (
                    SELECT CASE 
                                WHEN is_decimal =1  THEN sql_stmt := sql_stmt + '(CAST '+ COLUMN_NAME +' as NUMBER(38,18)) as ' + COLUMN_NAME +','
                                ELSE sql_stmt := sql_stmt + '(CAST '+ COLUMN_NAME +' as varchar ) as ' + COLUMN_NAME +','
                            END )
                WHEN  'TEXT' THEN sql_stmt := sql_stmt + '(CAST '+ COLUMN_NAME +' as varchar ) as ' + COLUMN_NAME +','
                ELSE sql_stmt := sql_stmt + '(CAST '+ COLUMN_NAME +' as varchar ) as ' + COLUMN_NAME +','
            END
            sql_stmt = sql_stmt + ' '+ COLUMN_NAME
            sql_stmt = left(sql_stmt,(LENGTH(sql_stmt)-1))
    END LOOP;
        sql_stmt := sql_stmt +' FROM SOME_SCHMEA.SOME_TABLE'
        qry_head :=  'CREATE OR REPLACE VIEW ANOTHER_SCHEMA.VDF_SOME_TABLE AS SELECT ';
    EXECUTE IMMEDIATE (qry_head+sql_stmt);
    RETURN 'done';
END;
$

Answer 1

Cleaned up the procedure code a bit. Replace the needed logic inside the CURSOR FOR LOOP

CREATE OR REPLACE PROCEDURE VIEW_BUILDER()
returns varchar
language sql
AS 
$$
DECLARE 
    qry_head varchar := '';
    qry_stmt varchar := '';
    sql_stmt varchar := '';
BEGIN
    DROP TABLE IF EXISTS SYS_INFO_OUT;
    CREATE or replace TEMPORARY TABLE SYS_INFO_OUT AS
        SELECT tbl.TABLE_NAME,
            col.COLUMN_NAME,
            col.DATA_TYPE,
            COALESCE(col.CHARACTER_MAXIMUM_LENGTH,col.CHARACTER_OCTET_LENGTH,col.NUMERIC_PRECISION ,col.DATETIME_PRECISION) AS COL_LENGTH,
            col.ORDINAL_POSITION,
            col.IS_NULLABLE,
            CASE WHEN nvl(col.NUMERIC_SCALE,0) = 0 THEN 0 ELSE 1 END AS is_decimal
        FROM INFORMATION_SCHEMA.TABLES AS tbl
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON tbl.TABLE_NAME = col.TABLE_NAME AND tbl.TABLE_SCHEMA = col.TABLE_SCHEMA
        WHERE tbl.TABLE_SCHEMA ='SOME_SCHMEA' AND tbl.TABLE_NAME ='SOME_TABLE'
        ORDER BY tbl.TABLE_NAME,
         col.ORDINAL_POSITION ASC;           
        let c1 cursor for SELECT * FROM SYS_INFO_OUT ;
        for record in c1 do
            qry_head:='x'; --- replace with code as needed
        end for;
    sql_stmt := sql_stmt ||'* FROM PUBLIC.TABLEA';
    qry_head :=  'CREATE OR REPLACE VIEW PUBLIC.VDF_SOME_TABLE AS SELECT ';
    EXECUTE IMMEDIATE (qry_head||sql_stmt);
    RETURN 'done';
END;
$$
;

call VIEW_BUILDER();

VIEW_BUILDER
done

Checked if view being created in procedure is getting created -

select count(*) from PUBLIC.VDF_SOME_TABLE;

COUNT(*)
1