Access local variables using function namespaces - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports direct access to local variables declared in functions using function namespaces. You can explicitly add the current function name before local variables to clearly distinguish and access them.

Prerequisites

This feature is supported only on clusters of PolarDB for PostgreSQL (Compatible with Oracle) 2.0 with revision version 2.0.14.17.33.0 or later.

Note

You can check the minor engine version in the console, or you can use the SHOW polardb_version; statement to check. If the minor engine version requirement is not met, you can upgrade the minor engine version.

Considerations

Access local variables within the current function: Variables accessed using <function_name>.<variable_name> must be local variables declared in the function. You cannot access local variables from other functions.
Avoid variable conflicts: When a local variable name conflicts with an external variable (such as a global variable or package variable), you can explicitly distinguish them using namespaces.
Use concise code: Use local variable names directly to reduce code complexity so long as no conflict is caused.

Examples

The following example demonstrates how to access local variables using function namespaces and distinguish package-level variables with the same name:

CREATE OR REPLACE PACKAGE p AS
    m INTEGER := 200; -- Package-level global variable
END p;

DECLARE
    PROCEDURE p(v IN VARCHAR2) IS
        m INTEGER := 100; -- Local variable in the subprocedure
    BEGIN
        -- Access local variable using subprocedure namespace
        dbms_output.put_line(p.m); -- Outputs 100
    END;
BEGIN
    -- Call the subprocedure
    p('abcd');
END;

In the package p, a global variable m is defined with a value of 200.
A local procedure p is defined in the anonymous block, which declares a local variable m with a value of 100.
In the procedure p, the local variable m can be accessed using p.m.

In this case, p.m explicitly represents the local variable m, not the global variable p.m in the package. Sample results:

The variable accessed using the function namespace is a local variable, not a global variable in the package. This mechanism provides an elegant solution for namespace management and variable conflict issues, improves code readability, and enables flexible control over the access scope of variables.