Hi all,
I hope someone can help. I'm a PostgreSQL veteran but have found myself working on a client system based on MySQL, and the transition has been a little rocky
Right now I'm trying to use Stored Procs in MySQL 5.0. I have a simple proc which works OK:
Code:
CALL get_visible_folders('fred')
which returns a set of (folder_name, category_id, section_id) triples.
My problem is that I don't seem to be able to use this procedure within other SQL statements. With my Postgres hat on I I'm used to being able to use a stored procedure wherever tables and views are normally allowed, to do things like:
Code:
SELECT * FROM table1 AS t
NATURAL JOIN get_visible_folders('fred') AS f
WHERE t.id=foo;
or
Code:
SELECT * FROM table1
WHERE t.folder_name
IN (SELECT folder_name FROM get_visible_folders('fred'));
etc, but I haven't been able to get MySQL to accept anything along these lines. E.g, a minimal case:
Code:
SELECT * FROM productions
NATURAL JOIN
CALL get_visible_folders(u)
just gives the usual 'check the manual' syntax error.
Now, being a MySQL newbie, I'm not sure if I'm just missing a trick, or whether it's fundamentally not possible, in which case I should stop banging my head now. The docs seem a little flappy on this topic, but I confess it's late and I'm probably not at my best right now! If anyone could help me answer this I'd be very grateful.
Many thanks,
/sim