User Tools

Site Tools


podoc:concat_ignore_null_sql_function

Database Function Description of "concat_ignore_null"

Name

concat_ignore_null

Description

This function will concatenate two strings.

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

Purpose

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

Schema

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

History

Used first

Version 2.16

Used last

Still in use

Change history

= 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
);
podoc/concat_ignore_null_sql_function.txt · Last modified: 2007/04/23 10:25 by Luud