Redshift删除特定沙箱Schema外部表报错：无法在存储过程执行DROP EXTERNAL TABLE

问题背景 #

需求：删除Redshift特定沙箱（singh_sandbox）中指定Schema（deleted）下的所有外部表。
编写存储过程后执行报错：

ERROR Exception: DROP EXTERNAL TABLE cannot be executed from a function or procedure

涉及的存储过程代码：

CREATE OR REPLACE PROCEDURE "workspace"."qw"()
AS
$$
DECLARE
	t_sql                      VARCHAR(32000);
	t_script_name              VARCHAR(100) := 'load_sample_dcdr$table_cleanup';
	t_table_name               VARCHAR(100);
	t_start_runtime             TIMESTAMP;
	t_row_count                  BIGINT;
	t_current_db_YYYYMM            VARCHAR;
	t_current_db            VARCHAR(100);
	cur_loop                   REFCURSOR;
BEGIN
--  IF UPPER(t_current_db) = UPPER(current_database()) THEN
	t_sql := 'select distinct(table_name) from svv_all_columns where schema_name=''deleted'' and database_name=''singh_sandbox''';

	OPEN cur_loop FOR EXECUTE t_sql;
       LOOP
        FETCH cur_loop INTO t_table_name;
        EXIT WHEN NOT FOUND;
             EXECUTE 'DROP  table IF EXISTS deleted.'||t_table_name||' cascade';
           
            t_row_count=t_row_count+1;

       END LOOP;
    CLOSE cur_loop;
--  END IF;
END;
$$ LANGUAGE plpgsql;

问题原因 #

Redshift的PL/pgSQL存储过程对部分DDL操作存在限制，DROP EXTERNAL TABLE属于受限操作，无法在存储过程内部通过EXECUTE语句执行。

替代解决方案 #

方案1：生成删除脚本手动执行 #

先运行以下查询，生成所有目标外部表的删除语句：

SELECT 'DROP EXTERNAL TABLE IF EXISTS deleted.' || table_name || ' CASCADE;'
FROM svv_all_columns 
WHERE schema_name = 'deleted' 
  AND database_name = 'singh_sandbox'
GROUP BY table_name;

将查询结果导出为SQL脚本，直接在Redshift客户端（如psql、DataGrip）中执行即可完成批量删除。

方案2：用脚本语言批量执行（以Python为例） #

通过psycopg2连接Redshift，自动查询并删除目标表：

import psycopg2

# 配置Redshift连接参数
conn_params = {
    'dbname': 'your_database',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_redshift_endpoint',
    'port': '5439'
}

# 建立连接并执行删除操作
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

# 查询所有需要删除的外部表
cur.execute("""
    SELECT DISTINCT table_name 
    FROM svv_all_columns 
    WHERE schema_name = 'deleted' 
      AND database_name = 'singh_sandbox'
""")

tables = cur.fetchall()

for (table_name,) in tables:
    drop_sql = f"DROP EXTERNAL TABLE IF EXISTS deleted.{table_name} CASCADE;"
    cur.execute(drop_sql)
    print(f"已删除表: deleted.{table_name}")

conn.commit()
cur.close()
conn.close()

方案3：使用AWS API自动化执行 #

通过AWS CLI调用Redshift的execute-statement接口，批量执行生成的删除语句，适合自动化运维场景。

内容的提问来源于stack exchange，提问作者Kartikeya Singh