一次性入库

示例

一次性入库使用示例。

-- 设置如下参数。
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