au PAY マーケット

PostgreSQLについて

コマンド:
————————————-
ip addr | grep inet

◆ユーザー名をpostgresに切り替え
———————————————
su – postgres
———————————————

◆psqlを起動する
———————————————
[postgres ~]$ psql – db1 -U postgres
———————————————
【説明】
 db1:接続するデータベース名です。
 postgres:接続するときのユーザー名です。
[postgres ~]$ psql – データベース名 -U ユーザー名

◆psqlを終了する
psqlを終了するときは、「\q」コマンドを入力します。
———————————————
 db1=# \q
 [postgres ~]$
———————————————


◆psql起動オプション

オプション意味
-U ユーザ接続するときのユーザ名。デフォルトはOSユーザと同名ユーザ
-d データベース接続先データベース名。デフォルトはユーザ名と同名データベース
-h ホスト接続ホスト名。デフォルトはローカルホスト
-p ポート番号接続先ポート番号。デフォルトは5432ポート
-f 入力ファイル入力ファイルに書かれたSQLコマンドをひととおり実行して終了する
-o 出力ファイル結果出力先ファイルを指定する
-c 'SQLコマンド'指定SQLコマンドだけい実行して終了する


◆よく使うバックスラッシュコマンド

コマンド意味
\qpsqlを終了する
\lデータベース一覧を表示
\dデータベース内のテーブルなどの一覧
\d テーブルなどテーブルなどの定義情報を表示
\i ファイルファイルを読み込んで実行
\o ファイル実行結果の出力先ファイルを指定
\password [ユーザ名]現在ユーザまたは指定ユーザのパスワード変更
\encoding [文字エンコーディング]クライアント文字エンコーディングの表示・変更
\h SQLコマンドSQLコマンドのヘルプを表示
\?バックスラッシュコマンドのヘルプを表示


◆基本的な演算子

演算子意味
x + y加算
x - y減算
x * y乗算
x / y除算
x % y剰余
x || y文字列の連結


◆条件式でよく使う比較演算子

演算子意味
x = y等しい
x != y等しくない
x <> y等しくない
x < y左辺が右辺より小さい
x <= y等しいか、左辺が右辺より小さい
x > y左辺が右辺より大きい
x >= y等しいか、左辺が右辺より大きい


◆論理演算子

演算子意味
NOT 条件式条件式が真ではない
条件式 AND 条件式条件式が真ではない
条件式 OR 条件式条件式が真ではない


◆トランザクション制御のSQL文

SQL意味
BEGIN;トランザクションを開始する
ROLLBAK;トランザクションをキャンセルする
COMMIT;トランザクションを確定する


◆SQLコマンドの分類

区分説明該当するコマンド
DMLテーブルの行データを参照・操作するコマンドSELECT,INSERT,UPDATE,DELETE
DDLテーブル等のオブジェクトを定義・変更・削除するコマンドCREAE,DROP,ALTERなど
DCLその他の制御のためのコマンドGRANT,REVOKE,COMMIT,ROLLBACKなど


◆主なエスケープシーケンス

エスケープシーケンス意味
\n改行(LF、0x0a)
\r復帰(CR、0x0d)
\tタグ
\「\」文字
\'「'」文字
\0128進数3桁で示すコードの文字
\xA116進数2桁で示すコードの文字
\u304216進数4桁のユニコードの文字
\U0000004116進数8桁のユニコードの文字


◆代表的な集約関数

集約関数説明
count(c)行数を返す
max(c)、min(c)列の最大値、最小値を返す
sum(c)列の合計値を返す


◆「::」演算子を使い、型指定あるいは型変換
 ’文字列’::データ型
 CAST(’文字列’ AS データ型)
 データ型 ‘文字列’

例:
SELECT md5(CURRENT_TIMESTAMP::text);



◆NULLと論理式

論理式結果
TRUE AND NULLNULL
FALSE AND NULLFALSE
TRUE OR NULLTRUE
FALSE OR NULLNULL
NOT NULLNULL


◆関数の作成・削除
CREATE [OR REPLACE] FUNCTION 関数名(引数定義) RETURNS 戻り値のデータ型
 [STRICT] [関数定義のその他オプション]  LANGUAGE 言語
 AS { ‘関数定義’ | ‘オブジェクトファイル’, ‘実装関数名’ };

 戻り値のデータ型は:
  データ型
  SETOF データ型
  TABLE (列名 データ型[, …])

 DROP FUNCYION 関数名 [(引数定義)];


テーブル結合

◆テーブル結合とは
 テーブル結合とは、複数のテーブル間の同じ意味を持つ列を基準に、テーブル同士を連携させる仕組みのことです。

◆FROM句を使った交差結合
 まずはFROM句を使った結合方法を試してみます。SELECTでは、FROM句に複数のテーブルをカンマ区切りで記述することができます。下記の二つの問い合わせを組み合わせてみましょう。
▲例 会員番号と会員の登録名の情報が含まれるt_usersテーブル

☆例☆

SELECT a.user_id, a.reg_name FROM t_users a;
 user_id   | reg_name
 ーーーーー+ーーーーーー
 1001    | Bashinka
 1002    | たなか
 1003    | Tom
(3 行)                              

▲例 会員番号と会員の認証方式の情報が含まれるt_users_authsテーブル

☆例☆

SELECT b.user_id, b.method FROM t_users_auths b;
 user_id   | method
 ーーーーー+ーーーーーー
 1001    | qq
 1002    | yahoo
 1003    | google
 1004    | Twitter
(4 行)                              


 この二つのテーブルをFROM句に指定します。それぞれのテーブルには別名として「a」「b」を付けておきます。別名はFROM句のテーブル名の指定のあとにスペースを空けて記述します。テーブルに別名を付けておくと、a.user_idのようにどのテーブルの列かを指定する際に短い記述で済みます。
▲例 FROM句に複数テーブルを指定する

☆例☆

SELECT a.user_id, a.reg_name, b.user_id, b.method
 FROM t_users a, t_users_auth b;                              


 これを実行すると、単純に二つのテーブルの内容を組み合わせた表が出力されます。
▲例 二つのテーブルの内容を組み合わせた表が出力された

☆例☆

 user_id   | reg_name  | user_id | method
 ーーーーー+ーーーーーーー+ーーーーー+ーーーーー
 1001    | Bashinka  | 1001  | qq
 1002    | たなか    | 1001  | qq
 1003    | Tom     | 1001  | qq
 1001    | Bashinka  | 1002  | yahoo
 1002    | たなか    | 1002  | yahoo
 1003    | Tom     | 1002  | yahoo
 1001    | Bashinka  | 1003  | google
 1002    | たなか    | 1003  | google
 1003    | Tom     | 1003  | google
 1001    | Bashinka  | 1004  | Twitter
 1002    | たなか    | 1004  | Twitter
 1003    | Tom     | 1004  | Twitter
(12 行)                              


 FROM句に二つのテーブルを記述すると、両テーブルの行のすべての組み合わせが作られます。t_usersが3行、t_users_authが4行なので、3行 X 4行で12行の結果が現れます。これをテーブルの交差結合(クロスジョイン)、または直積結合と呼びます。

◆FROM句を使った内部結合

▲例

Code


                              

◆JOIN構文を使ったテーブル結合

▲例

Code


                              

◆外部結合

▲例

Code


                              

プロシージャ内で使用できる命令

◆変数を定義するには
DECLARE

●DECLARE●
★変数定義

★POINT★


文法
variable_name [ CONSTANT ] type [{:= | DEFAULT} initial_value];
  variable_name type [:= initial_value ];
 
引数
 ・variable_name:変数名
 ・type:型
 ・initial_value:初期値
                                   


DECLARE」により、変数を定義することができます。PostgreSQLでのDECLAREはDECLARE部を宣言するためのものです。
PostgreSQL(PL/pgSQL)での変数は、「DECLARE」中に定義します。複数の変数を定義したい場合、DECLARE部に複数記述します。この際に、「:=」によって初期値を与えることができます。
変数への代入は、「SELECT 値 INTO variable_name」のようにSELECT命令で行うか、「variable_name := 値」のように、代入演算子「:=」を使うことで行います。
▲例 i、sという二つの変数を定義し、変数iには200を、sには’test string’を代入する関数を定義します。



Code
1
2
3
4
5
6
7
8
9
10
11
12

CREATE FUNCTION test() RETURNS INTEGER AS $$
DECLARE
 i INTEGER;
 s VARCHAR;
BEGIN
 i := 200;
 s := ‘test string’;
 RAISE NOTICE ‘i=% s=%’, i, s;
 RETURN i;
END;
$$ LANGUAGE ‘plpgsql’
                              


◆カーソルを定義するには
DECLARE CURSOR
FETCH
カーソル変数


 カーソルを定義すると、プロシージャ、ファンクション内でSELECTに対する結果を1行ごとに処理することが可能になります。
 PostgreSQLでのカーソルは、「DECLARE部」中で「CURSOR FOR」により定義します。Oracleと似ていますが、「カーソル名 CURSOR FOR SELECT命令;」となるので注意してください。OPEN、CLOSE及びFETCHはOracleと同じです。


●DECLARE CURSOR●
★カーソル変数定義

★POINT★


文法
cursor_name CURSOR FOR select_statement;
 
引数
 ・cursor_name:カーソル名
 ・select_statement:SELECT命令
                                   


DECLARE CURSOR」により、カーソル変数を定義することができます。
▲例 「SELECT a FROM foo」という命令を伴うカーソルcursor_nameを定義します。

Code
1
2
3
4
5
6
7

CREATE OR REPLACE FUNCTION test_cursor() RETURNS void AS $$
DECLARE
 cursor_name CURSOR FOR SELECT a FROM foo;
BEGIN
END;
$$ LANGUAGE ‘plpgsql’;
                              


定義したカーソルから、結果を得るには、「FETCH」命令を使います。カーソルのOPEN、CLOSEも必要です。
PL/pgSQLでは、「レコード変数」と、「FORループ命令」によってカーソルと同等な機能を実現することができます。

●FETCH●
★カーソルから行を抽出

★POINT★


文法
FETCH [direction {IN | FROM}] cursor_name
INTO variable_name [,variable_name…];
 
引数
 ・cursor_name:カーソル名
 ・variable_name:変数名
 ・direction:方向。FORWARD、BACKWARD、RELATIVEの何れか
                                   


FETCH」により、カーソルから1行分のデータを抽出することができます。

PostgreSQLの「FETCH」命令は、Oracleに似ています。「INTO」に続けて、変数を指定します。カンマで区切り、複数を指定することが可能です。レコード型(ROWTYPE)を使用することもできます。カーソルの終わりであるかは、「FOUND変数」を使用します。
▲例 定義したカーソルをオープンし、そのレコードの内容を変数iに代入しながら、すべてのレコードをループします。

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION test_fetch() RETURNS void AS $$
DECLARE
 cursor_name CURSOR FOR SELECT a FROM foo;
 i INTEGER;
BEGIN
 OPEN cursor_name;
 LOOP
  FETCH cursor_name INTO i;
  EXIT WHEN NOT FOUND;
  RAISE NOTICE ‘i=%’, i;
 END LOOP;
 CLOSE cursor_name;
END;
$$ LANGUAGE ‘plpgsql’;
                              

カーソルパラメータ
 PostgreSQLでは、カーソルをOPENする際に、パラメータを指定することができます。このとき、カーソルの宣言時にパラメータも定義されている必要があります。
▲例 パラメータpを持つカーソルを宣言します。OPEN時にpの値として1を渡します。

Code
1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE
 cursor_name CURSOR(p NUMBER) FOR SELECT a FROM foo WHERE a=p;
 i NUMBER;
BEGIN
 OPEN cursor_name(1);
 LOOP
  FETCH cursor_name INTO i;
  EXIT WHEN cursor_name%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE( ‘i=’ || i);
 END LOOP;
 CLOSE cursor_name;
END;
                              

カーソル変数
★カーソルをパラメータで渡す

★POINT★


文法
cursor_name REFCURSOR;
OPEN cursor_name FOR select_statement;
 
引数
 ・cursor_name:カーソル変数名
 ・select_statement:SELECT命令
                                   

PostgreSQLでのカーソル変数は、「REFCURSOR」型で宣言することができます。OPEN時にSELECT命令を設定します。
▲例 カーソル変数をパラメータとするプロシージャを定義します。

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE FUNCTION test_cursor_pc(pc REFCURSOR) RETURNS void AS $$
DECLARE
 i INTEGER;
BEGIN
 LOOP
  FETCH pc INTO i;
  EXIT WHEN NOT FOUND;
 END LOOP;
END;
$$ LANGUAGE ‘plpgsql’;
 
CREATE FUNCTION test_cursor() RETURNS void AS $$
DECLARE
cursor_name REFCURSOR;
BEGIN
 OPEN cursor_name FOR SELECT a FROM foo;
 PERFORM test_cursor_pc(cursor_name);
 CLOSE cursor_name;
END;
$$ LANGUAGE ‘plpgsql’
                              


◆ループ処理をするには
LOOP
WHILE
REPEAT
FOR
EXIT
COUNTINUE

●LOOP●
★繰り返し

★POINT★


文法
LOOP
 statement
END LOOP;
 
引数
 ・statement:繰り返し実行する命令
                                   


Code
1

                              

●WHILE●
★条件付きの繰り返し

★POINT★


文法
WHILE boolean_expression LOOP
 statement
END LOOP;
 
引数
 ・boolean_expression:ループする条件式
 ・statement:繰り返し実行する命令
                                   


WHILE」命令は、ある条件をもとに、繰り返し処理を行います。


Code
1

                              

●REPEAT●
★条件付きの繰り返し

★POINT★


文法
REPEAT
 statement
 UNTIL boolean_expression
END REPEAT;
 
引数
 ・boolean_expression:終了条件
 ・statement:繰り返し実行する命令
                                   


Code
1

                              

●FOR●
★FORループ

★POINT★


文法
FOR i IN [REVERSE] from..to LOOP
 statement
END LOOP;

FOR rec IN select_statement LOOP
 statement
END LOOP;
 
引数
 ・i:ループカウンタ
 ・from:ループの初期値
 ・to:ループの終了値
 ・statement:繰り返し実行する命令
 ・rec:レコード変数
 ・select_statement:ループの対象となるSELECT命令
                                   


Code
1

                              

●EXIT●
★ループを抜ける

★POINT★


文法
EXIT [ label ] WHEN expression ];
 
引数
 ・label:どのループをぬけるかのラベル指定
 ・expression:ループを抜ける条件
                                   


Code
1

                              

●COUNTINUE●
★ループの先頭に戻る

★POINT★


文法
CONTINUE [ label ] [ WHEN expression ];
 
引数
 ・label:どのループに戻すかのラベル指定
 ・expression:先頭に戻る条件式
                                   


CONTINUE」命令を使用すると、ループの先頭に戻って処理が続けられます。


Code
1

                              


◆条件を判断するには
IF
CASE
GOTOとラベル

●IF●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●CASE●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●GOTOとラベル●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              


◆パラメータを使用するには
パラメータ付きプロシージャ
パラメータ付きファンクション
テーブルを返すファンクション

●パラメータ付きプロシージャ●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●パラメータ付きファンクション●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●テーブルを返すファンクション●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              


◆動的SQL
EXECUTE

●EXECUTE●
★動的にSQL命令を実行

 
■文法
  EXECUTE statement [INTO v [,v…]]  
■引数
  ・statement:文字列データによるSQL命令
  ・v:実行結果を受け取る変数
 

EXECUTE」命令により、文字列で与えられたSQL命令を実行することができます。
文字列データをSQL命令として実行するしくみが、「動的SQL」です。文字列データをSQLとして実行するには、「EXECUTE」命令を使用します。
PostgreSQLでのEXECUTE命令では、「EXECUTE」に続けて実行したいSQL命令を指定します。必要なら、「INTO」に続けて変数を指定することで、実行結果を変数に代入させることができます。

▲例 引数で与えられたテーブル名でテーブルを作成するファンクションf_create_tableを定義します。

例:
1
2
3
4
5
6
7

CREATE FUNCTION f_create_table(table_name VARCHAR)
 RETURNS void AS $$
BEGIN
 EXECUTE ‘CREATE TABLE’ || table_name || ‘(a INTEGER)’;
END;
$$ LANGUAGE ‘plpgsql’;
                              

●●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              


◆例外処理
システム例外
ユーザ定義例外
RAISE
RAISERROR
DECLARE HANDLER
SIGNAL
TRY CATCH

●システム例外●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●ユーザ定義例外●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●RAISE●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●RAISERROR●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●DECLARE HANDLER●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●SIGNAL●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              

●TRY CATCH●


★POINT★


文法

 
引数
 ・:
 ・:
 ・:
                                   


Code
1

                              






Code
1

                              


前の記事

上は

次の記事

うちに <時間幅>
ショップチャンネル