PostgreSQL Oracle 兼容性之 - create type

背景

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

digoal’s 大量PostgreSQL文章入口