一次性入库

示例

一次性入库使用示例。

-- 设置如下参数。
SET ddl_invalid_mode='invalid';
SET
SET enable_force_create_obj=on;
SET

-- 新建函数，入参使用的类型不存在。
CREATE OR REPLACE FUNCTION func1(var type_not_exist) RETURN int
IS
BEGIN
RETURN NULL;
END;
/
WARNING:  Type type_not_exist does not exist.
WARNING:  Function created with compilation errors.
CREATE FUNCTION

-- 查询状态。
SELECT proname,valid FROM pg_object obj JOIN pg_proc proc ON obj.object_oid = proc.oid AND proname = 'func1' AND propackageid = 0;
 proname | valid 
---------+-------
 func1   | f
(1 row)

-- 新建PACKAGE，入参使用的类型不存在。
CREATE OR REPLACE PACKAGE pkg
IS
FUNCTION func1(var type_not_exist) RETURN int;
END pkg;
/
WARNING:  Type type_not_exist does not exist.
WARNING:  Package created with compilation errors.
CREATE PACKAGE

CREATE OR REPLACE PACKAGE BODY pkg
IS
FUNCTION func1(var type_not_exist) RETURN int AS
DECLARE
BEGIN
RETURN NULL;
END;
END pkg;
/
WARNING:  Type type_not_exist does not exist.
WARNING:  Package Body created with compilation errors.
CREATE PACKAGE BODY

-- 查询状态。
SELECT pkgname,object_type,valid FROM pg_object obj JOIN gs_package pkg ON obj.object_oid = pkg.oid AND pkgname = 'pkg';
 pkgname | object_type | valid 
---------+-------------+-------
 pkg     | B           | f
 pkg     | S           | f

-- 重新创建入参使用的类型。
CREATE TYPE type_not_exist AS (f1 int, f2 text);
CREATE TYPE

ALTER PACKAGE pkg COMPILE;
ALTER PACKAGE
SELECT pkgname,object_type,valid FROM pg_object obj JOIN gs_package pkg ON obj.object_oid = pkg.oid AND pkgname = 'pkg';
 pkgname | object_type | valid 
---------+-------------+-------
 pkg     | S           | t
 pkg     | B           | t
(2 rows)

不支持场景报错示例。

view%rowtype、type%rowtype作为函数参数类型使用示例。

-- view%rowtype、type%rowtype作为函数参数类型，正向创建报错。
DROP FUNCTION IF EXISTS func1;
DROP VIEW IF EXISTS teview;
DROP TABLE IF EXISTS tetab;
CREATE TYPE tp1 IS (a int,b int);
CREATE TABLE tetab(a int,b int);
CREATE OR REPLACE VIEW teview AS SELECT * FROM tetab;
gaussdb=# CREATE OR REPLACE FUNCTION func1(a teview%rowtype)
gaussdb-# RETURN INTEGER
gaussdb-# AS
gaussdb$# BEGIN
gaussdb$# RETURN 1;
gaussdb$# END;
gaussdb$# /
ERROR:  relation does not exist when parse word.
DETAIL:   relation "teview" referenced by %ROWTYPE does not exist.
gaussdb=# CREATE OR REPLACE FUNCTION func1(param1 tp1%rowtype) RETURN INTEGER
gaussdb-# AS
gaussdb$# BEGIN
gaussdb$# RETURN 1;
gaussdb$# END;
gaussdb$# /
ERROR:  relation does not exist when parse word.
DETAIL:   relation "tp1" referenced by %ROWTYPE does not exist.


-- 调换顺序后可以入库，无法执行。
DROP FUNCTION IF EXISTS func1;
DROP VIEW IF EXISTS teview;
DROP TABLE IF EXISTS tetab;
CREATE OR REPLACE FUNCTION func1(a teview%rowtype)
RETURN INTEGER
AS
BEGIN
RETURN 1;
END;
/
CREATE TABLE tetab(a int,b int);
CREATE OR REPLACE VIEW teview AS SELECT * FROM tetab;

-- 执行和编译报错。
gaussdb=# SELECT func1((1,2));
ERROR:  Function func1(record) does not exist.
LINE 1: SELECT func1((1,2));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: func1

gaussdb=# ALTER FUNCTION func1 COMPILE;
ERROR:  relation does not exist when parse word.
DETAIL:   relation "teview" referenced by %ROWTYPE does not exist.

CREATE OR REPLACE FUNCTION func1(param1 tp1%rowtype) RETURN INTEGER
AS
BEGIN
RETURN 1;
END;
/
CREATE TYPE tp1 IS (a int,b int);
gaussdb=# SELECT func1((1,2));
ERROR:  Function func1(record) does not exist.
LINE 1: SELECT func1((1,2));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: func1

gaussdb=# ALTER FUNCTION func1 COMPILE;
ERROR:  relation does not exist when parse word.
DETAIL:   relation "tp1" referenced by %ROWTYPE does not exist.

catalog.schema.type%rowtype、catalog.package.type%rowtype作为函数参数类型使用示例。

-- CATALOG相关，跨数据库访问。
DROP DATABASE IF EXISTS tedb1;
DROP DATABASE IF EXISTS tedb2;
CREATE DATABASE tedb1;
CREATE DATABASE tedb2;
\c tedb1
CREATE SCHEMA sch1;
CREATE TYPE sch1.tp1 IS (a int,b int);
CREATE OR REPLACE PACKAGE pkg1
AS
TYPE tp1 IS record(a int,b int);
END PKG1;
/

\c tedb2
tedb2=# CREATE OR REPLACE FUNCTION func1(param tedb1.sch1.tp1%rowtype) RETURN INTEGER
tedb2-# AS
tedb2$# BEGIN
tedb2$# RETURN 1;
tedb2$# END;
tedb2$# /
ERROR:  cross-database references are not implemented: "tedb1.sch1.tp1"

tedb2=# CREATE OR REPLACE FUNCTION func1(param tedb1.pkg1.tp1%rowtype) RETURN INTEGER
tedb2-# AS
tedb2$# BEGIN
tedb2$# RETURN 1;
tedb2$# END;
tedb2$# /
ERROR:  cross-database references are not implemented: "tedb1.pkg1.tp1"

catalog.package.type作为函数参数类型使用示例。

-- CATALOG相关，跨数据库访问。
DROP DATABASE IF EXISTS tedb1;
DROP DATABASE IF EXISTS tedb2;
CREATE DATABASE tedb1;
CREATE DATABASE tedb2;
\c tedb1
CREATE SCHEMA sch1;
CREATE TYPE sch1.tp1 IS (a int,b int);
CREATE OR REPLACE PACKAGE pkg1
AS
TYPE tp1 IS record(a int,b int);
END pkg1;
/

\c tedb2
CREATE OR REPLACE FUNCTION func1(param tedb1.sch1.tp1) RETURN INTEGER
AS
BEGIN
RETURN 1;
END;
/
-- 执行报错，编译无效。
tedb2=# SELECT func1((1,2));
WARNING:  Type tp1 does not exist.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
WARNING:  Function func1 recompile with compilation errors.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
ERROR:  Function func1(record) does not exist.
LINE 1: SELECT func1((1,2));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: func1

tedb2=# alter function func1 compile
WARNING:  Type tp1 does not exist.
WARNING:  Function func1 recompile with compilation errors.
ALTER FUNCTION

CREATE OR REPLACE FUNCTION func1(param tedb1.pkg1.tp1) RETURN INTEGER
AS
BEGIN
RETURN 1;
END;
/
-- 执行报错，编译无效。
tedb2=# SELECT func1((1,2));
WARNING:  Type tp1 does not exist.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
WARNING:  Function func1 recompile with compilation errors.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
ERROR:  Function func1(record) does not exist.
LINE 1: SELECT func1((1,2));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: func1

tedb2=# alter function func1 compile;
WARNING:  Type tp1 does not exist.
WARNING:  Function func1 recompile with compilation errors.
ALTER FUNCTION

package.type%rowtype作为函数参数类型使用示例。

-- package.type%rowtype作为函数参数类型。
DROP PACKAGE IF EXISTS pkg1;
DROP FUNCTION IF EXISTS func1;
CREATE OR REPLACE PACKAGE pkg1 IS 
    TYPE rec IS record(c1 int, c2 varchar);
    var1 rec;
END pkg1;
/
CREATE OR REPLACE FUNCTION func1(p pkg1.rec%rowtype) RETURN INTEGER IS
BEGIN
    RETURN 1;
END;
/
-- 执行报错，编译无效。
gaussdb=# SELECT func1((1,'var1'));
WARNING:  relation does not exist when parse word.
DETAIL:   relation "pkg1.rec" referenced by %ROWTYPE does not exist.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
WARNING:  Function func1 recompile with compilation errors.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
ERROR:  Function func1(record) does not exist.
LINE 1: SELECT func1((1,'var1'));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: func1

gaussdb=# alter function func1 compile;
WARNING:  relation does not exist when parse word.
DETAIL:   relation "pkg1.rec" referenced by %ROWTYPE does not exist.
WARNING:  Function func1 recompile with compilation errors.
ALTER FUNCTION

-- 变更顺序。
DROP PACKAGE IF EXISTS pkg1;
DROP FUNCTION IF EXISTS func1;
CREATE OR REPLACE FUNCTION func1(p pkg1.rec%rowtype) RETURN INTEGER IS
BEGIN
    RETURN 1;
END;
/
CREATE OR REPLACE PACKAGE pkg1 IS 
    TYPE rec IS record(c1 int, c2 varchar);
    var1 rec;
END PKG1;
/
-- 执行报错，编译无效。
gaussdb=# SELECT func1((1,'var1'));
WARNING:  relation does not exist when parse word.
DETAIL:   relation "pkg1.rec" referenced by %ROWTYPE does not exist.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
WARNING:  Function func1 recompile with compilation errors.
CONTEXT:  SQL statement "alter function public.func1 compile"
PL/pgSQL function inline_code_block line 11 at SQL statement
ERROR:  Function func1(record) does not exist.
LINE 1: SELECT func1((1,'var1'));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  referenced column: func1

gaussdb=# alter function func1 compile;
WARNING:  relation does not exist when parse word.
DETAIL:   relation "pkg1.rec" referenced by %ROWTYPE does not exist.
WARNING:  Function func1 recompile with compilation errors.
ALTER FUNCTION

包嵌套依赖其他包，执行实例化报错的场景。

• 包2依赖包1，在创建包2的时候执行包1的实例化，执行实例化报错。
  示例：

  CREATE TABLE t1(a INT);
  CREATE TABLE
  
  CREATE OR REPLACE PACKAGE package2 IS
      var1 t1%ROWTYPE;
      PROCEDURE procedure3();
  END package2;
  /
  CREATE PACKAGE
  
  CREATE OR REPLACE PACKAGE package1 IS
      PROCEDURE procedure1();
      PROCEDURE procedure2();
  END package1;
  /
  CREATE PACKAGE
  
  CREATE OR REPLACE PACKAGE BODY package2 IS
      PROCEDURE procedure3() IS
      DECLARE
      BEGIN
          package1.procedure2();
      END;
  BEGIN
      SELECT * INTO var1 FROM t1;
      package1.procedure1();
  END package2;
  /
  CREATE PACKAGE BODY
  
  CREATE OR REPLACE PACKAGE BODY package1 IS
      PROCEDURE procedure1() IS
      DECLARE
      BEGIN
          package2.procedure3();
      END;
      PROCEDURE procedure2() IS
      DECLARE
      BEGIN
          null;
      END;
  BEGIN
      package2.procedure3();
  END package1;
  /
  ERROR:  Query does not return rows when INTO is processed.
  CONTEXT:  PL/pgSQL function inline_code_block line 2 at SQL statement
  compilation of PL/pgSQL package "package1" near line 2

• 包2依赖包1，包1依赖包2，在创建包2的时候执行包1的实例化触发包2的实例化，执行实例化成环报错。
  示例：

  CREATE OR REPLACE PACKAGE packag1 IS
  	PROCEDURE procedure1();
  	PROCEDURE procedure2();
  END packag1;
  /
  CREATE PACKAGE
  
  CREATE OR REPLACE PACKAGE package2 IS
  	PROCEDURE procedure3();
  END;
  /
  CREATE PACKAGE
  
  CREATE OR REPLACE PACKAGE BODY package2 IS
  	PROCEDURE procedure3() IS
  BEGIN
  	packag1.procedure2();
  END;
  BEGIN
      packag1.procedure1();
  END;
  /
  CREATE PACKAGE BODY
  
  CREATE OR REPLACE PACKAGE BODY packag1 IS
  PROCEDURE procedure1() IS
  BEGIN
  	package2.func3();
  END;
  PROCEDURE procedure2() IS
  BEGIN
  	NULL;
  END;
  BEGIN
  	package2.func3();
  END;
  /
  ERROR:  Warning when compiling package, pkg is compiled into a loop.
  DETAIL:  When compile package, there is a dependence itself.
  CONTEXT:  SQL statement "CALL packag1.procedure1()"
  PL/pgSQL function inline_code_block line 2 at PERFORM
  compilation of PL/pgSQL package "packag1" near line 2

部分场景一次性入库支持，但是无法执行。

• 包内函数支持入库，执行阶段报错的场景：

  synonym.col%type，其中同义词指向table。

  示例：

  -- 设置如下参数。
  SET ddl_invalid_mode='invalid';
  SET
  SET enable_force_create_obj=on;
  SET
  
  CREATE OR REPLACE PACKAGE pkg1 IS
  DECLARE
      FUNCTION proc1(var1 type1_syn.id%TYPE) RETURN INT;
  END pkg1;
  /
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  CONTEXT:  compilation of PL/pgSQL package "pkg1" near line 3
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  WARNING:  Package created with compilation errors.
  CREATE PACKAGE
  
  CREATE OR REPLACE PACKAGE body pkg1 IS
  DECLARE
  FUNCTION proc1(var1 type1_syn.id%TYPE) RETURN INT as
  	BEGIN
  	var1 := 'aaa';
  		RETURN 1;
      END;
  END pkg1;
  /
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  CONTEXT:  compilation of PL/pgSQL package "pkg1" near line 3
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  CONTEXT:  compilation of PL/pgSQL package "pkg1" near line 7
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  WARNING:  dependent type id is not exist.
  DETAIL:  N/A
  WARNING:  Package Body created with compilation errors.
  CREATE PACKAGE BODY
  
  CREATE TYPE type1 as (id varchar(20));
  CREATE TYPE
  
  CREATE SYNONYM type1_syn FOR TYPE1;
  CREATE SYNONYM
  
  ALTER PACKAGE pkg1 COMPILE;
  NOTICE:  type reference type1_syn.id%TYPE converted to character varying
  NOTICE:  type reference type1_syn.id%TYPE converted to character varying
  NOTICE:  type reference type1_syn.id%TYPE converted to character varying
  NOTICE:  type reference type1_syn.id%TYPE converted to character varying
  ALTER PACKAGE
  
  SELECT pkg1.proc1('bbb');
  ERROR:  relation "type1_syn" does not exist
  DETAIL:  Maybe you want to use synonym to reference a type object, but it is not yet supported.
  CONTEXT:  compilation of PL/pgSQL package "pkg1" near line 3
  referenced column: proc1