はじめに

以前はMySQLをよく扱っていたのですが最近PostgreSQLを扱う頻度が高くなってきました。細かいコマンドがわからずに毎度調べているので使用頻度が高いコマンドをまとめることにしました。 DBのログイン、作り方、PL/pgSQLまで、MySQLと違うコマンドが多い（少し違うのではなく全然違う）ので少しでもPostgreSQLから離れると記憶からすぐに消えてしまうので心が折れました笑

DB操作

PL/pgSQL

前提知識

全体構成

PL/pgSQLの全体構成は以下の通り。基本的には変数の宣言（DECLARE）と処理記述（BEGIN）の後ろに書くだけ。 EXCEPTION範囲を入れ子にしたい時はBEGIN〜ENDを入れ子にすることも可能。

DECLARE　変数の宣言を行う
BEGIN　処理を記述する
EXCEPTION　例外を記述する
END;　処理の終わりを宣言する

ファンクション

関数の作成には「CREATE OR REPLACE FUNCTION」を使うと楽（関数が既にあれば上書きする）。削除だけしたい場合は「DROP FUNCTION」。

作成した関数一覧を表示したい場合は「pg_proc」システムビューを参照。「\df」メタコマンドを使うと情報を絞って表示。

ストアドプロシージャ

PostgreSQL11から実装された。ファンクションとの大まかな違いは以下の通り

戻り値がなくなる
呼び出し方がcall proc();のようになる（ファンクションはSELECT func();）
トランザクション管理ができる（コミットやロールバック）

変数の扱い

変数の記述方法は以下の通り

CREATE OR REPLACE FUNCTION func(integer)
RETURN integer AS $$
  DECLARE
    age1 integer;  // DECLARE部で宣言。 age integer := 3;　という記述でもOK
    age2 ALIAS FOR $1  // ALIAS FORで第1引数の値を指す変数を宣言
  BEGIN
    age1 := 3;  // :=で値を代入
    RETURN age1;
  END;
$$ LANGUAGE plplsql;

代入の記述は以下3つが使用可能。DECLARE部で宣言しながら代入することも可能。

=
:=
DEFAULT

条件の扱い

CREATE OR REPLACE FUNCTION func(integer)
RETURN text AS $$
  DECLARE
    age ALIAS FOR $1;
  BEGIN
    IF age > 60 THEN
      RETURN 'Senior'
    ELSIF age > 18 THEN
      RETURN 'Adult'
    ELSE
      RETURN 'Kids'
    END IF;
  END;
$$ LANGUAGE plplsql;

ループの扱い

CREATE OR REPLACE FUNCTION func(integer)
RETURN text AS $$
  DECLARE
    age ALIAS FOR $1;
    age_list text := '';
  BEGIN
    FOR i IN 1 .. age LOOP
      CONTINUE WHEN i = 2; // 特定条件の時にはループをスキップする
      age_list = text || ', ' || i
    END LOOP;
    RETURN age_list;
  END;
$$ LANGUAGE plplsql;

カーソル

該当の1行のみを返却するサンプル

CREATE OR REPLACE FUNCTION func(integer)
RETURN text AS $$
  DECLARE
    id ALIAS FOR $1;
    age integer; 
  BEGIN
    SELECT age INTO age FROM student WHERE id = id;
    return age;
  END;
$$ LANGUAGE plplsql;

カーソルと条件

CREATE OR REPLACE FUNCTION func(integer)
RETURN void AS $$
  DECLARE
    name text;
    age integer
    student_cursor CURSOR (over_age numeric) FOR SELECT name, age FROM student WHERE age = over_age;
  BEGIN
    OPEN student_cursor (over_age := $1);
    LOOP
      FETCH student_cursor INTO name, age;
      IF NOT FOUND THEN  // この3行はなくてもOK
        EXIT;
      END IF;
      RAISE NOTICE 'name=%, age=%', name, age;
    END LOOP;
    CLOSE student_cursor;
    RETURN;
  END;
$$ LANGUAGE plplsql;

FEATURED TAGS

aws ddd java kotlin rails ruby wordpress

ABOUT ME

温泉ソムリエエンジニア。サーバ構築からアプリケーションの開発まで手広く対応。Web系・サーバサイドの開発が特に得意

PostgreSQLよく使うキーワードまとめ（作成中）

TOC