CREATE FUNCTION myFunction(id INT) RETURNS TABLE
BEGIN
RETURN SELECT * FROM board;
END
This query gives following error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE
As per documentation on loadable user defined functions in MySQL
you can only return values of type {STRING|INTEGER|REAL|DECIMAL}
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
SONAME shared_library_name
If you want to read a select resultset you have to define a procedure but not function.
DELIMITER //
DROP PROCEDURE IF EXISTS myProcedure //
CREATE PROCEDURE
myProcedure( id INT )
BEGIN
SELECT * FROM board
-- add where condition if required
WHERE Col_name = id
;
END
//
DELIMITER ;
And you can call procedure like
call myProcedure( 6 )
That returns implicit objects based on the statements used in the procedure.
Also Refer to: Adding a Loadable Function
id = id ; END //check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or higher) client/server protocol for this to work. This means that, for example, in PHP, you need to use the mysqli extension rather than the old mysql extension.
reference link: mySql docs
