Updated on 2025-05-29 GMT+08:00

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