Access Object
If no schema is specified for a stored procedure, the stored procedure searches for objects based on the sequence specified by SEARCH_PATH. As a result, unexpected objects may be accessed. If tables, stored procedures, and other database objects with the same name exist in different schemas, unexpected results may occur if the schema is not specified. Therefore, it is recommended that you always explicitly specify a schema when a stored procedure accesses a data object.
Example:
-- Create two different schemas. gaussdb=# create schema best_practices_for_procedure1; CREATE SCHEMA gaussdb=# create schema best_practices_for_procedure2; CREATE SCHEMA -- Create the same stored procedure in two different schemas. 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 -- Calling the same stored procedure with different search_path settings may lead to differences. 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot