一次性入库
设置GUC参数enable_force_create_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作为函数参数类型语法报错。
- 使用未定义的函数或变量作为函数参数默认值不支持入库。
- package.var%rowtype作为函数参数类型不支持入库。
- 包外函数支持入库,执行时语义解析报错的场景:
- 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语法报错,调换顺序可以入库,编译、执行报错。
- package.var%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类型的列类型不支持入库。
- 通过CREATE OR REPLACE的方式,构造PACKAGE依赖成环触发报错,不支持入库,触发场景请参见CREATE OR REPLACE构造依赖成环示例。
- 开启级联失效参数后,实例化块中调用已失效的函数会触发报错,需要通过ALTER COMPILE的方式使函数变为有效后再执行。
一次性入库并不能保证将编译报错信息全部展示出来,报错信息不全不影响对象的入库。当用户手动创建缺失的依赖对象并重新编译当前对象时,剩余报错信息会被重新打印。
示例
一次性入库使用示例。
-- 设置如下参数。 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)
不支持场景报错示例。
-- 打开失效重编译和一次性入库参数。
gaussdb=# SET DDL_INVALID_MODE='INVALID';
SET
gaussdb=# SET ENABLE_FORCE_CREATE_OBJ='ON';
SET
-- 正向创建package,依赖关系不成环。
gaussdb=# DROP PACKAGE IF EXISTS pkg1;
NOTICE: package pkg1() does not exist, skipping
DROP PACKAGE
gaussdb=# DROP PACKAGE IF EXISTS pkg2;
NOTICE: package pkg2() does not exist, skipping
DROP PACKAGE
gaussdb=# DROP PACKAGE IF EXISTS pkg3;
NOTICE: package pkg3() does not exist, skipping
DROP PACKAGE
gaussdb=# CREATE OR REPLACE PACKAGE pkg1 IS
gaussdb$# p1_v1 number(3);
gaussdb$# p1_v2 int;
gaussdb$# END pkg1;
gaussdb$# /
CREATE PACKAGE
gaussdb=# CREATE OR REPLACE PACKAGE pkg2 IS
gaussdb$# p1_v1 number(3);
gaussdb$# p1_v2 pkg1.p1_v2%type;
gaussdb$# END pkg2;
gaussdb$# /
CREATE PACKAGE
gaussdb=# CREATE OR REPLACE PACKAGE pkg3 IS
gaussdb$# p1_v1 pkg2.p1_v1%type;
gaussdb$# p1_v2 varchar(3);
gaussdb$# END pkg3;
gaussdb$# /
CREATE PACKAGE
-- 通过CREATE OR REPLACE重建pkg1,使依赖关系成环,会触发报错。
gaussdb=# CREATE OR REPLACE PACKAGE pkg1 IS
gaussdb$# p1_v1 number(3);
gaussdb$# p1_v2 pkg3.p1_v2%type;
gaussdb$# END pkg1;
gaussdb$# /
WARNING: Warning when compiling package, pkg is compiled into a loop.
DETAIL: When compile package, there is a dependence itself.
CONTEXT: compilation of PL/pgSQL package "pkg2" near line 2
compilation of PL/pgSQL package "pkg3" near line 1
compilation of PL/pgSQL package "pkg1" near line 2
ERROR: invalid type name "pkg1.p1_v2%type"
LINE 2: p1_v2 pkg1.p1_v2%type;
^
QUERY: PACKAGE DECLARE p1_v1 number(3);
p1_v2 pkg1.p1_v2%type;
END
CONTEXT: compilation of PL/pgSQL package "pkg2" near line 2
compilation of PL/pgSQL package "pkg3" near line 1
compilation of PL/pgSQL package "pkg1" near line 2 view%rowtype、type%rowtype作为函数参数类型使用示例。
-- view%rowtype、type%rowtype作为函数参数类型,正向创建报错。
DROP FUNCTION IF EXISTS func1;
DROP VIEW IF EXISTS teview;
DROP TABLE IF EXISTS tetab;
DROP TYPE IF EXISTS tp1;
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: The relationship does not exist when the statement is parsed.
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: The relationship does not exist when the statement is parsed.
DETAIL: Relation "tp1" referenced by %ROWTYPE does not exist.
-- 调换顺序后可以入库,无法执行。
DROP FUNCTION IF EXISTS func1;
DROP VIEW IF EXISTS teview;
DROP TABLE IF EXISTS tetab;
DROP TYPE IF EXISTS tp1;
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: The relationship does not exist when the statement is parsed.
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: The relationship does not exist when the statement is parsed.
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: The relationship does not exist when the statement is parsed.
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 12 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 12 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: The relationship does not exist when the statement is parsed.
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: The relationship does not exist when the statement is parsed.
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 12 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 12 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: The relationship does not exist when the statement is parsed.
DETAIL: Relation "pkg1.rec" referenced by %ROWTYPE does not exist.
WARNING: Function func1 recompile with compilation errors.
ALTER FUNCTION 报错信息显示不全示例。
gaussdb=# CREATE FUNCTION test_proc1(var1int t1.id%type, var2int pkg_v.rec1) gaussdb-# RETURN int gaussdb-# AS gaussdb$# BEGIN gaussdb$# RETURN 1; gaussdb$# END; gaussdb$# / WARNING: dependent type id is not exist. DETAIL: N/A WARNING: dependent type id is not exist. DETAIL: N/A WARNING: Function created with compilation errors. CREATE FUNCTION -- 建表后,重新编译函数会提示pkg.type不存在。 gaussdb=# CREATE TABLE t1(id int, name varchar2); CREATE TABLE gaussdb=# ALTER FUNCTION test_proc1 COMPILE; NOTICE: type reference t1.id%TYPE converted to integer WARNING: Type rec1 does not exist. WARNING: Function test_proc1 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