concat_ignore_null

This function will concatenate two strings.

The concatenation operator || has been redefined to use this function.

This function will concatenate two strings. The difference from the || operator is that our function will ignore null arguments.

CREATE FUNCTION concat_ignore_null(varchar, varchar) returns varchar AS '
    declare
        string_1         alias for $1;
        string_2         alias for $2;
    begin
        if string_1 is null then
           return string_2;
        end if;
 
        if string_2 is null then
           return string_1;
        end if;
 
        return string_1||string_2;
     end;
' LANGUAGE  'plpgsql' ;
 
-- concat operator that ignores null arguments
CREATE operator ||| (
  leftarg   = varchar,
  rightarg  = varchar,
  procedure = concat_ignore_null
);

Version 2.16

Still in use

= Version 2.16 =

-- this function will concatenate two strings
-- the difference from the || operator is that our function
-- will ignore null arguments
CREATE FUNCTION concat_ignore_null(varchar, varchar) returns varchar AS '
    declare
        string_1         alias for $1;
        string_2         alias for $2;
    begin
        if string_1 is null then
           return string_2;
        end if;
 
        if string_2 is null then
           return string_1;
        end if;
 
        return string_1||string_2;
     end;
' LANGUAGE  'plpgsql' ;
 
-- concat operator that ignores null arguments
CREATE operator ||| (
  leftarg   = varchar,
  rightarg  = varchar,
  procedure = concat_ignore_null
);