Insert to a table using function with default arguments

Question

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.

Answer 1

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 ;

Answer 2

You can do it with a CASE statement:

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;

or you can use dynamic 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;