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

Naming Convention

Improper stored procedure and variable naming may adversely affect system usage.

  • The name of a stored procedure, variable, or type can contain a maximum of 63 characters. If this limit is exceeded, the name is automatically truncated to 63 characters.
    gaussdb=# CREATE SCHEMA best_practices_for_procedure;
    CREATE SCHEMA
    
    -- When a stored procedure name containing 66 characters is created, a message is displayed, indicating that the name is truncated to 63 characters.
    gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011() AS
    BEGIN
        NULL;
    END;
    /
    NOTICE:  identifier "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011" will be truncated to "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891"
    CREATE PROCEDURE
    
    -- When a variable name containing 66 characters is created, a message is displayed, indicating that the name is truncated to 63 characters.
    gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.proc1(abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011 int) as
    BEGIN
        NULL;
    END;
    /
    NOTICE:  identifier "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891011" will be truncated to "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891"
    CREATE PROCEDURE
    
    gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE;
    NOTICE:  drop cascades to 2 other objects
    DETAIL:  drop cascades to function best_practices_for_procedure.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz01234567891()
    drop cascades to function best_practices_for_procedure.proc1(integer)
    DROP SCHEMA
  • When creating a stored procedure, avoid using variables or types with the same name in different variable scopes. For details, see "Stored Procedures > Basic Statements > Variable Definition Statements > Scope of a Variable" in Developer Guide. Using variables and types with the same name in different variable scopes may reduce the readability of stored procedures and increase the maintenance difficulty.
    gaussdb=# CREATE SCHEMA best_practices_for_procedure;
    CREATE SCHEMA
    
    -- Create a stored procedure, create the same variable name in different variable scopes, and assign values.
    gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.proc1() AS
        name varchar2(10) := 'outer';
        age int := 2025;
    BEGIN
        DECLARE
        name varchar2(10) := 'inner'; -- This is only an example and is not recommended.
        age int := 2024; -- This is only an example and is not recommended.
        BEGIN
            dbe_output.print_line('inner name =' || name);
            dbe_output.print_line('inner age =' || age);
        END;
        dbe_output.print_line('outer name =' || name);
        dbe_output.print_line('outer age =' || age);
    END;
    /
    CREATE PROCEDURE
    
    -- Execute the stored procedure. The same variable name in different scopes actually refers to different variables.
    gaussdb=# CALL best_practices_for_procedure.proc1();
    inner name =inner
    inner age =2024
    outer name =outer
    outer age =2025
     proc1
    -------
    
    (1 row)
    
    gaussdb=# DROP SCHEMA best_practices_for_procedure cascade;
    NOTICE:  drop cascades to function best_practices_for_procedure.proc1()
    DROP SCHEMA
  • Do not use SQL keywords in stored procedure, internal variable, and data type names to ensure that the stored procedure can run properly in all scenarios.

    gaussdb=# CREATE SCHEMA best_practices_for_procedure;
    CREATE SCHEMA
    
    gaussdb=# 
    CREATE OR REPLACE PROCEDURE best_practices_for_procedure."as"() AS -- This is only an example and is not recommended.
    BEGIN
        NULL;
    END;
    /
    CREATE PROCEDURE
    
    -- A direct call will result in an error.
    gaussdb=# CALL as();
    ERROR:  syntax error at or near "as"
    LINE 1: call as();
                 ^
    gaussdb=# CALL best_practices_for_procedure."as"();
     as
    ----
    
    (1 row)
    
    gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE;
    NOTICE:  drop cascades to function best_practices_for_procedure."as"()
    DROP SCHEMA
  • When creating a stored procedure, avoid using the same name as system functions to prevent confusion. If the same name must be used, specify the schema during a call.

    gaussdb=# CREATE SCHEMA best_practices_for_procedure;
    CREATE SCHEMA
    
    -- Create an abs function with the same name as the abs system function in the schema. This is only an example and is not recommended.
    gaussdb=# 
    CREATE OR REPLACE FUNCTION best_practices_for_procedure.abs(a int) RETURN int AS
    BEGIN
        dbe_output.print_line('my abs funciton.');
        RETURN abs(a);
    END;
    /
    CREATE FUNCTION
    
    -- Call a stored procedure. If no schema is added, the abs system function is called.
    gaussdb=# CALL abs(-1);
     abs
    -----
       1
    (1 row)
    
    -- You are advised to add a schema.
    gaussdb=# CALL best_practices_for_procedure.abs(-1);
    my abs funciton.
     abs
    -----
       1
    (1 row)
    
    gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE;
    NOTICE:  drop cascades to function best_practices_for_procedure.abs(integer)
    DROP SCHEMA