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.