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.
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 amNice dispatch and this mail helped me alot in my college assignement. Thank you as your information.
June 22, 2010 at 5:49 amPretty 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 amBrilliant
July 21, 2010 at 4:59 pmI would like to exchange links with your site blog.ropardo.ro
July 31, 2010 at 6:15 pmIs this possible?
One again, your idea is very
good.thank you!very much.
September 16, 2010 at 2:11 amI 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);
November 18, 2010 at 11:31 pmCREATE OPERATOR + (LEFTARG = anyelement, RIGHTARG = text, PROCEDURE = concat2);
CREATE OPERATOR + (LEFTARG = text, RIGHTARG = text, PROCEDURE = concat3);