スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

PL/pgSQLで登録と更新を行うストアードプロシージャーの作成

Activeレコード使ってユーザ登録しようと思ったらパスワードのハッシュ化を実現するのが難しくなってしまい、やっぱり素のSQLが書きたくなってしまいました。

insertとupdateを同時にこなすようにしました。user_idがなかったらinsertになります。
またupdateでは、引数に値がセットされない場合は以前のデータを反映するようにしています。
以前のデータを受けるのはrecord型で定義しておくと、個別に変数を定義しなくて便利です。

CREATE TABLE t_user (
user_id BIGSERIAL NOT NULL
,user_nm TEXT NOT NULL
,mail TEXT NOT NULL
,password TEXT NOT NULL
,delete_ts TIMESTAMP WITH TIME ZONE
,ins_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
,upd_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
,bk TEXT
,PRIMARY KEY(user_id)
);


-- ユーザ登録、更新
-- 引数
-- p_user_nm : ユーザ名
-- p_mail : メール
-- p_password : パスワード
-- p_user_id : ユーザID(更新の時設定)
-- 戻り値
-- ユーザID
CREATE OR REPLACE FUNCTION add_user(
p_user_nm TEXT DEFAULT NULL
,p_mail TEXT DEFAULT NULL
,p_password TEXT DEFAULT NULL
,p_user_id BIGINT DEFAULT NULL
) RETURNS BIGINT AS $$
DECLARE
-- 以前のデータを受ける
w_row RECORD;
w_now TIMESTAMP WITH TIME ZONE := 'now';
BEGIN
-- 存在チェック
-- 以前のデータ取得
IF p_user_id IS NOT NULL THEN
SELECT
t1.*
INTO
w_row
FROM
t_user AS t1
WHERE
t1.user_id = p_user_id
;
IF w_row IS NULL THEN
RETURN -2;
END IF;
END IF;

IF p_user_id IS NULL THEN
-- 登録
IF p_user_nm IS NULL OR '' = p_user_nm OR
p_mail IS NULL OR '' = p_mail OR
p_password IS NULL OR '' = p_password
THEN
RETURN -1;
END IF;
INSERT INTO t_user (
user_nm
,mail
,password
) VALUES (
p_user_nm
,p_mail
,crypt(p_password, gen_salt('bf'))
);

RETURN currval('t_user_user_id_seq');
ELSE
-- 更新
UPDATE t_user SET
upd_ts = w_now
,user_nm = CASE
WHEN p_user_nm IS NULL OR '' = p_user_nm THEN w_row.user_nm
ELSE p_user_nm
END
,mail = CASE
WHEN p_mail IS NULL OR '' = p_mail THEN w_row.mail
ELSE p_mail
END
,password = CASE
WHEN p_password IS NULL OR '' = p_password THEN w_row.password
ELSE crypt(p_password, gen_salt('bf'))
END
WHERE
user_id = p_user_id
;
RETURN w_row.user_id;
END IF;
END;
$$ LANGUAGE plpgsql;
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


この記事にトラックバックする(FC2ブログユーザー)

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。