PostgreSQL calling function with a null parameter

Question

I have a function that updates a row:

CREATE OR REPLACE FUNCTION MyTable_UPDATE 
(
   _ID int,
   _Description text
) 
RETURNS bool
AS $$
DECLARE _OK boolean;

BEGIN
   _OK := false;
   UPDATE mytable SET
      Description = _Description 
   WHERE ID = _ID;
   _OK := true;

   RETURN _OK;  
END;
$$ LANGUAGE plpgsql STRICT;

When I call it with a value in description it works:

select MyTable_UPDATE(9, 'testing 123');

If I call it with a null value, it doesn't update, there is no error message and no value is returned:

select MyTable_UPDATE(9, null);

If I run an update query to set description = null, it works:

UPDATE mytable SET
   Description = null 
WHERE ID = 9;

Answer 1

From postgresql documentation on CREATE FUNCTION

STRICT

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.

In short, the function is not executed.

You have to remove the STRICT parameter to be able to pass a NULL value.

Answer 2

You can also update the function to default to a value if null is supplied to allow for optional parameters.

CREATE OR REPLACE FUNCTION MyTable_UPDATE 
(
   _ID int,
   _Description text default null
) 
...