Issue with CREATE FUNCTION on MySQL 5

[gforceindustries]

#155718

Morning all,

I have a very simple database which contains two tables, no links between them. It’d be perfectly sufficient to represent this data in a spreadsheet, but I want it to be accessible via a web app, hence the database.

The table in question stores details of the holiday’s I’ve booked from work.

Code:

CREATE TABLE holidays_db.holiday (
holiday_id TINYINT NOT NULL AUTO_INCREMENT,
holiday_hours DECIMAL(2,1) NOT NULL,
holiday_date DATE NOT NULL,
holiday_reason VARCHAR (30) NOT NULL,
holiday_bort ENUM(“b”, “t”) DEFAULT “b” NOT NULL,
PRIMARY KEY (holiday_id),
UNIQUE (holiday_date)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_bin;

I’m trying to create a stored function to return the number of allocated hours. I can get this from the database using

Code:

SELECT SUM(h.holiday_hours) FROM holiday h

What I can’t seem to do is create a function to do this for me. I want to use a function rather than a procedure because I don’t want to have to execute multiple statements (ie a CALL followed by a SELECT), as I will want to use this value in other SELECT operations.

I’ve tried various permutations of the CREATE FUNCTION syntax based on the MySQL 5 documentation, all with the same error. The code to create my function is:

Code:

CREATE FUNCTION holidays_db.allocated_hours (i INT)
RETURNS DECIMAL(2,1)
READS SQL DATA
RETURN SUM(h.holiday_hours) FROM holidays_db.holiday h

Which gives

Code:

ERROR 1064 (42000): 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 ‘FROM holidays_db.holiday h’ at line 4

Can anybody give me a hint please? :)

To confirm, the select statement doesn’t appear to be the problem:

Code:

mysql> SELECT SUM(h.holiday_hours) FROM holidays_db.holiday h;
+———————-+
| SUM(h.holiday_hours) |
+———————-+
| 62.5 |
+———————-+
1 row in set (0.00 sec)

Thanks

[Author]

Posts