本文共 5255 字，大约阅读时间需要 17 分钟。
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、创建基本类型的表类型
实例：
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支持的类型更加丰富，一共分为6种。
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)
底层类型的扩展，详见
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 了解两种数据库对类型的使用方法差异后，就很容易进行转换。
1、创建自定义类型，Oracle通过定义方法来实现对新建类型的支持，而PG则是通过定义函数来对新建类型进行逻辑计算的支持。调用方法上存在差异。
oracle:
new_type_col.method()
pg:
udf(new_type_col)
2、创建自定义类型的构造器
oracle，默认构造器与类型同名。
PG，不需要构造器，可以直接使用::转换。
3、创建基本类型的表类型
PostgreSQL不需要构造表类型，可以在函数中returns setof new_type返回表。或者使用数组。
PostgreSQL在以上类型功能基础之上，增加了基本类型 枚举、范围、域 的支持。同时支持底层类型的扩展。
转载地址：http://jskra.baihongyu.com/