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