MySQL create function won't return sets

Question

I can't get this work with MySQL 5.x. This function should return all relevent rows from the select-statement:

CREATE FUNCTION getuids(root INT)
RETURNS SET OF INT AS $$
BEGIN
RETURN SELECT uid FROM pages WHERE deleted = 0
END
$$ LANGUAGE 'SQL';

Any idea what I made wrong?

Answer 1

A MySQL function may not return a row or set of rows in MySQL. Only a stored procedure can return rows. This is true up to and including MySQL 5.4. See the Limitations page. Usage of stored procedures as tables or where clause values in queries is also unavailable.

About the best you can do if you need to use a function is to use GROUP_CONCAT to get the values into a comma-separated list:

CREATE FUNCTION getuids() RETURNS VARCHAR(1024)
BEGIN
SELECT GROUP_CONCAT(uid SEPARATOR ',') INTO @uid_list FROM pages WHERE deleted = 0;
RETURN @uid_list;
END

Then in your select statement that needs to check the uids, you can use the FIND_IN_SET function like this:

SELECT ... WHERE FIND_IN_SET(my_field, get_uids());

P.S. I had originally posted links to the GROUP_CONCAT and FIND_IN_SET functions on the MySQL website, but Stack Overflow would only let me use one link. Yay for being new.