Postgresql – Insert to a table using function with default arguments

default valuefunctionsinsertpostgresql

I'm trying to write a function that makes an insert to the table defined below:

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
);

The idea is to make only one argument mandatory and other two optional. The function listed below works well for all three inputs being present, but doesn't make an insert if less than three arguments are provided. What should I do to to make the function work for the 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

Please note that the pl_test_function doesn't return anything in this toy example, but in reality its more complex production cousin returns an integer.

Best Answer

You need to remove the clause STRICT according to the documentation :

...RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically...

Use your code like this:

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 ;
Related Question