PostgreSQLにはISNULLやNVLがないので関数で自作する

PostgreSQL には SQLServer の ISNULL や Oracle の NVL に対応する関数がないので値が NULL の場合に別の値に変換して取得するには COALESCE で代用するが多いようです。

ないなら作ってみようということで自作の ISNULL 関数を作成します。

PostgreSQLのISNULL、NVL関数対応

PostgreSQL には ISNULL や NVL 関数に対応するものがないので、自作します。

PostgreSQL の関数はオーバーロードが可能なので、自作するには ISNULL 関数の引数の型、戻り値の型を「なんでもＯＫ」にしてやる必要があります。

でないと integer や char などの呼び出す側での引数の型の分だけ ISNULL 関数を作らなくてはなりません。

これは無駄なので調べると PostgreSQL には anyelement 型という「なんでもOK型（正式名称は多様型）」があるのでこれを使います。

ISNULL関数作成

ISNULL 関数を作る前に準備をします。

まずはテーブル作成。

CREATE TABLE test_table
(
     name VARCHAR(20)
    ,age  smallint
    ,data_type_numeric numeric
)

データを挿入しておきます。

INSERT INTO test_table
    VALUES ( 'Taro',20 ),(NULL,30),('Hanako',NULL)

ISNULL関数 その1

実際に作成する ISNULL 関数の SQL は以下の通りです。

CREATE OR REPLACE FUNCTION isNull( v1 anyelement, v2 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0 ;
BEGIN
    result := v1;
    IF v1 IS NULL THEN
        result := v2;
    END IF;

    RETURN result;
END;
$$ LANGUAGE plpgsql

第 1 引数に NULL かどうか判定する値、第 2 引数に NULL の場合に返す値を指定して呼び出します。

SELECT
     ISNULL( name, 'No name') AS name
    ,ISNULL( age, 300) AS age
FROM test_table;

上記の実行結果は以下の通りです。

ISNULL関数 その2

単純に CASE WHEN を使ってもできそうです。

CREATE OR REPLACE FUNCTION ISNULL(v1 anyelement ,v2 anyelement)
RETURNS
anyelement AS $$
  SELECT CASE WHEN v1 IS NULL THEN v2 ELSE target END;
$$ LANGUAGE SQL;

SELECT
     ISNULL( name, 'No name') AS name
    ,ISNULL( age, 300) AS age
FROM test_table;

上記の実行結果も、その1と同様に以下の通り正常に動作します。

注意点

上記のその1、その2とも引数の型はなんでも良いのですが、numeric などの一部の型では明示的な型の変換が必要になります。

SELECT
     ISNULL( data_type_numeric, cast (10 as numeric)) AS name
FROM test_table;