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;

Community · Accepted Answer · 2020-06-20 09:12:55Z

22

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 20, 2016 at 21:27

Luc M

17.5k26 gold badges78 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D. Gibbs · Accepted Answer · 2020-05-07 22:42:30Z

5

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

answered May 7, 2020 at 22:42

D. Gibbs

5804 silver badges17 bronze badges

2 Comments

Brad Over a year ago

Default null worked for me. Now I can pass nothing in the call or a value. Thanks

Laurent Lyaudet Over a year ago

Useful, but it only works if you give also a default value to all arguments after it.

Collectives™ on Stack Overflow

PostgreSQL calling function with a null parameter

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related