EXPERT RESPONSE
The stock answer is: with a view.
For starters, you do not want an additional
actual physical column if you can help it. That would mean additional
code when inserting or updating, because you have to specify
the last_name and first_name values twice. It's also wasteful
of space.
create
view mynames
as
select first_name
, last_name
, coalesce(last_name||', ', '')
|| first_name as full_name
from names
Here the full name is constructed by concatenating
the last name and first name columns with a comma and space as
separators. (The full name is not actually a separate column, because
the view doesn't exist beyond the definition of the view. Unless you
materialize the view. But we're getting sidetracked.)
There is a special trick used here. The assumption
is that people who have only one name—yes, it is perfectly
legal to have just one name—will be registered in the table
with this name as the first name and with NULL as the last name.
Now, concatenating the comma-space onto NULL produces NULL, but
then COALESCE turns that into a non-NULL empty string.
Note that in this case, there is no comma and space.
The result of the COALESCE is then concatenated with the first name,
which will have been defined as NOT NULL. The final results would be:
first_name last_name full_name
---------- --------- ---------
Joe Btfsplk Btfsplk, Joe
Cher NULL Cher
Neat, eh? Without the COALESCE trick, the concatenation of last name,
comma, space, and first name would produce an overall result of NULL. You
might then be tempted just to enter an empty string as Cher's last name, and
that's just not right.
|
