MySQL 5.7 – SIZE is Not a Valid Input Error

MySQLmysql-5.7

I want to create user-defined function including SELECT. I tried below SQL script.

DELIMITER //

CREATE FUNCTION SEARCH(TEXT CHAR(250))
    RETURNS VARCHAR(SIZE) # => ERROR: SIZE is no valid input at this position... 
    NOT DETERMINISTIC
    BEGIN
        DECLARE result VARCHAR(SIZE);
            SET result = (
            select * from wp_posts 
                where post_type='post' 
                    AND post_status='publish' 
                    AND (post_author='1' OR post_author='2') 
                    AND NOT (post_author='3')
                    AND post_title REGEXP TEXT
                    )
                        ;        
        RETURN result;
    END //

DELIMITER ;

SEARCH("python") ;

I do not why occur an error "SIZE is no valid input at this position". I missing something?

Best Answer

Function need a static definition of the returning type. Size of VARCHAR should be declared explicitly like VARCHAR(50).

Related Question