Concatenation

Concatenation is the process of combining two (or more) strings together to produce another string. It is a standard operation built into PostgreSQL, and may therefore be used directly on variables within a PL/pgSQL function. When working with several variables containing character data, it is an irreplaceable formatting tool.

Concatenation can only be used with character strings. Strings are concatenated by placing the concatenation operator (||) between two or more character strings (string literal or a character string variable) that you wish to be combined. This can be used to combine two strings together to form a compound word, and to combine multiple strings together to form complex character string combinations.

Concatenation can only be used in situations where your function requires a string value, such as when a string must be returned (as shown in Example 11-32), or when you are assigning a new value to a string variable (as shown in Example 11-33).

CREATE FUNCTION compound_word(text, text) RETURNS text AS '
  DECLARE
     
     -- Define aliases for function arguments.
    word1 ALIAS FOR $1;
    word2 ALIAS FOR $2;
  
  BEGIN
     
     -- Return the resulting joined words.
    RETURN word1 || word2;
  
  END;

' LANGUAGE 'plpgsql';

When the words break and fast are passed as arguments to the compound_word() function, the function returns breakfast as the concatenated string:

booktown=# 
SELECT compound_word('break', 'fast');

 compound_word
---------------
 breakfast
(1 row)

CREATE FUNCTION title_and_author (text, text) RETURNS text AS '
    DECLARE
        
        -- Declare aliases for the two function arguments.
       title ALIAS for $1;
       author ALIAS for $2;
        
        -- Declare a text variable to hold the string result
        -- of the concatenation.
       result text;
    
    BEGIN
        
        -- Combine the title variable and the author
        -- variable together, placing a comma and the
        -- word by between them.
       result := title || '', by '' || author;
        
        -- Return the resulting string.
       return result;
    
    END;
' language 'plpgsql';

If you pass the strings Practical PostgreSQL and Command Prompt, Inc. to the function created in Example 11-33, the function returns Practical PostgreSQL, by Command Prompt, Inc. :

booktown=# 
SELECT title_and_author('Practical PostgreSQL','Command Prompt, Inc.');

               title_and_author
-----------------------------------------------
 Practical PostgreSQL, by Command Prompt, Inc.
(1 row)