标簽
PostgreSQL , Oracle , create type , method , constructor , table type , composite type , domain , enum , range , operator , index , udf
https://github.com/digoal/blog/blob/master/201804/20180410_05.md#%E8%83%8C%E6%99%AF 背景
Oracle 自定義類型與PostgreSQL自定義類型的相容性。
雖然兩者用法上有一定的不同，但是PostgreSQL功能上完全覆寫到了Oracle的自定義類型。
下面從幾個方面描述PostgreSQL Oracle create type的相容性。
1、建立自定義類型
2、建立自定義類型的構造器
3、建立自定義類型的方法
4、建立基本類型的表類型
https://github.com/digoal/blog/blob/master/201804/20180410_05.md#oracle-create-type Oracle create type
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8001.htm#i2126568執行個體：
CREATE TYPE demo_typ1 AS OBJECT (a1 NUMBER, a2 NUMBER);
預設的構造器是與自定義類型同名的構造器。
CREATE TABLE demo_tab1 (b1 NUMBER, b2 demo_typ1);
INSERT INTO demo_tab1 VALUES (1, demo_typ1(2,3)); -- demo_typ1就是類型名
通過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
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
https://github.com/digoal/blog/blob/master/201804/20180410_05.md#postgresql-create-type PostgreSQL create type
PostgreSQL支援的類型更加豐富，一共分為6種。
https://www.postgresql.org/docs/devel/static/sql-createtype.html https://www.postgresql.org/docs/devel/static/sql-altertype.html1、複合類型
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.html6、表類型
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
https://github.com/digoal/blog/blob/master/201804/20180410_05.md#oracle-postgresql-create-type-%E8%BD%AC%E6%8D%A2 Oracle PostgreSQL create type 轉換
了解兩種資料庫對類型的使用方法差異後，就很容易進行轉換。
1、建立自定義類型，Oracle通過定義方法來實作對建立類型的支援，而PG則是通過定義函數來對建立類型進行邏輯計算的支援。調用方法上存在差異。
oracle:
new_type_col.method()
pg:
udf(new_type_col)
oracle，預設構造器與類型同名。
PG，不需要構造器，可以直接使用
::
轉換。
3、建立基本類型的表類型
PostgreSQL不需要構造表類型，可以在函數中returns setof new_type傳回表。或者使用數組。
PostgreSQL在以上類型功能基礎之上，增加了基本類型 枚舉、範圍、域 的支援。同時支援底層類型的擴充。
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)