示例 一次性入库使用示例。 -- 设置如下参数。
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作为函数参数类型使用示例。 -- CATA
LOG 相关，跨数据库访问。
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
