mysqlsqlfunctionsql-functionmysql-function

Error in MySQL function with parameters

I have MySQL function with 2 parameters namely user_id and post_id

Here's my function:

CREATE FUNCTION isliked(pid INT, uid INT)
RETURN TABLE
AS
RETURN (EXISTS (SELECT 1 FROM likedata ld WHERE post_id = pid AND user_id = uid
       )) as is_liked
END

I tried to call it with below query:

SELECT posts.id, posts.title, isliked(111,123)
FROM posts

It returns the following error:

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 'RETURN TABLE
AS
RETURN (EXISTS (SELECT 1 FROM likedata ld WHERE post_id = pid AN' at line 2

It should be return results like this http://sqlfiddle.com/#!9/91040/5 I'm new to sql, any help will be great, thanks in advance

Solution

  • If you want the function to return Boolean value use:

    CREATE FUNCTION isliked(pid INT, uid INT)
RETURNS BIT
   RETURN ( EXISTS ( SELECT 1 FROM likedata ld WHERE post_id = pid AND user_id = uid ) )