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;

But you can just pass the id, and then select the row with that ID inside the function, and then you have the entire row... — Hanlet Escaño
– Hanlet Escaño, Commented Dec 26, 2013 at 20:31
Interesting, I'll give it a shot, but wouldn't that have an enormous performance impact to query each of potentially hundreds of rows individually when I already have all of the necessary data already? — Rob
– Rob, Commented Dec 26, 2013 at 20:37
I do not know the details for your query, or how much data you have, have you looked into a cursor? Why do you use a function? Maybe a stored procedure/cursor combination would do all of this really fast in just one shot. — Hanlet Escaño
– Hanlet Escaño, Commented Dec 26, 2013 at 20:39
@Rob - Make it work first and worry about performance afterwards, if it is an issue. — user2839610
– user2839610, Commented Dec 26, 2013 at 21:17
Why are you writing something which duplicates the MySQL COALESCE() built-in function? — APC
– APC, Commented Dec 27, 2013 at 6:25

Bill Karwin · Accepted Answer · 2017-12-31 20:47:28Z

2

+50

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.

answered Dec 31, 2017 at 20:47

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erel Segal-Halevi Over a year ago

This looks like the closest thing to having a row as a parameter.

Rick James · Accepted Answer · 2017-12-31 20:21:20Z

0

SQL is not a good language for text manipulation. SQL is a good language for storing and retrieving data.

Keep the "business logic" in the application code. That is, have a Layer of code (in whatever language you are using) that performs the Account_Name manipulation.

The "client" can call the Layer with a row reference or whatever makes sense. Design the API to keep it clean. The Layer then does whatever grungy work is needed to implement functions like this. It may even have to run off to more than one table to gather the pieces.

For example, today you have the email in the same table. If tomorrow you have it somewhere else, then change the Layer to do two SELECTs or (better) one SELECT with a JOIN.

answered Dec 31, 2017 at 20:21

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Passing a row reference as a MySQL function input parameter

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related