List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 13 2007 9:37pm
Subject:Re: creating a function in mysql
View as plain text  
>I've determined that I don't seem to 
>be able to create functions at all.

Declare a delimiter, and lose the quote around the function name, eg

DROP FUNCTION IF EXISTS weighted_average;
DELIMITER |
CREATE FUNCTION weighted_average(n1 INT, n2 INT, n3 INT, n4 INT) RETURNS 
int(11)
DETERMINISTIC
BEGIN
  DECLARE avg INT;
  SET avg = (n1+n2+n3*2+n4*4)/8;
  RETURN avg;
END;
|
DELIMITER ;

PB

-----

Andrey Dmitriev wrote:
> All,
>
> I've been trying to create a function that will generate URLs so that I wouldn't have
> to wrote ugly SQL all the time. After no success, I've determined that I don't seem to be
> able to create functions at all. I am relatively new to mysql development (or management),
> so any advice is appreciated (coming from oracle world, btw)
>
> [root@mt-adm1 ~]# rpm -qa|grep -i mysql
> MySQL-shared-standard-5.0.18-0.rhel4
> mysql-gui-tools-5.0r3-1rhel4
> mysqlclient10-3.23.58-4.RHEL4.1
> mysql-administrator-5.0r3-1rhel4
> MySQL-server-pro-5.0.18-0.rhel4
> MySQL-client-pro-5.0.18-0.rhel4
>
> following:
> http://www.databasejournal.com/features/mysql/article.php/10897_3569846_2
>
> getting
> Database changed
> mysql> CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4 INT)
>     ->  RETURNS int(11)
>     ->     DETERMINISTIC
>     -> BEGIN
>     ->    DECLARE avg INT;
> 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
> ''WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4 INT)
>  RETURNS int(11)
>     DETERMI' at line 1
> mysql>    SET avg = (n1+n2+n3*2+n4*4)/8;
> ERROR 1193 (HY000): Unknown system variable 'avg'
> mysql>    RETURN avg;
> 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 'RETURN avg' at
> line 1
> mysql>   END
>     ->
>     -> ;
> 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 'END' at line 1
> mysql>
>
>
>
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 269.4.0/759 - Release Date: 4/12/2007 7:58 PM
>   

Thread
creating a function in mysqlAndrey Dmitriev13 Apr
  • Re: creating a function in mysqlPeter Brawley13 Apr
    • RE: creating a function in mysqlAndrey Dmitriev14 Apr
      • Re: creating a function in mysqlPeter Brawley14 Apr
      • Re: creating a function in mysqlBaron Schwartz14 Apr