Being the Stored Procedure woes of a foreigner in the land of MySQL

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. :confused:

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