How to create a computed column that is recalculated each time it is accessed

[waqia]

Hello!
Considering a two column table
-------------------------
| DOB | Age |
-------------------------
|yyyy-mm-dd | Xy-Yd-Zd |
-------------------------
| | |

Using a UDF, I would like to force calculate Age every time the record is accessed.

Sorry if it is not clear.
Thanks in advance!

[Luuk]

delimiter //
CREATE PROCEDURE update_dob()
BEGIN
    UPDATE dob set age=datediff(dob,now());
END//
 
delimiter ;

But this requires to run this procedure to update the values....

 call update_dob();

[waqia]

Thank you Luuk.
I was hoping that there was I proper calculated/computed column type in Mysql.

[Luuk]

maybe you can use a view

create view v_dob as select dob, datediff(dob,now()) age from dob;
 

after doing "insert into dob (dob) values ('2013-09-01');"

[test@test]> select * from v_dob where dob='2013-09-01';
+------------+------+
| dob        | age  |
+------------+------+
| 2013-09-01 |   -7 |
+------------+------+
1 row in set (0.00 sec)
 

[waqia]

Thank you again.
But I definitely need to use a UDF or something that allows me to return the age in this format :
x_years-y_month-z_days
eg : 10Y-11M-29D

Cheers!

[Rabbit]

Create a UDF if you need to reuse code. Create a view if you want to display data in a specific way. Combine the two if you need both. In your case, you definitely want a view. Whether or not you create a UDF to use in the view is up to you.