MySQL CREATE FUNCTION result with multiple records gives error #1172

Question

I have the following 3 tables:

table "name"       
---------------
id     name
---------------
1      book
2      pen


table "color"
------------------
id     color
------------------
1       red
2       yello
3       green
4       pink


table "both"
------------------------
id     name      color
----------------------
1      1            1
2      1            2
3      1            3
4      2            2

and I have the following function:

    DELIMITER //

    CREATE FUNCTION get_word(n VARCHAR(20))
        RETURNS VARCHAR(10)
        READS SQL DATA
        DETERMINISTIC
        BEGIN
            DECLARE b VARCHAR(20);
       SELECT  `color`.`color` INTO b  FROM   `name` 
        LEFT JOIN `both` 
          ON `name`.`id`=`both`.`name`
        LEFT JOIN `color`
          ON `color`.`id`=`both`.`color` 
       WHERE `name`.`name`=n;        

        RETURN b;
        END//

    DELIMITER ;

now when I run SELECT get_word('pen') it returns yellow which is what is expect.

but when I run the code SELECT get_word('book') it get error: #1172 - Result consisted of more than one row

my Question: What to do so this function works with multiple records as well as single record which it does when I search for "pen"? thanks

UPDATE:

If use the query without the function as follow, it just works fine:

       SELECT  `color`.`color` AS b  FROM   `name` 
        LEFT JOIN `both` 
          ON `name`.`id`=`both`.`name`
        LEFT JOIN `color`
          ON `color`.`id`=`both`.`color` 
       WHERE `name`.`name`='book';

and it returns:

**b**
red
yellow
green

what's your desired result when you have multiple records ? 'book' has red, yellow and green as results. So what should be returned ? — Raphaël Althaus, Aug 28 '13 at 13:53
does it have to do anything with the RETURNS VARCHAR(10)? can we change the return type to be multiple row? — barnamah, Aug 28 '13 at 14:13
Well, in fact no, you can't do this in a function. See my answer for details. — Raphaël Althaus, Aug 28 '13 at 14:26

score 3 · Answer 1 · edited May 23 '17 at 11:44

3

Well, it seems that you want a resultset (more than one value)

But a mysql stored function cannot return a resultset (see doc : Restrictions for Stored Functions).

Solution 1 : use a stored procedure

CREATE PROCEDURE get_word(n VARCHAR(20))
BEGIN
 SELECT  `color`.`color` 
 FROM   `name` 
   LEFT JOIN `both` 
          ON `name`.`id`=`both`.`name`
   LEFT JOIN `color`
          ON `color`.`id`=`both`.`color` 
   WHERE `name`.`name`=n; 
END

Solution2 : return a concatenated string

something like "green, yellow, blue" GROUP_CONCAT can do that easily. see this stackoverflow question for example

edited May 23 '17 at 11:44

Community

1
1

answered Aug 28 '13 at 14:18

Raphaël Althaus

59,727
6
96
122

Thank you. It worked perfectly. What is the difference and limitation of PROCEDURE with FUNCTION? – barnamah Aug 28 '13 at 14:26
1

Well, one big limitation on procedure is that you can't use it in a query... For more details and info, see for example this : http://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when – Raphaël Althaus Aug 28 '13 at 14:28

score 0 · Answer 2 · answered Aug 28 '13 at 13:56

0

Try to use group by after where , for example try to add group by id or something what is unique in your table.

answered Aug 28 '13 at 13:56

IMujagic

1,229
10
22

didn't get what you say. Nothing is unique in "both" table. A book can have "red", "yellow" and Green. – barnamah Aug 28 '13 at 14:01

MySQL CREATE FUNCTION result with multiple records gives error #1172

2 Answers2