- PostgreSQLへの接続
psql -h 192.168.137.131 -p 5432 postgres sa
tusc@6789#JKL
- ユーザー作成
CREATE USER name thunisoft createdb; --(等価: CREATE ROLE name LOGIN createdb);
-
データベース作成
create database test_database owner = thunisoft; -
ヘルプの表示 psqlで「help」と入力
[thunisoft@localhost ~]$ psql test_database
psql (9.3.6)
Type "help" for help.
test_database=> help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test_database=>
- 他のデータベースとの比較
a. 標準SQLを大部分サポート。
b. 複雑なクエリ、外部キー、マルチバージョン同時実行制御、トランザクションなどをサポート。
c. 多様なデータ型と関数を提供。
6. Navicat Premiumや組み込みのpsqlを使用して接続可能。Oracleのsqlplusに類似。
- OracleのストアドプロシージャをPostgreSQLに移行する。
PostgreSQLは複数のプロシージャ言語をサポートしており、ネイティブのものはpl/pgsqlです。他にもPL/Python、PL/perlなども利用可能。
以下ではPL/pgsqlを使用し、元のコードを簡略化・改名した例を示す。
Oracleでのプロシージャ定義:
1 -- 結果テーブルへデータ抽出
2 procedure scheme_test_etl(in_scheme_id varchar2,
3 in_bbq varchar2,
4 table_Prefix varchar2) is
5 v_error_rows number;
6 v_row_cnt number;
7 v_bbq varchar2(10);
8 v_model varchar2(30);
9 v_table varchar2(32);
10 v_sql varchar2(3000);
11
12 begin
13 v_bbq := in_bbq;
14 v_model := table_Prefix;
15
16 /*一時テーブルのクリア*/
17 execute immediate 'truncate table TEMP_EDQ10_DIM_LEVEL_ANAYS';
18
19 /*一時テーブルへのデータ挿入*/
20 v_sql := 'insert into TEMP_EDQ10_DIM_LEVEL_ANAYS select * from ' ||
21 v_model || '_DIM_LEVEL_ANAYS';
22 execute immediate v_sql;
23
24 -- チェック対象テーブル情報
25 v_table := v_model || '_checkedtb';
26 execute immediate 'delete from ' || v_table ||
27 ' t where t.schemeid_ = ' || chr(39) || in_scheme_id ||
28 chr(39) || ' and t.bbq =' || v_bbq;
29 for c in (select t.PERIOD_ BBQ_,
30 t.SCHEMEID_,
31 t.HIERARCHY_, -- データ階層
32 t2.facttableid_, -- テーブル名
33 100 - sum(nvl(DUETABLESCORE_, 0) - nvl(TABLESCORE_, 0)) table_score, -- テーブルスコア
34 sum(nvl(t.TOTALCNT_, 0)) TOTALCNT_, -- この期間・この顧客・このルールの全データ行数 => ルールチェック回数
35 sum(nvl(t.ERRORCNT_, 0)) ERRORCNT_ -- エラー数 => エラーチェック回数
36 from temp_EDQ10_STATISTICS t, EDQ10_RULES t2
37 where t.ruleid_ = t2.id_
38 group by t.PERIOD_,
39 t.SCHEMEID_,
40 t.HIERARCHY_,
41 t2.facttableid_) loop
42
43 v_error_rows := get_scheme_error_rows(c.hierarchy_, c.facttableid_); -- 方案のエラー行数取得
44 v_row_cnt := get_scheme_total_rows(c.hierarchy_, c.facttableid_); -- 方案の合計行数取得
45
46 v_sql := 'insert into ' || v_table || '
47 (bbq,
48 SCHEMEID_,
49 hierarchy_,
50 checkedtable_score,
51 checkedtable_id,
52 checkedtable_rows,
53 error_rows,
54 checked_times,
55 error_times)
56 select :1,:2,:3,:4,:5,:6,:7,:8,:9 from dual';
57 execute immediate v_sql
58 using c.BBQ_, c.SCHEMEID_, c.HIERARCHY_, c.table_score, c.facttableid_, v_row_cnt, v_error_rows, c.TOTALCNT_, c.ERRORCNT_;
59 end loop;
60
61 commit;
62
63 exception
64 when others then
65 dbms_output.put_line(sqlcode || '--' || sqlerrm);
66 rollback;
67 end;
OracleプロシージャPostgreSQLでの関数定義:
1 create or replace function DataClaen_ETL.scheme_test_etl(in_scheme_id in varchar,in_bbq in varchar,table_Prefix in varchar)
2 returns void as $func$
3 declare
4 -- 結果テーブルへデータ抽出
5 v_error_rows numeric;
6 v_row_cnt numeric;
7 v_bbq varchar(10);
8 v_model varchar(30);
9 v_table varchar(32);
10 v_sql text;
11
12 referrer_keys CURSOR IS
13 select t.period_ bbq,
14 t.SCHEMEID_,
15 t.hierarchy_, -- データ階層
16 100 -
17 sum(t.DUERULESCORE_ - t.RULESCORE_) scheme_score, -- ==> 方案スコア
18 sum(t.TOTALCNT_) TOTALCNT_, -- この期間・この顧客・このルールの全データ行数 => ルールチェック回数
19 sum(t.ERRORCNT_) error_times, -- エラー数 => エラーチェック回数
20 count(distinct t.ruleid_) scheme_rule_cnt, -- 方案のルール総数
21 sum(case
22 when t.ERRORCNT_ = 0 then
23 0
24 else
25 1
26 end) error_rule_cnt -- エラー発生ルール数
27 from temp_EDQ10_STATISTICS t
28 group by t.period_, t.SCHEMEID_, t.hierarchy_
29 order by t.period_, t.SCHEMEID_, t.hierarchy_;
30
31 begin
32 v_bbq := in_bbq;
33 v_model := table_Prefix;
34
35 /*一時テーブルのクリア*/
36 execute 'truncate table TEMP_EDQ10_DIM_LEVEL_ANAYS';
37 /*一時テーブルへのデータ挿入*/
38 v_sql := 'insert into TEMP_EDQ10_DIM_LEVEL_ANAYS select * from ' ||
39 v_model || '_DIM_LEVEL_ANAYS';
40 execute v_sql;
41
42 -- 方案概要テーブル
43 v_table := v_model || '_scheme_vi';
44 v_sql := 'delete from ' || v_table || ' t where t.schemeid_ = ' ||
45 chr(39) || in_scheme_id || chr(39) || ' and t.PERIOD_ =' ||chr(39)||v_bbq||chr(39);
46
47 execute v_sql;
48
49 for c in referrer_keys loop
50 v_error_rows := dataclaen_etl.get_scheme_error_rows(c.hierarchy_, 'AA'); -- 方案のエラー行数取得
51 v_row_cnt := dataclaen_etl.get_scheme_total_rows(c.hierarchy_, 'AA'); -- 方案の合計行数取得
52
53 execute '
54 insert into ' || v_table || '
55 (PERIOD_,
56 SCHEMEID_,
57 hierarchy_,
58 scheme_score,
59 row_cnt,
60 ERR_ROW_CNT,
61 ruler_checked_times,
62 error_times,
63 scheme_rule_cnt,
64 error_rule_cnt)
65 values ('||chr(39)||c.bbq||chr(39)||' , '||chr(39)||c.SCHEMEID_||chr(39)||' , '||c.hierarchy_||' , '||c.scheme_score||' ,'|| v_row_cnt||' ,'|| v_error_rows||' , '||c.totalcnt_||' , '||c.error_times||' ,'|| c.scheme_rule_cnt||' , '||c.error_rule_cnt||')';
66 end loop;
67
68 $func$ language plpgsql;
PostgreSQL関数実装方法の違い:
a. plpgsqlにはpackageの概念が存在しない（Oracleではpackageでprocedureとfunctionを整理していた）。PostgreSQLではschemaで代替可能（つまりDataClaen_ETLというschemaを作成する）。 b. PostgreSQLにはプロシージャがないが、関数は存在し、Oracleの関数と同様の機能を持つ。さらに、PostgreSQLの関数はreturns voidをサポートしており、これにより関数とプロシージャの違いはほとんどない。 c. PL/pgSQLではカーソルは事前に宣言する必要があり、Oracleのようにforループ内で直接定義することはできない。 d. PL/pgSQLでは関数の開始と終了を示す識別子が必要であり、実装言語を明示する必要がある（例：language plpgsql）。 e. PostgreSQLにはdualテーブルがない。 f. PL/pgSQLでは自前のトランザクション処理は不可であり、commit文は使用不可。