A PL/pgSQL Function
Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.
The two ways enjoy two techniques (SQL language and PL/pgSQL language), which gives us four possible solutions. I’ve also provided a conditional drop statement for the full_name function. If you’re new to PostgreSQL the DROP
statement might make you scratch your head because you’re wondering why you need to use the formal parameter list. The DROP
statement needs the parameter list because PostgeSQL lets you overload schema/database functions and procedures.
The code is for a named parameter lists using the SQL language is:
DROP FUNCTION IF EXISTS full_name ( IN pv_first_name text , IN pv_middle_name text , IN pv_full_name text); CREATE FUNCTION full_name ( IN pv_first_name text , IN pv_middle_name text , IN pv_last_name text , OUT pv_full_name text) AS 'SELECT pv_first_name || CASE WHEN pv_middle_name IS NOT NULL THEN '' '' || pv_middle_name || '' '' ELSE '' '' END || pv_last_name' LANGUAGE SQL; |
The code is for a positional parameter lists using the SQL language is:
DROP FUNCTION IF EXISTS full_name ( IN text , IN text , IN text); CREATE FUNCTION full_name ( IN text , IN text , IN text , OUT text) AS 'SELECT $1 || CASE WHEN $2 IS NOT NULL THEN '' '' || $2 || '' '' ELSE '' '' END || $3' LANGUAGE SQL; |
You would re-write the function in the PL/pgSQL language as follows (please note the named parameter list):
CREATE FUNCTION full_name ( IN pv_first_name text , IN pv_middle_name text , IN pv_last_name text) RETURNS text AS $$ DECLARE lv_output text; BEGIN IF pv_middle_name IS NULL THEN lv_output = CONCAT(pv_first_name, N' ', pv_last_name); ELSE lv_output = CONCAT(pv_first_name, N' ', pv_middle_name, N' ', pv_first_name); END IF; RETURN lv_output; END $$ LANGUAGE plpgsql IMMUTABLE; |
You can test either version of the program with the following two queries from the pseudo table dual, which isn’t require in the SELECT
statement:
SELECT full_name('Henry',NULL,'Pym') AS "Ant-Man" UNION ALL SELECT full_name('Henry','''Hank''','Pym') AS "Ant-Man"; |
It prints:
Ant-Man text ---------------- Henry Pym Henry 'Hank' Pym |
As always, I hope this helps those looking for how to accomplish a concatenation function in PostgreSQL.