スポンサーサイト

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

PL/pgSQLで検索条件を与えた時だけ検索する小ネタ

例えば以下のような、ストアードプロシージャーをあります。

CREATE OR REPLACE FUNCTION list_user(
p_delete_flag text
) RETURNS SETOF t_user AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
*
FROM
t_user
WHERE
delete_flag = p_delete_flag
ORDER BY
user_id
;
END;
$$ LANGUAGE plpgsql;

このとき、引数の削除フラグがNULLの場合には、削除フラグが立っていようがいまいが検索したいとします。
さて、もちろんこのままNULLを入れて呼び出すとフラグが設定されていないものだけ取ってきてしまうので、
うまくいきません。
単純に考えればIF文で判定してSELECT文を分ければ問題ありません。

CREATE OR REPLACE FUNCTION list_user(
p_delete_flag text
) RETURNS t_user AS $$
DECLARE
BEGIN
IF p_delete_flag IS NOT NULL THEN
RETURN QUERY SELECT
*
FROM
t_user
WHERE
delete_flag = p_delete_flag
ORDER BY
user_id
;
ELSE
RETURN QUERY SELECT
*
FROM
t_user
ORDER BY
user_id
;
END IF;
END;
$$ LANGUAGE plpgsql;

しかし、同じようなSELECT文を書かなければならず冗長です。
そこで一工夫。以下のようにしてみました。

CREATE OR REPLACE FUNCTION list_user(
p_delete_flag text DEFAULT NULL
) RETURNS t_user AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
*
FROM
t_user
WHERE
p_delete_flag IS NULL OR delete_flag = p_delete_flag
ORDER BY
user_id
;
END;
$$ LANGUAGE plpgsql;

これですと、p_delete_flagがNULLの時はWHERE句のORの前がTRUEになり、削除フラグがどんな値でも検索します。
p_delete_flagに値が入っている時は、ORの後の判定が有効になるので、指定した削除フラグの行が検索できます。
おまけとして、引数のDEFAULT値をNULLにしておきました。指定無しの場合はNULLになります。

複雑な検索系の画面で、検索条件が与えられている時だけWHERE句に追加したいみたいなSQLを作りたい時に役に立つと思います。
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


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

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