Passing a row reference as a MySQL function input parameter

Question

I am creating a function to easily retrieve a client name from a user account record based on the available information. All of the account fields other than id are optional and may be blank, so this successively checks various identifying columns until one is found, returning the id as a last result.

CREATE FUNCTION ACCOUNT_NAME(
    fname varchar(50),
    lname varchar(50),
    company varchar(50),
    email varchar(50),
    id int(10) unsigned
)
RETURNS VARCHAR(100) DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER
RETURN
    IF( TRIM(CONCAT(fname, ' ', lname)) <> '',
        TRIM(CONCAT(fname, ' ', lname)),
        IF( company <> '',
            company,
            IF(email <> '', email, id)
        )
    )
;

This works fine, however I need to pass each column into the function individually, i.e.:

SELECT ACCOUNT_NAME(
    a.first_name,
    a.last_name,
    a.company,
    a.email,
    a.id
) AS client_name
FROM accounts AS a WHERE 1;

Not only is this tedious, it will be extremely difficult to modify or extend this function in the future, likely requiring finding and updating every invocation as well as the definition itself.

Is it possible to pass a reference to the entire row result as an input parameter? Ideally I would like to do something like this:

CREATE FUNCTION ACCOUNT_NAME(row result)
    RETURNS VARCHAR(100) DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER
RETURN
    IF( TRIM(CONCAT(row.first_name, ' ', row.last_name)) <> '',
        TRIM(CONCAT(row.first_name, ' ', row.last_name)),
        IF( row.company <> '',
            row.company,
            IF(row.email <> '', row.email, row.id)
        )
    )
;

SELECT ACCOUNT_NAME(a.*) AS client_name
FROM accounts AS a WHERE 1;

Answer 1

The answer to your literal question is no—the parameters of a stored procedure or stored function cannot be a reference to a row object. They must be scalar data types, like the data types you use for defining columns in a table.

You could define a VIEW instead:

CREATE OR REPLACE VIEW accounts_view (id, client_name) AS
  SELECT id, COALESCE(
    NULLIF(CONCAT(fname, ' ', lname), ' '),
    NULLIF(company, ''),
    NULLIF(email, ''),
    id) AS client_name
  FROM accounts;

Then you can query it more simply:

SELECT client_name
FROM accounts_view;

If you ever change the way you want client_name to be formatted, then just use another CREATE OR REPLACE VIEW statement. It is very quick to re-write a view definition.