访问对象
未指定SCHEMA的存储过程将依据SEARCH_PATH的顺序查找对象,可能导致访问到非预期对象。如果不同模式中存在同名表、存储过程以及其他数据库对象,未明确指定SCHEMA可能引发意外结果。因此,建议在存储过程访问数据对象时,始终明确指定SCHEMA。
示例:
--创建两个不同的schema。 gaussdb=# create schema best_practices_for_procedure1; CREATE SCHEMA gaussdb=# create schema best_practices_for_procedure2; CREATE SCHEMA --在两个不同的schema下创建相同的存储过程。 gaussdb=# create or replace procedure best_practices_for_procedure1.proc1() as begin dbe_output.print_line('in schema best_practices_for_procedure1'); end; / CREATE PROCEDURE gaussdb=# create or replace procedure best_practices_for_procedure2.proc1() as begin dbe_output.print_line('in schema best_practices_for_procedure2'); end; / CREATE PROCEDURE --在不同的search_path下调用相同的存储过程可能存在差异。 gaussdb=# set search_path to best_practices_for_procedure1, best_practices_for_procedure2; SET gaussdb=# call proc1(); in schema best_practices_for_procedure1 proc1 ------- (1 row) gaussdb=# reset search_path; RESET gaussdb=# set search_path to best_practices_for_procedure2, best_practices_for_procedure1; SET gaussdb=# call proc1(); in schema best_practices_for_procedure2 proc1 ------- (1 row) gaussdb=# reset search_path; RESET gaussdb=# drop schema best_practices_for_procedure1 cascade; NOTICE: drop cascades to function best_practices_for_procedure1.proc1() DROP SCHEMA gaussdb=# drop schema best_practices_for_procedure2 cascade; NOTICE: drop cascades to function best_practices_for_procedure2.proc1() DROP SCHEMA