« 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

Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.

Trackback Pings

TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/236

Listed below are links to weblogs that reference Table-Reference Restriction Lifted from Functions and Triggers in MySQL 5.0:

» Ephedra products. from Liquid ephedra.
Ephedra products. Ephedra based. Ephedra diet pills. Ephedra supplements. Ephedra liquid gel products. [Read More]

Tracked on January 1, 2008 10:18 PM