8
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 12

PostgreSQL Advent Calendar 2020

@plusultra(Noriyoshi Shinoda)

orafce拡張モジュールの紹介

Last updated at Posted at 2020-12-11

本エントリーは PostgreSQL Advent Calendar 2020 の 12 日目です。

orafce 拡張モジュールとは

　orafce は PostgreSQL 上に Oracle Database と互換性がある関数やデータ型を提供するオープンソース・ソフトウェアです。orafce は PostgreSQL 標準の拡張モジュール（Extension）として提供されているため、PostgreSQL を直接変更せずに利用できます。AWS のマネージドサービスである RDS 上の PostgreSQL やAurora for PostgreSQL でも使うことができます（マニュアル）。

利用方法

インストール方法と利用開始までを説明します。

インストール

　ソースコードからビルドする場合は GitHub からダウンロードして、PostgreSQL ソースの contrib ディレクトリに展開してからビルドします。RPM パッケージは pkgs.org サイト等で見つけることができるでしょう。

以下の例は PostgreSQL 13.1 のソースに orafce を加えてビルドする手順です。

orafce のビルドとインストール
$ cd postgresql-13.1/contrib
$ unzip ~/orafce.zip
$ cd orafce
$ export NO_PGXS=1
$ make 
# make install
# cat /usr/local/pgsql/share/extension/orafce.control

# orafce extension
comment = 'Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS'
default_version = '3.14'
module_pathname = '$libdir/orafce'
relocatable = false

インストールが完了すると、pg_available_extensions ビューで拡張モジュールとして参照できるようになります。

拡張モジュールの確認
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'orafce';
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------
name              | orafce
default_version   | 3.14
installed_version | 3.14
comment           | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS

postgres=#

利用する前に

　orafce を利用するためには他の Contrib モジュールと同じように CREATE EXTENSION 文を実行します。orafce を利用するデータベースに SUPERUSER 属性を持つユーザーで接続して実行する必要があります。

利用開始
postgres=# CREATE EXTENSION orafce;
CREATE EXTENSION

CREATE EXTENSION 文を実行すると、多数のスキーマが作成されます。このうちoracle スキーマには Oracle Database 互換の関数が定義されているため、pg_catalog よりも先に参照するようにあらかじめ search_path 変数を変更しておきます。

スキーマの確認
postgres=> \dn
     List of schemas
     Name     |  Owner
--------------+----------
 dbms_alert   | postgres
 dbms_assert  | postgres
 dbms_output  | postgres
 dbms_pipe    | postgres
 dbms_random  | postgres
 dbms_utility | postgres
 oracle       | postgres
 plunit       | postgres
 plvchr       | postgres
 plvdate      | postgres
 plvlex       | postgres
 plvstr       | postgres
 plvsubst     | postgres
 public       | postgres
 utl_file     | postgres
(15 rows)

postgres=> SHOW search_path;
             search_path
-------------------------------------
 "$user", public, oracle, pg_catalog
(1 row)

互換機能

　主な互換機能について簡単に説明します。詳しくはマニュアルを参照してください。

DUAL テーブル

Oracle Database ユーザーにはおなじみの DUAL テーブルが提供されています。FROM 句を省略できない Oracle Database の仕様に対応するために作られたダミー用のテーブルです。orafce では VIEW として定義されています。

DUAL テーブル
postgres=> SELECT * FROM dual;
 dummy
-------
 X
(1 row)

postgres=> \d+ dual
                                 View "public.dual"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 dummy  | character varying |           |          |         | extended |
View definition:
 SELECT 'X'::character varying AS dummy;

データ型

以下の Oracle Database 独自データ型が提供されています。

  • VARCHAR2
  • NVARCHAR2
  • DATE

Oracle Database では NVARCHAR2 型は異なるエンコードを指定できますが、orafce では VARCHAR と変わりません。データ長の指定は PostgreSQL の長さ制限に従います（Oracle Database よりも長い）。DATE 型は PostgreSQL にもありますが、orafce の DATE 型は Oracle Database と同じように時刻まで含みます。これらのデータ型は単なるエイリアスではなく、独自データ型として定義されています。

互換データ型の利用
postgres=> CREATE TABLE emp(emp_id NUMERIC, first_name VARCHAR2(30), last_name NVARCHAR2(30), birth_date DATE);
CREATE TABLE
postgres=> \d emp
                     Table "public.emp"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 emp_id     | numeric       |           |          |
 first_name | varchar2(30)  |           |          |
 last_name  | nvarchar2(30) |           |          |
 birth_date | date          |           |          |

postgres=> SELECT sysdate()::oracle.date, sysdate()::pg_catalog.date FROM dual;
       sysdate       |  sysdate
---------------------+------------
 2020-12-11 13:27:22 | 2020-12-11
(1 row)

関数

Oracle Database 独自の関数が多数提供されています。Oracle Database では関数名の後の()を省略できる場合がありますが、orafce では省略できません。

互換関数の利用
postgres=> SELECT sysdate() FROM dual;
       sysdate
---------------------
 2020-12-10 02:13:46
(1 row)

以下の関数が提供されています。

  • LENGTH / LENGTHB
  • SUBSTR / SUBSTRB / INSTR
  • NLSSORT
  • LTRIM / RTRIM / BTRIM / LPAD / RPAD
  • TO_DATE / TO_CHAR(date) / TO_MULTI_BYTE / TO_SINGLE_BYTE / TO_NUMBER
  • SYSDATE
  • DBTIMEZONE / SESSIONTIMEZONE
  • BITAND / COSH / TANH / SINH
  • ADD_MONTH / LAST_DAY / MONTHS_BETWEEN / TRUNC
  • DECODE / LNNVL / NALVL / NVL / NVL2
  • LISTAGG / MEDIAN
  • DUMP

LENGTH 関数はもともと PostgreSQL にもありますが、orafce の LENGTH 関数はOracle Database と同じ動作になるように調整されています。下記の例では CHAR 型の列に文字列を格納した場合に、LENGTH 関数が後端のスペースを含めるかが変化する様子を示しています。

LENGTH 関数
postgres=> CREATE TABLE char1(c1 CHAR(5), c2 CHAR(5));
CREATE TABLE
postgres=> INSERT INTO char1 VALUES ('ABC', 'ABC');
INSERT 0 1
postgres=> SELECT oracle.LENGTH(c1), pg_catalog.LENGTH(c2) FROM char1;
 length | length
--------+--------
      5 |      3
(1 row)

パッケージ

Oracle Database には機能を拡張する機能としてパッケージと呼ばれる機能を持っています。Oracle Database には様々な機能を持つ多数の標準パッケージが提供されています。orafce では新規のパッケージを作ることはできませんが、以下の標準パッケージ互換機能が提供されています。

  • DBMS_ALERT
  • DBMS_ASSERT
  • DBMS_OUTPUT
  • DBMS_PIPE
  • DBMS_RANDOM
  • DBMS_UTILITY
  • UTL_FILE

以下の例は DBMS_OUTPUT と UTL_FILE パッケージを使った Oracle Database のファンクション例と orafce を利用して移行した例になります。完全に同じにはなりませんが、多くの部分がそのまま動作します。

Oracle Databaseの例

Oracle Database のパッケージ
SQL> CREATE OR REPLACE FUNCTION func1 RETURN VARCHAR2
  2  IS
  3    v1 VARCHAR2(32767);
  4    f1 UTL_FILE.FILE_TYPE;
  5  BEGIN
  6    f1 := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'test1.txt', 'R', 256);
  7    UTL_FILE.GET_LINE(f1, v1);
  8    DBMS_OUTPUT.PUT_LINE(v1);
  9    UTL_FILE.FCLOSE(f1);
 10
 11    RETURN v1;
 12  END;
 13  /

ファンクションが作成されました。

SQL> SET SERVEROUTPUT ON
SQL> SELECT func1() FROM DUAL;

FUNC1()
--------------------------------------------------------------------------------
DATA1

DATA1

orafce を使った例
DIRECTORY オブジェクトの代わりに UTL_FILE.UTL_FILE_DIR テーブルに利用可能なディレクトリを挿入します。

orafce のパッケージ
postgres=# INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('/home/postgres');
INSERT 0 1

postgres=# \connect postgres demo
You are now connected to database "postgres" as user "demo".
postgres=>
postgres=> CREATE OR REPLACE FUNCTION func1() RETURNS VARCHAR2
postgres-> AS $$
postgres$> DECLARE
postgres$>   v1 VARCHAR2(32767);
postgres$>   f1 UTL_FILE.FILE_TYPE;
postgres$> BEGIN
postgres$>   f1 := UTL_FILE.FOPEN('/home/postgres/', 'test1.txt', 'R', 256);
postgres$>   v1 := UTL_FILE.GET_LINE(f1);
postgres$>   PERFORM DBMS_OUTPUT.PUT_LINE(v1);
postgres$>   PERFORM UTL_FILE.FCLOSE(f1);
postgres$>
postgres$>   RETURN v1;
postgres$> END;
postgres$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=>  
postgres=> SELECT DBMS_OUTPUT.SERVEROUTPUT(TRUE);
 serveroutput
--------------

(1 row)

postgres=> SELECT func1();
DATA1
 func1
-------
 DATA1
(1 row)

postgres=>

トランザクション

残念ながらトランザクションの動作まで同じにはなりません。トランザクション中にエラーが発生すると COMMIT 文を実行してもトランザクション全体がロールバックされる仕様はそのままです。

トランザクションの動作
postgres=> CREATE TABLE data1(c1 NUMERIC PRIMARY KEY, c2 VARCHAR2(10));
CREATE TABLE
postgres=> BEGIN;
BEGIN
postgres=*> INSERT INTO data1 VALUES (100, 'data1');
INSERT 0 1
postgres=*> INSERT INTO data1 VALUES (100, 'duplicate');
ERROR:  duplicate key value violates unique constraint "data1_pkey"
DETAIL:  Key (c1)=(100) already exists.
postgres=!> COMMIT;
ROLLBACK

明日は @batakoo3 さんがテーブル継承について書かれる予定です。

8
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?