标签
PostgreSQL , Oracle , create type , method , constructor , table type , composite type , domain , enum , range , operator , index , udf
背景
Oracle 自定义类型与PostgreSQL自定义类型的兼容性。
虽然两者用法上有一定的不同，但是PostgreSQL功能上完全覆盖到了Oracle的自定义类型。
下面从几个方面描述PostgreSQL Oracle create type的兼容性。
1、创建自定义类型
2、创建自定义类型的构造器
3、创建自定义类型的方法
4、创建基本类型的表类型
Oracle create type
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8001.htm#i2126568
实例：
1、创建自定义类型
CREATE TYPE demo_typ1 AS OBJECT (a1 NUMBER, a2 NUMBER);
2、创建自定义类型的构造器
默认的构造器是与自定义类型同名的构造器。
CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1);
INSERT INTO demo_tab1 VALUES (1, demo_typ1(2,3)); -- demo_typ1就是类型名
3、创建自定义类型的方法
通过member function指定方法。
CREATE TYPE demo_typ2 AS OBJECT (a1 NUMBER,
MEMBER FUNCTION get_square RETURN NUMBER);
通过创建类型体，定义方法的实体。
CREATE TYPE BODY demo_typ2 IS
MEMBER FUNCTION get_square
RETURN NUMBER
IS x NUMBER;
BEGIN
SELECT c.col.a1*c.col.a1 INTO x
FROM demo_tab2 c;
RETURN (x);
END;
END;
/
创建测试表，使用自定义类型
CREATE TABLE demo_tab2(col demo_typ2);
使用默认构造器构造自定义类型的值
INSERT INTO demo_tab2 VALUES (demo_typ2(2));
使用col.method_name调用自定义类型的方法
SELECT t.col.get_square() FROM demo_tab2 t;
T.COL.GET_SQUARE()
------------------
4
4、创建基本类型的表类型
CREATE TYPE textdoc_typ AS OBJECT
( document_typ VARCHAR2(32)
, formatted_doc BLOB
) ;
CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
表类型通常用于在PL/SQL函数中，返回多条记录。
create or replace TYPE "STRINGS_TABLE" is table of varchar2(2000)
create or replace FUNCTION highsoft_split(
p_str IN long, --VARCHAR2,
p_delimiter IN VARCHAR2)
RETURN strings_table IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str long;--VARCHAR2(2000);
str_split strings_table := strings_table();
BEGIN
len := LENGTH(p_str);
len1 := LENGTH(p_delimiter);
WHILE j < len LOOP
j := INSTR(p_str, p_delimiter, i);
IF j = 0 THEN
j := len;
str := SUBSTR(p_str, i);
str_split.EXTEND;
str_split(str_split.COUNT) := str;
IF i >= len THEN
EXIT;
END IF;
ELSE
str := SUBSTR(p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split(str_split.COUNT) := str;
END IF;
END LOOP;
RETURN str_split;
END highsoft_split;
效果如下
SQL> select * from table(highsoft_split('abc,d,e,f,g',','));
COLUMN_VALUE
--------------------------------------------------------------------------------
abc
d
e
f
g
PostgreSQL create type
PostgreSQL支持的类型更加丰富，一共分为6种。
https://www.postgresql.org/docs/devel/static/sql-createtype.html
https://www.postgresql.org/docs/devel/static/sql-altertype.html
1、复合类型
postgres=# create type new_box as (upper point, lower point);
CREATE TYPE
postgres=# create type tt as (c1 int, c2 int, c3 timestamp);
CREATE TYPE
不需要构造器，直接输入并制定类型即可：
postgres=# select ('(1,2)', '(3,9)')::new_box;
row
-------------------
("(1,2)","(3,9)")
(1 row)
postgres=# select (1,2,'2017-01-01 10:10:10')::tt;
row
-----------------------------
(1,2,"2017-01-01 10:10:10")
(1 row)
postgres=# select tt $$(1,2,'2017-01-01 10:10:10')$$;
tt
-----------------------------
(1,2,"2017-01-01 10:10:10")
(1 row)
2、域类型
属于一种限定值范围的类型，比如EMAIL，邮编等，有一定的规律，可以定义域类型，直接限定其范围。
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE us_snail_addy (
address_id SERIAL PRIMARY KEY,
street1 TEXT NOT NULL,
street2 TEXT,
street3 TEXT,
city TEXT NOT NULL,
postal us_postal_code NOT NULL
);
使用起来与域的底层类型一样，所有与底层类型一致的操作符、函数都通用。比例这里的us_postal_code与text完全通用。
3、范围类型
范围类型顾名思义存的是一个范围。
CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
create table tbl1 (id int, rg float8_range);
postgres=# select float8_range(1.1,2);
float8_range
--------------
[1.1,2)
(1 row)
4、枚举类型
一些常量值的集合，通常用于描述一些有限取值空间的内容。
CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
CREATE TABLE bug (
id serial,
description text,
status bug_status
);
ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
5、底层类型
通过C函数定义的类型，支持更加丰富的操作，包括支持索引接口，OP接口，UDF接口等。
5.1、操作符
5.2、AM
5.3、UDF
5.4、构造器（Input function， Output function）
5.5、方法(UDF)
底层类型的扩展，详见
https://www.postgresql.org/docs/devel/static/xtypes.html
https://www.postgresql.org/docs/devel/static/extend.html
6、表类型
PostgreSQL不需要表类型，因为在函数中返回setof type即返回多条记录（类似表的效果）。
当然如果你要在函数内存储多值，有两种方法：
1、临时表
2、数组
例子
do language plpgsql $$
declare
v_tbl int[];
begin
for i in 1..100 loop
v_tbl[i] := i*2;
end loop;
raise notice '%', v_tbl;
end;
$$;
NOTICE: {2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50,52,54,56,58,60,62,64,66,68,70,72,74,76,78,80,82,84,86,88,90,92,94,96,98,100,102,104,106,108,110,112,114,116,118,120,122,124,126,128,130,132,134,136,138,140,142,144,146,148,150,152,154,156,158,160,162,164,166,168,170,172,174,176,178,180,182,184,186,188,190,192,194,196,198,200}
DO
Oracle PostgreSQL create type 转换
了解两种数据库对类型的使用方法差异后，就很容易进行转换。
1、创建自定义类型，Oracle通过定义方法来实现对新建类型的支持，而PG则是通过定义函数来对新建类型进行逻辑计算的支持。调用方法上存在差异。
oracle:
new_type_col.method()
pg:
udf(new_type_col)
2、创建自定义类型的构造器
oracle，默认构造器与类型同名。
PG，不需要构造器，可以直接使用::转换。
3、创建基本类型的表类型
PostgreSQL不需要构造表类型，可以在函数中returns setof new_type返回表。或者使用数组。
PostgreSQL在以上类型功能基础之上，增加了基本类型 枚举、范围、域 的支持。同时支持底层类型的扩展。
参考
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8001.htm#i2126568
https://www.postgresql.org/docs/devel/static/sql-createtype.html
https://www.postgresql.org/docs/devel/static/sql-createdomain.html