Error in MySQL function with parameters

Question

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

your function have syntax problem. go the office mysql site and find the syntax of what's actual does syntax and make the appropriate changes — Ankit Agrawal, May 28 '18 at 05:35
You probably want to return a Boolean/bit rather than a table. You only need one value and it looks like you want to use it in the select clause. — Horia Coman, May 28 '18 at 05:43
What is the expected output? What do you want isliked function to return? — dWinder, May 28 '18 at 06:24

dWinder · Accepted Answer · 2018-05-28T06:54:58.103

1

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 ) )

edited May 28 '18 at 06:54

answered May 28 '18 at 06:35

dWinder

11,597
3
24
39

Error in MySQL function with parameters

1 Answers1