Snowflake Store Procedure Execute as Owner

Question

I am trying to create an SP with execute as an owner to return a list of tables. SP was getting created but on calling it is throwing error.

create database trash;
CREATE OR REPLACE procedure trash.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 trash.public.sandbox_tables()

Error: SQL compilation error: Invalid identifier TRASH.PUBLIC.SANDBOX_TABLES

Not sure what was the Invalid identifier and if you could explain what is next() doing and what parameters can go into getColumnValue function/method would be appreciated.

waldente waldente · Accepted Answer · 2020-01-09T01:44:02

Stored procedures don't return tables, so I don't think it was actually created. Here's a workaround: Using RESULT_SCAN to Retrieve the Result from a Stored Procedure

Snowflake Store Procedure Execute as Owner

1 Answers