Updated on 2025-09-04 GMT+08:00

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