AWS Redshift存储过程无法访问information_schema问题咨询

嘿，我一眼就发现了问题所在——你在拆分输入表名的时候，把schema和table的变量赋值搞反了！

问题根源 #

当你传入参数'dev.abc'时，split_part(tablename,'.',1)提取的是.分隔符前的部分dev（这其实是schema名称），split_part(tablename,'.',2)提取的是.后的abc（这是表名称）。但你却把这两个值分别赋值给了table_name和schema_name变量，导致后续查询逻辑完全偏离了预期：

你的存储过程里执行的是：

counts := (select count(*) from information_schema.tables where table_schema = schema_name);

这里的schema_name实际是abc，而不是你想要的dev，所以查询返回0（你的数据库里应该没有名为abc的schema）。而你在存储过程外直接查询table_schema = 'dev'，自然能得到正确结果。

修正后的存储过程 #

只需要调换变量赋值的顺序就能解决问题：

create or replace procedure dev.gp_information_schema_test (tablename varchar(64)) as $$
declare
 table_name varchar(64);
 schema_name varchar(64);
 counts int;
begin
 -- 正确赋值：第一部分是schema，第二部分是table
 schema_name := split_part(tablename,'.',1);
 table_name := split_part(tablename,'.',2);
 raise info 'table_name - %,Schema_name - %',table_name,schema_name;
 counts := (select count(*) from information_schema.tables where table_schema = schema_name);
 raise info 'count is -%',counts;
end;
$$ language plpgsql;

验证效果 #

重新调用存储过程：

call dev.gp_information_schema_test('dev.abc');

这次你会看到Schema_name - dev的提示信息，并且count值会和你在存储过程外执行select count(*) from information_schema.tables where table_schema = 'dev'的结果完全一致。

额外优化建议 #

为了让存储过程更健壮，可以处理用户只传入表名（不带schema）的情况：

create or replace procedure dev.gp_information_schema_test (tablename varchar(64)) as $$
declare
 table_name varchar(64);
 schema_name varchar(64);
 counts int;
begin
 -- 判断输入是否包含schema分隔符
 if position('.' in tablename) > 0 then
  schema_name := split_part(tablename,'.',1);
  table_name := split_part(tablename,'.',2);
 else
  -- 无schema时，使用当前会话的默认schema
  schema_name := current_schema();
  table_name := tablename;
 end if;
 raise info 'table_name - %,Schema_name - %',table_name,schema_name;
 counts := (select count(*) from information_schema.tables where table_schema = schema_name);
 raise info 'count is -%',counts;
end;
$$ language plpgsql;

内容的提问来源于stack exchange，提问作者Golokesh Patra