Support For Constants In Package As Default Values

Availability

This feature is available since MogDB 5.0.0.

Introduction

This feature supports package constants as default values for function or procedure entry parameters.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Constraints

The current MogDB implementation can only read the PACKAGE package constants and assign them to the function in-parameters.In Oracle, if the FUNCTION in-parameter directly references the PACKAGE variable, and the PACKAGE variable type and the FUNCTION in-parameter type don't need to be type-converted, then modifying the value of the PACKAGE variable will affect the value of the FUNCTION in-parameter. The value of the PACKAGE variable will not affect the value of the FUNCTION in-parameter if the in-parameter references a PACKAGE variable expression or if type conversion is required.

According to Oracle's behavior, if the input parameter of FUNCTION/PROCEDURE is a direct reference to a PACKAGE variable, and the type does not need to be converted, then modifying the value of the PACKAGE variable will affect the value of the FUNCTION input parameter.

If the input parameter of FUNCTION/PROCEDURE is an expression of the PACKAGE variable, or the type needs to be converted, then the value of the PACKAGE variable will be affected. Changing the value of the PACKAGE variable will not affect the value of the FUNCTION entry.

Syntax Description

The package constants of PACKAGE are referenced after the default value of the function or PROCEDURE entry parameter.

FUNCTION test_func(input p1 DEFAULT pkg_name.aaa)

Add GUC parameter proc_inparam_immutable, default is true.

If the current library is in A library compatibility mode and proc_inparam_immutable is true, only IN type can use default value for MogDB input parameter, and the input parameter cannot be modified in FUNCTION/PROCEDURE, which is consistent with ORACLE. Modifying the value of the PACKAGE variable affects the behavior of function inputs consistent with Oracle.

According to the PG_CAST system table, make a list of the type conversions that are required.

With the following statement, you can query the list of source_type and target_type that require type conversion.

SELECT pt1.typname AS source_type, pt2.typname AS target_type FROM pg_cast pc, pg_type pt1, pg_type pt2 WHERE pc.castsource = pt1.oid AND pc.casttarget = pt2.oid ORDER BY source_type;

Example

CREATE TABLE tlog(
id number
llevel number
);
CREATE OR REPLACE package pkg_logparam IS
  default_level constant tlog.llevel%type :=70; --lerror
END pkg_logparam;
/
CREATE OR REPLACE package pkg_mplog AS
  FUNCTION getLevelInText(pLevel tlog.llevel%type DEFAULT pkg_logparam.default_level) RETURN varchar;
END pkg_mplog;
/
CREATE OR REPLACE package body pkg_mplog AS
  FUNCTION getLevelInText(pLevel tlog.llevel%type DEFAULT pkg_logparam.default_level) RETURN varchar
  IS
  BEGIN
    RETURN pLevel;
  END;
END pkg_mplog;
/
SELECT pkg_mplog.getLevelInText();
 getlevelintext
-----------------
 70
(1 row)

CREATE OR REPLACE package test_pkg_paraml IS
  v1 varchar2(4000) := 'old'; -- package public variable
  v3 constant varchar2(4000) := 'old'; -- Package public constant
  PROCEDURE proc(p1 varchar2 DEFAULT v1,
                 p3 varchar2 := v3
                 );
END;

CREATE OR REPLACE package body test pkg_paraml IS
  v5 varchar2(4000) := 'old';-- package private variable
  v7 constant varchar2(4000) := 'old';-- Package private constant
  PROCEDURE proc2(p1 varchar2 default v1,
                  p3 varchar2 default v3,
                  p5 varchar2 default v5,
                  p7 varchar2 default v7) IS
  BEGIN
    raise notice 'v1:%, p1:%', v1, p1;
    raise notice 'v3:%, p3:%', v3, p3;
    raise notice 'v5:%, p5:%', v5, p5;
    raise notice 'v7:%, p7:%', v7, p7;
    raise notice 'Modify v5 to new, v1 has been modified previously';
    v5 :='new';
    raise notice 'v1:%, p1:%', v1, p1;
    raise notice 'v5:%, p5:%', v5, p5;
  END;
  PROCEDURE proc(p1 varchar2 DEFAULT v1, p3 varchar2 DEFAULT v3) IS
  BEGIN
    raise notice '---proc begin';
    raise notice 'v1:%, p1:%', v1, p1;
    raise notice 'v3:%, p3:%', v3, p3;
    raise notice 'Modify v1 to new';
    v1 := 'new';
    raise notice 'v1:%, p1:%', v1, p1;
    raise notice '--- proc1 begin';
    raise notice '--- proc1 begin';
    raise notice '--- proc end';
  END;
END;

BEGIN
  raise notice '---------------------';
  test_pkg_paraml.proc;
  raise notice '---------------------';
  test_pkg_paraml.proc('0', '0');
END;
/
NOTICE:  ----------------------------
NOTICE:  --- proc begin
NOTICE:  v1:old, p1:old
NOTICE:  v3:old, p3:old
NOTICE:  Modify v1 to new
NOTICE:  vl:new, p1:old
NOTICE:  --- proc1 begin
NOTICE:  --- proc1 begin
NOTICE:  --- proc end
NOTICE:  ----------------------------
NOTICE:  --- proc begin
NOTICE:  vl:new, p1:0
NOTICE:  v3:old, p3:0
NOTICE:  Modify v1 to new
NOTICE:  vl:new, p1:0
NOTICE:  --- proc1 begin
NOTICE:  --- proc1 begin
NOTICE:  --- proc end