# 一次性入库
设置GUC参数enable_force_obj值为on后，支持函数、存储过程和包的一次性入库。
开启参数后，以下场景不支持入库。
- 包外函数语法不支持的场景：
  - table%type、table.column.column%type、table.column.column.column%type、schema.table.column.column%type作为函数参数类型语法报错。
  
  - view%rowtype、schema.view%rowtype作为函数参数类型，正向创建语法报错，调换顺序可以入库，编译、执行报错。
  
  - view%type、view.column.column%type、view.column.column.column%type、schema.view.column.column%type作为函数参数类型语法报错。
  
  - type%type、type%rowtype、schema.type%rowtype、type.column.column%type、type.column.column.column%type、schema.type.column.column%type、catalog.schema.type%rowtype作为函数参数类型，正向创建语法报错，调换顺序可以入库，编译、执行报错，catalog指的是数据库名，详细使用可参考示例。
  
  - schema.package.type%rowtype、schema.package.var%type、schema.package.type%type、package.var.col%type、schema.package.var.col%type、package.var.var.var%type、package.type.type.col%type作为函数参数类型语法报错。
  
  - catalog.schema.package.type、catalog.schema.package.type%type作为函数参数类型语法报错。
   
- 包外函数支持入库，执行时语义解析报错的场景：
  - catalog.schema.type、catalog.package.type作为函数参数类型。
  
  
  
  - package.type%rowtype作为函数参数类型，正向、调换创建顺序可以入库，可以编译，执行报错。
   
- 包内函数语法不支持的场景
  - table%type、table.column.column%type、table.column.column.column%type、schema.table.column.column%type作为函数参数类型语法报错。
  
  - view%rowtype、schema.view%rowtype作为函数参数类型语法报错，调换顺序可以入库，编译、执行报错。
  
  - view%type、view.column.column%type、view.column.column.column%type、schema.view.column.column%type作为函数参数类型语法报错。
  
  - type%rowtype、schema.type%rowtype语法报错，调换顺序可以入库，编译、执行报错。
  
  - type%type、type.column.column%type、type.column.column.column%type、schema.type.column.column%type、catalog.schema.type%rowtype作为函数参数类型语法报错。
  
  - schema.package.type%rowtype、schema.package.type%rowtype、package.var.var.var%type、package.type.col.col%type、catalog.schema.package.type、catalog.schema.package.type%type作为函数参数类型语法报错。
  
  - 使用当前package内的type%rowtype、var%rowtype作为函数参数类型语法报错。
  
  - 使用当前package内var%type创建包头可以入库，创建包体报错。
   
- 包内函数支持入库，执行时语义解析报错的场景：
  - 使用catalog.schema.type、catalog.package.type作为函数参数类型。
  
  - 使用package.type%rowtype作为函数参数类型。
  
  - schema.package.var%type、schema.package.type%type作为参数类型。
   
- 包嵌套依赖其他包，执行实例化报错的场景：
  - 包2依赖包1，在创建包2的时候执行包1的实例化，执行实例化报错。
  
  - 包2依赖包1，包1依赖包2，在创建包2的时候执行包1的实例化触发包2的实例化，执行实例化成环报错。
   
- 其他场景
  - 开启allow_procedure_compile_check选项后，包内游标查询不存在的表、未定义的PACKAGE内变量均不支持入库。
  
  - 开启allow_procedure_compile_check选项后，包内游标访问未定义的包内的函数不支持入库。
  
  - 使用未定义的包内类型的列类型，即pkg.type.col%type作为record类型的列类型不支持入库。
   
#### 示例
一次性入库使用示例。
```
-- 设置如下参数。
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
  ```