Attempting to create a stored procedure with 'execute as owner' in order to return a list of tables. The SP was successfully created, however calling it results in an error.
create database garbage;
CREATE OR REPLACE procedure garbage.public.sandbox_tables()
RETURNS TABLE (TABLE_SCHEMA varchar,TABLE_NAME varchar)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
as
$$
var sql_cmd = `select TABLE_SCHEMA,TABLE_NAME
from SNOWFLAKE.ACCOUNT_USAGE.TABLES
where TABLE_CATALOG ='SANDBOX'
and table_type='BASE TABLE'
and DELETED is NULL
and current_date()-CREATED::date >=90`;
var rs = snowflake.execute( {sqlText: sql_cmd} );
rs.next();
var result = rs.getColumnValue();
return result
$$;
call garbage.public.sandbox_tables()
Error:
SQL compilation error: Invalid identifier GARBAGE.PUBLIC.SANDBOX_TABLESThe specific cause of the invalid identifier error is unclear. Any clarification on the functions next() and getColumnValue(), including their accepted parameters, would be greatly appreciated.