使用帶有預設參數的函式插入表

我正在嘗試編寫一個插入下面定義的表的函式：

DROP TABLE IF EXISTS pl_table;
CREATE TABLE pl_table
(
   id          SERIAL PRIMARY KEY,
   mandatory   VARCHAR NOT NULL,
   option1     VARCHAR DEFAULT null,
   option2     VARCHAR DEFAULT null
);

這個想法是只使一個參數是強制性的，而另外兩個是可選的。下面列出的函式適用於所有三個輸入，但如果提供的參數少於三個，則不會插入。我應該怎麼做才能使該功能適用於 Case1？

CREATE OR REPLACE FUNCTION pl_test_function(mand_arg varchar
                                         , opt_arg1 varchar DEFAULT NULL
                                         , opt_arg2 varchar DEFAULT NULL) RETURNS void AS 
$$
BEGIN
     INSERT INTO pl_table (mandatory, option1, option2) VALUES
            (mand_arg, opt_arg1, opt_arg2);
END;
$$
     LANGUAGE plpgsql STRICT;

SELECT pl_test_function('abc'); -- Case1: doesn't work
SELECT pl_test_function('abc', 'xyz', 'def'); -- Case2: works

請注意，pl_test_function在這個玩具範例中，它不返回任何內容，但實際上它更複雜的生產表親返回一個整數。

您需要STRICT根據文件刪除該子句：

…RETURNS NULL ON NULL INPUT 或 STRICT 表示只要函式的任何參數為空，該函式總是返回空。如果指定了這個參數，當有空參數時函式不執行；而是自動假定為空結果…

像這樣使用您的程式碼：

CREATE OR REPLACE FUNCTION pl_test_function(mand_arg varchar
                                         , opt_arg1 varchar DEFAULT NULL
                                         , opt_arg2 varchar DEFAULT NULL) RETURNS void AS 
$$
BEGIN
     INSERT INTO pl_table (mandatory, option1, option2) VALUES
            (mand_arg, opt_arg1, opt_arg2);
END;
$$
     LANGUAGE plpgsql ;

你可以用一個CASE語句來做到這一點：

CASE WHEN opt_arg1 IS NOT NULL AND opt_arg2 IS NOT NULL
    THEN INSERT INTO pl_table (mandatory, option1, option2) VALUES
            (mand_arg, opt_arg1, opt_arg2);
    WHEN opt_arg1 IS     NULL AND opt_arg2 IS NOT NULL
    THEN INSERT INTO pl_table (mandatory, option1, option2) VALUES
            (mand_arg, DEFAULT, opt_arg2);
    WHEN opt_arg1 IS NOT NULL AND opt_arg2 IS     NULL
    THEN INSERT INTO pl_table (mandatory, option1, option2) VALUES
            (mand_arg, opt_arg1, DEFAULT);
    ELSE INSERT INTO pl_table (mandatory, option1, option2) VALUES
            (mand_arg, DEFAULT, DEFAULT);
END CASE;

或者您可以使用動態 SQL：

DECLARE
  sql text;
BEGIN
  sql := format(
            'INSERT INTO pl_table (mandatory, option1, option2) VALUES (%L, %s, %s)',
            mand_arg,
            CASE WHEN opt_arg1 IS NULL THEN 'DEFAULT' ELSE quote_literal(opt_arg1),
            CASE WHEN opt_arg2 IS NULL THEN 'DEFAULT' ELSE quote_literal(opt_arg2),
         );
  EXECUTE sql;
END;

引用自：https://dba.stackexchange.com/questions/264745