Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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

Example 11-32. Returning a concatenated string

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)

Example 11-33. Assigning a concatenated value to a string

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire