スポンサーサイト

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

PL/pgSQLでinsert文実行の後にidを取得する

PostgreSQLのPL/pgSQLでinsert後に自動連番されるidを取得する方法です。
以下のテーブルを想定します。
CREATE TABLE t_user (
user_id BIGSERIAL NOT NULL
,user_name TEXT NOT NULL
,PRIMARY KEY(user_id)
)

最初はよく見かけるサンプルですが、あまりお勧めしないのがLASTVAL()。
これは最後に発行された連番を返す関数なので一見うまくいきそうなのですが、triggerなどで別のテーブルに連番が発行された時に狂います。
以前slony-Iでtriggerが使われていて、バグを特定するのが大変だったことがありました。
ソースは以下のようになります。
CREATE OR REPLACE FUNCTION insert_user(
p_user_name TEXT
) RETURNS BIGINT AS $$
DECLARE
BEGIN
INSERT INTO t_user (
user_name
) VALUES (
p_user_name
);
RETURN LASTVAL();
END;
$$ LANGUAGE plpgsql;

LASTVAL()の改良としてはCURRVAL()があります。
これならば指定したシーケンスの最後の値なので、問題無くIDを回収できます。
強いて欠点を上げれば、いちいちシーケンスの名前を覚えておかないといけないことです。
ソースはこちら。
CREATE OR REPLACE FUNCTION insert_user(
p_user_name TEXT
) RETURNS BIGINT AS $$
DECLARE
BEGIN
INSERT INTO t_user (
user_name
) VALUES (
p_user_name
);
RETURN CURRVAL('t_user_user_seq_id');
END;
$$ LANGUAGE plpgsql;

シーケンス名を覚えて無くても使えるのが最後に紹介する方法です。
RETURNINGを使うとINSERT, UPDATE, DELETEでも値を返すことができます。影響のあった行だけ返します。
一行だけ変えるSQLならINTO句が使えて、値を変数で受けて返します。
CREATE OR REPLACE FUNCTION insert_user(
p_user_name TEXT
) RETURNS BIGINT AS $$
DECLARE
w_id BIGINT;
BEGIN
INSERT INTO t_user (
user_name
) VALUES (
p_user_name
) RETURNING
user_id
INTO
w_id
;
RETURN w_id;
END;
$$ LANGUAGE plpgsql;
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


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

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