Cannot create a temporary stage object in a Snowflake stored procedure

Question

The below stored procedure execution returns the error message 'Stored procedure execution error: Unsupported statement type 'temporary STAGE'.' Removing the keyword 'temporary' from the stored procedure definition solves the problem and it can be executed without any error.

How is it possible ? As per its documentation, Snowflake supports the creation of temporary stage. On top of that, the same SQL statement works properly once it is executed outside a stored procedure.

CREATE OR REPLACE PROCEDURE "TEST_TEMP_STAGE"()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
STRICT
EXECUTE AS OWNER
AS $$
    var req='create  temporary stage my_temp_stage;'
    snowflake.createStatement( { sqlText: req } ).execute();
    return 0;
$$

Answer 1

You should define your procedure as "EXECUTE AS CALLER" to be able to create a temporary stage inside your stored procedure.

CREATE OR REPLACE PROCEDURE "TEST_TEMP_STAGE"()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
STRICT
EXECUTE AS CALLER
AS $$
    var req='create temporary stage my_temp_stage;'
    snowflake.createStatement( { sqlText: req } ).execute();
    return 0;
$$
;

call TEST_TEMP_STAGE();