« Morning Run | Main | MySQL 5.0 In Depth - Stored Procedures, Views, Triggers and Cursors »

April 18, 2005

Table-Reference Restriction Lifted from Functions and Triggers in MySQL 5.0

There is no longer a restriction accessing tables from within MySQL stored functions (the internal ones build with SQL statements) and triggers. The MySQL documentation still has the phrase about it, but Jan Kneschke just said (in his tutorial on 5.0) the documentation isn't up to date.

I tried referencing a table in a function in 5.0.1 or 5.0.2 (they blur together now) and it definitely didn't work, didn't try it in 5.0.3 because I didn't see it in the documentation. I just tried this in 5.0.3 and am pleased to report that Jan is correct:

delimiter //
create function get_customer_count () returns INT
begin declare customer_count INTEGER;
select count(*) from customer into customer_count;
return customer_count;
end
//
A SELECT using this function works as it should:
mysql > select get_customer_count();
+----------------------+
| get_customer_count() |
+----------------------+
| 300000 |
+----------------------+
1 row in set (0.97 sec)

Good news.

Posted by mike at April 18, 2005 11:14 AM