Simple string return function with default param passed as NULL, returns NULL instead of string

Question

I have the following function:

CREATE OR REPLACE FUNCTION public.get_string(cmd_type text, udf_name text, 
group_name character varying DEFAULT 'usage'::character varying)
 RETURNS text
 LANGUAGE plpgsql
 AS $function$ 
BEGIN
 return 'This is the string: '''|| group_name ||''''::text;
END;
$function$

When i call it like this:

select public.get_string('test', 'myudf!', group_name=>null::character varying); 

It returns NULL.

I expect it to at least return:

This is the string: ''

However, when I call it like this:

select public.get_string('test', 'myudf!');

The I get the expected:

This is the string: 'usage'

Why does passing NULL to an optional parameter make the entire string NULL?

Answer 1

It is not mystic - any operation over NULL value is NULL again.

postgres=# select ('Hello' || null) is null ;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

You should to use a coalesce function and sanitize expression against NULL value.

postgres=# select ('Hello' || coalesce(null,'')) ;
┌──────────┐
│ ?column? │
╞══════════╡
│ Hello    │
└──────────┘
(1 row)

Maybe you know a Oracle database, where NULL and empty strings are equal. But it is true only for Oracle, elsewhere NULL is NULL and it is more aggressive.