Extending PostgreSQL – A better CONCAT operator
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
Tags: CONCAT operator custom operators Extending PostgreSQL PostgreSQL
Posted in: PostgreeSQL
May 4, 2010
| 
Alexandru Gyulai | 
7 Comments
7 Responses
June 21, 2010 1
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 22, 2010 2
Nice dispatch and this mail helped me alot in my college assignement. Thank you as your information.
July 19, 2010 3
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 21, 2010 4
Brilliant
July 31, 2010 5
I would like to exchange links with your site blog.ropardo.ro
Is this possible?
September 16, 2010 6
One again, your idea is very
good.thank you!very much.
November 18, 2010 7
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);
Leave a Reply