Extending PostgreSQL – A better CONCAT operator

Share

PostgreSQL users have the option of extending the set of functions and operators available. If you have a common SQL or programmatic routine, custom functions can be an effective way to more succinctly and efficiently accomplish your tasks. Likewise, custom operators can be created to call these functions (or existing built-in functions) in order to make more efficient and legible SQL statements. PostgreSQL provides four kinds of functions: query language functions (functions written in SQL), procedural language functions (functions written in, for example, PL/pgSQL or PL/Tcl), internal functions, C-language functions.

Creating custom functions or operators in PostgreSQL is not difficult at all. Here is an example of a user-defined operator that I find very useful.

A better CONCAT operator for PostgreSQL

In PostgreSQL, (unlike Oracle for example) concatenation with a null column (using || operator) nulls the result.
Here’s a quick example:

CREATE TABLE persons (
id        SERIAL      PRIMARY KEY,
firstname TEXT,
surname   TEXT        NOT NULL,
title     TEXT
);
 
INSERT INTO persons(firstname, surname, title)
VALUES ('Edward', 'Hyde', NULL);
INSERT INTO persons(firstname, surname, title)
VALUES ('Henry', 'Jekyll', 'Dr.');
INSERT INTO persons(firstname, surname, title)
VALUES (NULL, 'Fogle', NULL);
 
SELECT * FROM persons;
 
id         firstname          surname            title
--         ---------          --------           ----
1          Edward             Hyde               NULL
2          Henry              Jekyll             Dr.
3          NULL               Fogle              NULL
 
SELECT title || ' ' || firstname || ' ' || surname AS all_text FROM persons;
 
all_text
-----------------
NULL
Dr. Henry Jekyll
NULL

As you can see, the rows containing NULL values are omitted (concatenation result is NULL). This might not be the behavior you expect. There are several solutions to handle this situation, including the use of the SQL CASE WHEN expression, COALESCE or ARRAY_TO_STRING built-in functions.
But a more interesting and elegant approach is to make use of the PostgreSQL extensibility and to create an alternate version of the CONCAT operator.
First, we define a function which takes two TEXT values, and returns:
– One value if one of them is null (null if both are null)
– Concatenation of the values with a space between them if neither is null

CREATE OR REPLACE FUNCTION my_concat ( text, text )
RETURNS text AS
'SELECT
CASE WHEN $1 IS NULL THEN $2
WHEN $2 IS NULL THEN $1
ELSE $1 || '' '' || $2 END;'
LANGUAGE SQL IMMUTABLE;

Then we create a custom operator “||+

CREATE OPERATOR ||+ (LEFTARG = TEXT,  RIGHTARG = TEXT, PROCEDURE = my_concat);

Using this new operator in a query is very easy and will produce a more natural result.

SELECT title ||+ firstname ||+ surname AS all_text FROM persons;
 
all_text
----------------------
Edward Hyde
Dr. Henry Jekyll
Fogle

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.

7 thoughts on “Extending PostgreSQL – A better CONCAT operator”
  • Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

    June 21, 2010 at 10:33 am
  • WP Themes says:

    Nice dispatch and this mail helped me alot in my college assignement. Thank you as your information.

    June 22, 2010 at 5:49 am
  • Pretty nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

    July 19, 2010 at 4:37 am
  • Rui Pinto says:

    Brilliant

    July 21, 2010 at 4:59 pm
  • aleksptiz says:

    I would like to exchange links with your site blog.ropardo.ro
    Is this possible?

    July 31, 2010 at 6:15 pm
  • jerseys says:

    One again, your idea is very

    good.thank you!very much.

    September 16, 2010 at 2:11 am
  • David Frankson says:

    I was looking for a replacement for SQL Server’s + operator so it would treat NULL as ”, and could work with ints, floats, etc. I had to define 3 functions to over the operator. Posting here to save someone else time:

    CREATE OR REPLACE FUNCTION concat1(text,anyelement)
    RETURNS text AS
    $BODY$

    SELECT COALESCE($1,”) || COALESCE(CAST($2 as text),”)

    $BODY$
    LANGUAGE sql IMMUTABLE;

    CREATE OR REPLACE FUNCTION concat2(anyelement, text)
    RETURNS text AS
    $BODY$

    SELECT COALESCE($1,”) || COALESCE(CAST($2 as text),”)

    $BODY$
    LANGUAGE sql IMMUTABLE;

    CREATE OR REPLACE FUNCTION concat3(text, text)
    RETURNS text AS
    $BODY$

    SELECT COALESCE($1,”) || COALESCE($2,”)

    $BODY$
    LANGUAGE sql IMMUTABLE;

    CREATE OPERATOR + (LEFTARG = text, RIGHTARG = anyelement, PROCEDURE = concat);
    CREATE OPERATOR + (LEFTARG = anyelement, RIGHTARG = text, PROCEDURE = concat2);
    CREATE OPERATOR + (LEFTARG = text, RIGHTARG = text, PROCEDURE = concat3);

    November 18, 2010 at 11:31 pm

Comments are closed.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close