One-off Import
If the GUC parameter enable_force_obj is set to on, functions, stored procedures, and packages can be imported to the database at a time.
However, data cannot be imported to the database in the following scenarios:
- In the following scenarios, errors are reported for the syntax of out-of-package functions:
- A syntax error is reported when table%type, table.column.column%type, table.column.column.column%type, and schema.table.column.column%type are used as function parameter types.
- When view%rowtype and schema.view%rowtype are used as function parameter types, a syntax error is reported in forward creation. If the sequence is changed, they can be imported to the database. However, an error is reported during compilation and execution.
- A syntax error is reported when view%type, view.column.column%type, view.column.column.column%type, and schema.view.column.column%type are used as function parameter types.
- When type%type, type%rowtype, schema.type%rowtype, type.column.column%type, type.column.column.column%type, schema.type.column.column%type, and catalog.schema.type%rowtype ("catalog" indicates the database name. For details, see the examples.) are used as function parameter types, a syntax error is reported in forward creation. If the sequence is changed, they can be imported to the database. However, an error is reported during compilation and execution.
- A syntax error is reported when 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, and package.type.type.col%type are used as function parameter types.
- A syntax error is reported when catalog.schema.package.type and catalog.schema.package.type%type are used as function parameter types.
- In the following scenarios, out-of-package functions can be imported to the database, but semantic parsing errors are reported during execution:
- catalog.schema.type and catalog.package.type are used as function parameter types.
- package.type%rowtype is used as the function parameter type, importing to the database in both forward creation and creation after the sequence is changed and compilation are supported. However, an error is reported during execution.
- In the following scenarios, errors are reported for the syntax of functions in the package:
- A syntax error is reported when table%type, table.column.column%type, table.column.column.column%type, and schema.table.column.column%type are used as function parameter types.
- When view%rowtype and schema.view%rowtype are used as function parameter types, a syntax error is reported. If the sequence is changed, they can be imported to the database. However, an error is reported during compilation and execution.
- A syntax error is reported when view%type, view.column.column%type, view.column.column.column%type, and schema.view.column.column%type are used as function parameter types.
- Syntax errors are reported for type%rowtype and schema.type%rowtype. The syntax can be imported to the database after the sequence is changed. However, an error is reported during compilation and execution.
- A syntax error is reported when type%type, type.column.column%type, type.column.column.column%type, schema.type.column.column%type, and catalog.schema.type%rowtype are used as function parameter types.
- A syntax error is reported when schema.package.type%rowtype, schema.package.type%rowtype, package.var.var.var%type, package.type.col.col%type, catalog.schema.package.type, and catalog.schema.package.type%type are used as function parameter types.
- A syntax error is reported when type%rowtype and var%rowtype in the current package are used as function parameter types.
- The package header created using var%type in the current package can be imported to the database, but an error is reported during package body creation.
- In the following scenarios, functions in the package can be imported to the database, but semantic parsing errors are reported during execution:
- catalog.schema.type and catalog.package.type are used as function parameter types.
- package.type%rowtype is used as the function parameter type.
- schema.package.var%type and schema.package.type%type are used as parameter types.
- Other scenarios
- After the allow_procedure_compile_check option is enabled, tables that cannot be found by the cursor in the package and undefined variables in the package cannot be imported to the database.
- After the allow_procedure_compile_check option is enabled, undefined in-package functions that are accessed by the cursor in the package cannot be imported to the database.
- When a column is of an undefined type in the package, that is, when pkg.type.col %type is used as the record type, the column cannot be imported to the database.
Examples
Examples of one-off import.
-- Configure the following parameters: SET ddl_invalid_mode='invalid'; SET SET enable_force_create_obj=on; SET -- Create a function whose input parameter type does not exist. 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 -- Query status. 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) -- Create a package whose input parameter type does not exist. 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 -- Query status. 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 -- Re-create the input parameter type. 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)
Examples of errors reported in unsupported scenarios are as follows:
In the following example, view %rowtype and type %rowtype are used as function parameter types.
-- When view%rowtype and type%rowtype are used as function parameter types, an error is reported in forward creation. 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. -- After the sequence is changed, the functions can be imported to the database but cannot be executed. 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; -- An error is reported during execution and compilation. 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.
In the following example, catalog.schema.type%rowtype and catalog.package.type%rowtype are used as function parameter types.
-- Cross-database access related to 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"
In the following example, catalog.package.type is used as the function parameter type.
-- Cross-database access related to 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; / -- An error is reported during execution and the compilation is invalid. 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; / -- An error is reported during execution and the compilation is invalid. 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
In the following example, package.type%rowtype is used as the function parameter type.
-- package.type%rowtype is used as the function parameter type. 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; / -- An error is reported during execution and the compilation is invalid. 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 -- The sequence is changed. 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; / -- An error is reported during execution and the compilation is invalid. 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot