【PostgreSQL】【ストアド】 UPDATE or INSERT を行うストアドの作成

初めに

 * 「UPDATEしてレコードがなかったら、INSERTをする」ストアドを作成する

準備

テーブル「person」

* デフォルトにあるテーブルだけど
CREATE TABLE person
(
  id character(8) NOT NULL,
  name character varying(100),
  sex character(1),
  updatedate timestamp without time zone,
  CONSTRAINT person_pkey PRIMARY KEY (id)
)

サンプル

updateOrInsertPerson.sql

CREATE OR REPLACE FUNCTION updateOrInsertPerson(
  targetId character(8),
  targetName character varying(100),
  targetSex character(1)
) RETURNS BOOLEAN AS
$$
BEGIN
  UPDATE person SET
   name = targetName,
   sex = targetSex,
   updatedate = TIMESTAMP 'now'
  WHERE id = targetId;
  IF NOT found THEN
    BEGIN
      INSERT INTO person(
        id,
        name,
        sex,
        updatedate)
      VALUES (
        targetId,
        targetName,
        targetSex,
        TIMESTAMP 'now');
    END;
  END IF;
  RETURN TRUE;
EXCEPTION WHEN unique_violation THEN
  RETURN FALSE;
END;
$$
LANGUAGE plpgsql;

実行例

その1

SELECT updateOrInsertPerson('X0000004', 'Mike', 'm');

SELECT id, name, sex, updatedate
  FROM person WHERE id = 'X0000004';

表示結果1

    id      name  sex          updatedate
----------+------+---+-------------------------
"X0000004"|"Mike"|"m"|"2014-09-01 22:28:54.965"

その2

SELECT updateOrInsertPerson('X0000005', 'Mary', 'f');

SELECT id, name, sex, updatedate
  FROM person WHERE id = 'X0000004' OR id = 'X0000005';

表示結果2

    id      name  sex          updatedate
----------+------+---+-------------------------
"X0000004"|"Mike"|"m"|"2014-09-01 22:28:54.965"
"X0000005"|"Mary"|"f"|"2014-09-01 22:33:52.847"

その3

SELECT updateOrInsertPerson('X0000004', 'Ken', 'm');

SELECT id, name, sex, updatedate
  FROM person WHERE id = 'X0000004' OR id = 'X0000005';

表示結果3

    id      name  sex          updatedate
----------+------+---+-------------------------
"X0000004"|"Ken" |"m"|"2014-09-01 22:28:54.965"
"X0000005"|"Mary"|"f"|"2014-09-01 22:33:52.847"


関連記事

ストアドファンクション

http://blogs.yahoo.co.jp/dk521123/33329958.html

PostgreSQL】UPDATE or INSERT を行うストアドの作成

http://blogs.yahoo.co.jp/dk521123/33945911.html

INSERT / UPDATEを行い、それ以外のレコードはDELETEをする

http://blogs.yahoo.co.jp/dk521123/33954185.html

データあればUPDATE、なければINSERT

http://blogs.yahoo.co.jp/dk521123/30656970.html

SQL】レコードが存在していなかったら、INSERTするには

http://blogs.yahoo.co.jp/dk521123/34139759.html