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

Compatibility

Due to differences in database compatibility, the behavior of stored procedures may be inconsistent under different compatibility settings or GUC parameters. Exercise caution when using these compatibility functions. Example:

  • Due to differences in compatibility, functions with output parameters may ignore output values in certain cases. After the GUC parameter behavior_compat_options is set to 'proc_outparam_override', some scenarios can ensure the correct return of output values and return values. However, since this function behaves differently under different compatibility settings, you are advised not to use functions with output parameters. Instead, you can use procedures with output parameters.
    gaussdb=# CREATE SCHEMA best_practices_for_procedure;
    CREATE SCHEMA
    
    -- Create a function with output parameters.
    gaussdb=#  CREATE OR REPLACE FUNCTION best_practices_for_procedure.func (a out int, b out int) RETURN int AS -- This is only an example and is not recommended.
        c int;
    BEGIN
        a := 1;
        b := 2;
        c := 3;
        RETURN c;
    END;
    /
    CREATE FUNCTION
    
    -- When a function with output parameters is called, it is found that no value is assigned to parameters a and b.
    gaussdb=# DECLARE
        a int;
        b int;
        c int;
    BEGIN
        c := best_practices_for_procedure.func(a, b);
        dbe_output.print_line('a := ' || a || ' b := ' || b || ' c := ' || c);
    END;
    /
    a :=  b :=  c := 3
    ANONYMOUS BLOCK EXECUTE
    
    -- Set the GUC parameter.
    gaussdb=# SET behavior_compat_options='proc_outparam_override';
    SET
    
    -- When the function with output parameters is called again, values are assigned to parameters a, b, and c.
    gaussdb=# DECLARE
        a int;
        b int;
        c int;
    BEGIN
        c := best_practices_for_procedure.func(a, b);
        dbe_output.print_line('a := ' || a || ' b := ' || b || ' c := ' || c);
    END;
    /
    a := 1 b := 2 c := 3
    ANONYMOUS BLOCK EXECUTE
    
    -- You are advised to use a stored procedure with output parameters to replace the function with output parameters. You can change the preceding function to the following stored procedure.
    gaussdb=# RESET behavior_compat_options;
    gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.proc (a OUT int, b OUT int, c OUT int) AS
    BEGIN
        a := 1;
        b := 2;
        c := 3;
    END;
    /
    CREATE PROCEDURE
    
    gaussdb=# DECLARE
        a int;
        b int;
        c int;
    BEGIN
        best_practices_for_procedure.proc(a, b, c);
        dbe_output.print_line('a := ' || a || ' b := ' || b || ' c := ' || c);
    END;
    /
    a := 1 b := 2 c := 3
    ANONYMOUS BLOCK EXECUTE
    
    gaussdb=# DROP SCHEMA best_practices_for_procedure cascade;
    NOTICE:  drop cascades to 2 other objects
    DETAIL:  drop cascades to function best_practices_for_procedure.func()
    drop cascades to function best_practices_for_procedure.proc()
    DROP SCHEMA
  • In dynamic statements, if placeholder names are the same, compatibility settings across different databases may cause placeholders to be bound to different variables, thereby affecting expected behavior. After the GUC parameter behavior_compat_options is set to 'dynamic_sql_compat', you can use placeholders with the same name to bind different variables. However, since this function behaves differently under different compatibility settings, you are advised not to use placeholders with the same name.
    gaussdb=# CREATE SCHEMA best_practices_for_procedure;
    CREATE SCHEMA
    
    gaussdb=# CREATE TABLE best_practices_for_procedure.tb1 (a int, b int);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Create a stored procedure that uses dynamic statements and bind the same placeholders to the same variables.
    gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.pro_dynexecute() AS
        a int := 1;
        b int := 2;
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO best_practices_for_procedure.tb1 VALUES(:1, :1),(:2, :2);' USING IN a, IN b;
    END;
    /
    CREATE PROCEDURE
    
    gaussdb=# CALL best_practices_for_procedure.pro_dynexecute();
     pro_dynexecute
    ----------------
    
    (1 row)
    
    -- Check the table and find that the same placeholders are bound to the same variables.
    gaussdb=# SELECT * FROM best_practices_for_procedure.tb1;
     a | b
    ---+---
     1 | 1
     2 | 2
    (2 rows)
    
    -- Set the GUC parameter.
    gaussdb=# SET behavior_compat_options='dynamic_sql_compat';
    SET
    gaussdb=# TRUNCATE TABLE best_practices_for_procedure.tb1;
    TRUNCATE TABLE
    
    -- Create a stored procedure that uses dynamic statements and bind the same placeholders to different variables.
    gaussdb=# CREATE OR REPLACE PROCEDURE best_practices_for_procedure.pro_dynexecute() AS
        a int := 1;
        b int := 2;
        c int := 3;
        d int := 4;
    BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO best_practices_for_procedure.tb1 VALUES(:1, :1),(:2, :2);' USING IN a, IN b, IN c, IN d;
    END;
    /
    CREATE PROCEDURE
    
    gaussdb=# CALL best_practices_for_procedure.pro_dynexecute();
     pro_dynexecute
    ----------------
    
    (1 row)
    
    -- After the GUC parameter is set and the function is called, the same placeholders can be bound to different variables.
    gaussdb=# SELECT * FROM best_practices_for_procedure.tb1;
     a | b
    ---+---
     1 | 2
     3 | 4
    (2 rows)
    
    gaussdb=# RESET behavior_compat_options;
    RESET
    
    gaussdb=# DROP SCHEMA best_practices_for_procedure CASCADE;
    NOTICE:  drop cascades to 2 other objects
    DETAIL:  drop cascades to table best_practices_for_procedure.tb1
    drop cascades to function best_practices_for_procedure.pro_dynexecute()
    DROP SCHEMA